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
The Query:
Select name As
product As
provider As
data_source As
is_linked As
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
product As
provider As
data_source As
is_linked As
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
product As
provider As
data_source As
is_linked As
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 |
