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. 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;

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;

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.

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’.