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. 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|@AdamMachanic) 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>SQLMCMLAP</TD> <TD>SQL Server</TD> <TD>SQLNCLI</TD> <TD>SQLMCMLAP</TD> <TD>0</TD> </tr> <tr> <TD>repl_distributor</TD> <TD>SQL Server</TD> <TD>SQLNCLI</TD> <TD>SQLMCMLAP</TD> <TD>0</TD> </tr> <tr> <TD>SQLMCMLAPSQL12</TD> <TD>SQL Server</TD> <TD>SQLNCLI</TD> <TD>SQLMCMLAPSQL12</TD> <TD>0</TD> </tr> <tr> <TD>SSAS</TD> <TD></TD> <TD>MSOLAP</TD> <TD>SQLMCMLAP</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>repl_distributor</TD> <TD>SQL Server</TD> <TD>SQLNCLI</TD> <TD_x0020_align_x003D_center>SQLMCMLAP</TD_x0020_align_x003D_center> <TD_x0020_align_x003D_center>0</TD_x0020_align_x003D_center> </tr> <tr> <TRRow>0</TRRow> <TD>SQLMCMLAP</TD> <TD>SQL Server</TD> <TD>SQLNCLI</TD> <TD_x0020_align_x003D_center>SQLMCMLAP</TD_x0020_align_x003D_center> <TD_x0020_align_x003D_center>0</TD_x0020_align_x003D_center> </tr> <tr> <TRRow>1</TRRow> <TD>SQLMCMLAPSQL12</TD> <TD>SQL Server</TD> <TD>SQLNCLI</TD> <TD_x0020_align_x003D_center>SQLMCMLAPSQL12</TD_x0020_align_x003D_center> <TD_x0020_align_x003D_center>0</TD_x0020_align_x003D_center> </tr> <tr> <TRRow>0</TRRow> <TD>SSAS</TD> <TD></TD> <TD>MSOLAP</TD> <TD_x0020_align_x003D_center>SQLMCMLAP</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;} tr.even {background-color:white;} tr.odd {background-color:#eeeeee;} </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); Set @Body = Replace(@Body, '_x003D_', '='); Set @Body = Replace(@Body, '_x0020_', space(1)); Set @Body = Replace(@Body, '<tr><TRRow>0</TRRow>', '<tr class="even">'); Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr class="odd">'); Select @TableHead + @Body + @TableTail;
This yields an HTML email with the following table:
Paul
Would you be able to selectively do this to a particular row based on a value within that row? Say you wanted to highlight any row that has a value within it greater than 10,000?
SQLSoldier
Absolutely. I have done versions of this where at run time it set a style tag for either the TR or TD element to highlight certain values.
Brian
Thanks for this post! I had to do something similar and your post provided a great blueprint. Thanks again!
SQLSoldier
You’re welcome!
SQLPRODDBA
Could you please explain why this code does not work in SQL 2000 and what needs to be done to make it work? I am getting below error:
Msg 156, Level 15, State 1
Incorrect syntax near the keyword ‘For’.
SQLSoldier
It would have to be completely rewritten for SQL 2000. SQL 2000 did not have those SQL views or MAX data types.