Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Building HTML Emails With SQL Server and XML

Building HTML Emails With SQL Server and XML

I’ve written a lot of custom reports in my days that output data as an HTML string and then sends it via email. It makes for an easy to read, colorful report, and PM’s and business folks love easy to read, colorful reports. Indeed, my reports  Until recently, I used simple string concatenation for building my HTML strings. Simple string concatenation is not always simple as it can yield unexpected results due to data type conversions. I knew there had to be a better way.

 

I’ve seen many examples over the last couple of years using XML to combine data columns together as delimited strings. Most recently, Adam Machanic (blog|twitter) showed an example of how to do this in the MCM program. I had planned to investigate this method further because all of the examples I saw were simple concatenation examples. I needed something a little more complex.

 

Start Simple

 

I started simple by just figuring out the syntax to generate the base tags for the rows and cells of the table. I experimented with different approaches and settled on For XML Raw as the right method for my use. To get the cell tags, I used [TD] as the column alias for all columns and specified 'tr' as the root element for each row. SQL Server wasn’t very happy with this syntax, but by specifying the Elements option to make the XML element centric, SQL liked it just fine. To demonstrate how this works, I’m going to use my syntax against the sys.servers table on my laptop.

 

The Query:

 

Select name As [TD],

      product As [TD],

      provider As [TD],

      data_source As [TD],

      is_linked As [TD]

From sys.servers

For XML raw('tr'), Elements

 

The Output:

 

<tr>

  <TD>MyLapTop</TD>

  <TD>SQL Server</TD>

  <TD>SQLNCLI</TD>

  <TD>MyLapTop</TD>

  <TD>0</TD>

</tr>

<tr>

  <TD>MyRemoteServer</TD>

  <TD>SQL Server</TD>

  <TD>SQLNCLI</TD>

  <TD>MyRemoteServer</TD>

  <TD>1</TD>

</tr>

 

Expand on the Base

 

Now that I had the basic output that I wanted, I needed to add the extra elements to make my HTML table complete. So the first tricky thing I needed to work out was that I wanted some of the columns to be aligned left (the default) and some centered. So I needed to add some alignment to some of the tags. I took a shot and decided to see what would happen if I simply added the alignment property to the column aliases. To my surprise, I could work with that. The space and equals sign were entitized (is that a word?) – turned into entity codes – and I could fix that after the fact with the Replace() function. The space is changed to _x0020_ and the equals sign is changed to _x003D_.

 

The next brainteaser was how to get alternating background colors for the rows. This was simple in the old string concatenation method using the RowNumber() function and setting a different color for the even or odd rows. I decided to add a tag to the output of each row using the RowNumber() function with the modulo operator (%) and then replacing the tag with the appropriate value at the end. Each tag will get either a 0 or a 1 so it becomes simple to replace the even and odd rows with the colors I chose.

 

My query looks like this now:

 

Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

      name As [TD],

      product As [TD],

      provider As [TD],

      data_source As [TD align=center],

      is_linked As [TD align=center]

From sys.servers

Order By is_linked, name

For XML raw('tr'), Elements

 

And the results now look like this:

 

<tr>

  <TRRow>1</TRRow>

  <TD>MyLaptop</TD>

  <TD>SQL Server</TD>

  <TD>SQLNCLI</TD>

  <TD_x0020_align_x003D_center>MyLaptop</TD_x0020_align_x003D_center>

  <TD_x0020_align_x003D_center>0</TD_x0020_align_x003D_center>

</tr>

<tr>

  <TRRow>0</TRRow>

  <TD>MyRemoteServer</TD>

  <TD>SQL Server</TD>

  <TD>SQLNCLI</TD>

  <TD_x0020_align_x003D_center>MyRemoteServer</TD_x0020_align_x003D_center>

  <TD_x0020_align_x003D_center>1</TD_x0020_align_x003D_center>

</tr>

 

Wrap It Up

 

Now I needed to make the necessary replacements and then wrap it all in the rest of the HTML needed to complete the table and page. In order to perform the replacements, I need to wrap the query in another query to be able to assign the output to a variable

 

The Final Script:

 

Declare @Body varchar(max),

      @TableHead varchar(max),

      @TableTail varchar(max)

 

Set NoCount On;

 

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

                  '<style>' +

                  'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

                  '</style>' +

                  '</head>' +

                  '<body><table cellpadding=0 cellspacing=0 border=0>' +

                  '<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

                  '<td align=center><b>Product</b></td>' +

                  '<td align=center><b>Provider</b></td>' +

                  '<td align=center><b>Data Source</b></td>' +

                  '<td align=center><b>Is Linked?</b></td></tr>';

 

