Format / Indent XML code while sending email using sp_send_dbmail [formatting xml code in mail body]

Question

I am trying to send a dbmail with XML code as a body, see my code below showing the same,

declare @xml xml  = '<?xml version="1.0"?>
<Order>
<Date>2003/07/04</Date>
<CustomerId>123</CustomerId>
<CustomerName>Acme Alpha</CustomerName>
<Item>
<ItemId> 987</ItemId>
<ItemName>Coupler</ItemName>
<Quantity>5</Quantity>
</Item>
<Item>
<ItemId>654</ItemId>
<ItemName>Connector</ItemName>
<Quantity unit="12">3</Quantity>
</Item>
<Item>  
<ItemId>579</ItemId>
<ItemName>Clasp</ItemName>
<Quantity>1</Quantity>
</Item>
</Order>'

--SELECT @xml

declare @bodyprep varchar(max)  

select @bodyprep  = cast(@xml as varchar(max))

EXEC msdb. dbo.sp_send_dbmail

                              @profile_name='Profile-A' ,
                              @recipients ='.com',
                              @from_address = '.com' ,
                              @subject = 'test',
                              @body = @bodyprep

but the mail i receive is unaligned like shown below,

<Order><Date>2003/07/04</Date><CustomerId>123</CustomerId><CustomerName>Acme Alpha</CustomerName><Item><ItemId> 987</ItemId><ItemName>Coupler</ItemName><Quantity>5</Quantity></Item><Item><ItemId>654</ItemId><ItemName>Connector</ItemName><Quantity unit="12">3</Quantity></Item><Item><ItemId>579</ItemId><ItemName>Clasp</ItemName><Quantity>1</Quantity></Item></Order>

Is there a way to format or indent the XML code above ?

I would like to send a formatted code as shown below,

<Order>
  <Date>2003/07/04</Date>
  <CustomerId>123</CustomerId>
  <CustomerName>Acme Alpha</CustomerName>
  <Item>
    <ItemId> 987</ItemId>
    <ItemName>Coupler</ItemName>
    <Quantity>5</Quantity>
  </Item>
  <Item>
    <ItemId>654</ItemId>
    <ItemName>Connector</ItemName>
    <Quantity unit="12">3</Quantity>
  </Item>
  <Item>
    <ItemId>579</ItemId>
    <ItemName>Clasp</ItemName>
    <Quantity>1</Quantity>
  </Item>
</Order>

Thanks

score 0 · Answer 1 · answered Oct 04 '13 at 19:17

msdb.dbo.sp_send_dbmail has an additional parameter, @body_format = 'HTML', which would allow you to display the XML as you like, once you'd decorated it with the proper HTML markup. That's the only way you can control the appearance of the email body to the level you've specified, i.e. with proper indentation and color-coding.

If you can do without the color-coding, @body_format = 'TEXT' (the default) is fine, but either way you have some work on your hands to get it indented as you indicate.

Format / Indent XML code while sending email using sp_send_dbmail [formatting xml code in mail body]

1 Answers1