http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/

Printed 2014/08/29 04:39PM

Building HTML Emails With SQL Server and XML

By Robert Davis, 2010/06/15

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

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.