Select @Body = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

            name As [TD],

            product As [TD],

            provider As [TD],

            data_source As [TD align=center],

            is_linked As [TD align=center]

      From sys.servers

      Order By is_linked, name

      For XML raw('tr'), Elements)

 

-- Replace the entity codes and row numbers

Set @Body = Replace(@Body, '_x0020_', space(1))

Set @Body = Replace(@Body, '_x003D_', '=')

Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')

 

Select @Body = @TableHead + @Body + @TableTail

 

-- return output

Select @Body

 

The Final Output:

 

<html><head>

<style>

td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}

</style>

</head>

<body>

<table cellpadding=0 cellspacing=0 border=0>

<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td><td align=center><b>Product</b></td><td align=center><b>Provider</b></td><td align=center><b>Data Source</b></td><td align=center><b>Is Linked?</b></td></tr>

<tr bgcolor=#C6CFFF><TD>MYLAPTOP</TD><TD>SQL Server</TD><TD>SQLNCLI</TD><TD align=center>MYLAPTOP</TD align=center><TD align=center>0</TD align=center></tr>

<tr><TD>MYREMOTESERVER</TD><TD>SQL Server</TD><TD>SQLNCLI</TD><TD align=center>MYREMOTESERVER</TD align=center><TD align=center>1</TD align=center></tr>

</table></body></html>

 

What it looks like in the email:

 

Server Name

Product

Provider

Data Source

Is Linked?

MYLAPTOP

SQL Server

SQLNCLI

MYLAPTOP

0

MYREMOTESERVER

SQL Server

SQLNCLI

MYREMOTESERVER

1

 

Comments

Posted by dyfhid on 16 June 2010

Very cool, Robert, and also very useful! Thank you so much for posting this!

Posted by Todd McDermid on 16 June 2010

I'm just gonna blatantly piggyback on your article - because I know it will get views!  Despite my sysadmin's hate of HTML mail for security reasons, it's still a very valuable help in presenting information in a better way.

I've done almost the exact same thing when I process data using SQL Server Integration Services.  I usually do this kind of thing when I have to report exception records - but as you've done, it works equally well for periodic reporting.

I uploaded two objects to CodePlex - the HTML Table Destination (htmltabledestination.codeplex.com) that takes a data flow and stuffs it into an HTML coded table string, and the Send HTML Mail Task, which allows HTML content to be sent in a mail message.  So if you use Scripts to accomplish that kind of thing now, or you send out poorly formatted plain-text emails, try those two extensions out.

Posted by Robert L Davis on 16 June 2010

Thanks dyfhid and toddmcd!!

I like your codeplex offerings!! Piggyback rides are always welcome as long as they are relevant to the topic and provide some vlaue to the reader!!

If you have a blog post about these objects, please provide a link to that as well.

Posted by Adam Machanic on 16 June 2010

Great job, Robert! Fantastic application of attribute-centric XML :-)

Posted by Adam Machanic on 16 June 2010

Oops! Make that element-centric. :-)

Posted by Robert L Davis on 17 June 2010

Thanks Adam!!! You're probably happy to hear that I was paying attention in class!! :)

Posted by Alberto Ortoll on 13 January 2011

Great article! :)

Posted by Bensn on 6 June 2012

Thanks! Very useful!

Posted by inimesh on 19 November 2012

Thanks a lot!!!

Posted by sachindiwaker on 11 February 2013

Nice - very useful. Many Thanks.

Posted by bkraider on 27 February 2013

Awesome article helped me to finish my one of the project I am working on

Posted by Satya_0000 on 18 March 2013

A great Article !! helped me a lot...

Posted by basitbaig on 19 April 2013

Thank you for Helping Others, Good work!

Basit

Posted by chandanm.mishra on 27 June 2013

What could be max number of table rows.

Posted by John Waclawski on 20 August 2013

Thanks for the article!  Although when I use "[TD align=center]" or even "[TD align: center]" it concatenates the fields I'm using it on into 1 field, thereby eliminate 3 fields.  

So for example, 3 field returns are 0, Null, 0.  It pulls those 3 values into 1 field & says, 0Null0.

Sorry for the confusion.  I can provide you a screenshot if you'd like.  Not sure if I will have it fixed by that time but thought I'd throw this out there.

Thanks again!!

Leave a Comment

Please register or log in to leave a comment.