Sending SQL Server Query Results as Pretty HTML Email

 So my goal here is to be able to send out a pretty HTML email based on data from a SQL Query using only SQL Server 2005 (or greater).

 First we need to make sure the environment is all set up by doing the following

1. Make sure that CLR integration is enabled on the instance of SQL Server
2. Make sure that Database Mail is enabled and configured.
3. Make sure Openrowset is enabled.
4. Create our SampleData table and populate it with some data.
5. Create a table to store our XSL data.

Step 1.

 Make sure that CLR Integration is enabled on your SQL Server Instance by opening the SQL Server Surface Area Configuration tool and clicking on the enable CLR Integration checkbox like shown below. 

 

Step 2.

While you are there also make sure that Database Mail is enabled by checking the checkbox like shown here.

If you haven't configured DBMail yet here is a helpful step by step guide.

Step 3.
And also make sure the OPENROWSET is enabled.

Step 4.
Next we need some sample data to work with.
This will create the SampleData table and populate it with some sample data.

CREATE TABLE dbo.SampleData
    (
    ID int NOT NULL IDENTITY (1, 1),
    ItemNumber nvarchar(50) NULL,
    ItemDescription nvarchar(50) NULL,
    Cost decimal(18, 2) NULL,
    QtySold int NULL
    )

Insert into SampleData (ItemNumber, ItemDescription, Cost, QtySold)                 

values ('2WE4R5T', 'Thinga Ma Bob', '2.10', '1000')

Insert into SampleData (ItemNumber, ItemDescription, Cost, QtySold)

values ('96KL69YLK', 'DooHickey', '995.10', '22')

Insert into SampleData (ItemNumber, ItemDescription, Cost, QtySold)

values ('9FJ438JD', 'Whatchama Calut', '2256.25', '56')

Step 5.
 We could just store the XSL Files in the filesystem but we want to do as much in SQL Server as possible so we run the following to create a table to store our XSL.

CREATE TABLE [dbo].[XMLTransforms](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [XSL] [xml] NULL
)

Now that our environment is all set up we are ready to have some fun.

6. Write the query to get the data we want to send.
7. Write the XSL file to transform the XML returned in the above query to pretty HTML.
8. Import the XSL into the Table created in step 4 above. 
9. Write the CLR Function in C# to transform the XML to HTML.
10. Add the CLR Function to SQL Server.
11. Put it all together and watch the magic happen

Step 6.
  The only requirement is that the query returns it's data as the XML Data Type. So this will return an XML doc for us to work with.

    Declare @xmlDoc XML;

    Select @xmlDoc = (Select * from SampleData for xml auto, Root('Sample'), elements)

Here I am declaring a variable @xmlDoc as being of the XML Data Type.
I am just pulling all the data from the table we created above and sticking it into the @xmlDoc variable.

• The "for xml auto" portion of the above statement tells SQL to return the query results as nested XML elements.
• The "Root('Sample')" portion nests the results of the query in a root element named 'Sample'
• The "elements" portion tells SQL to return each column as a subelement rather than a an attribute of an element (read more about this here)

The resulting XML from the statement above should look like this.

Step 7.
Now we need to specify how we want to transform the XML that we have into html. We do this using XSL. We are going to keep things simple and just return the data in an html table and base our XSL file on the example shown here 
You can use Visual Studio or Notepad++ or your favorite text editor to edit the file. 
Here is what our XSL file looks like or you can download it here

Step 8.
 To import the XSL file from your Filesystem into the SQL Table created in step 5 run the following

INSERT INTO XMLTransforms(XSL)
    SELECT * FROM OPENROWSET(
     BULK 'c:\XSL\Sample.xsl', SINGLE_BLOB) AS x

Step 9.
Now we need a way to apply the XSL to the XML and spit out the pretty HTML we are looking for. We do this using a CLR function.
The CLR function looks like this and you can download the code here

To compile the above code you can use the csc.exe command line compiler that comes with the .Net Framework.In the case of my PC I ran the following from a command prompt

    cd C:\Windows\Microsoft.NET\Framework\v3.5
    csc.exe /target:library /out:C:\Code\XML2HTML.dll C:\Code\XML2HTML.cs

This creates the XML2HTML.dll in the C:\Code Directory

Step 10.
The next step is to add the map the Function in the DLL to a function in SQL Server. We do this by doing the following.

First we need to add the Assembly to SQL Server like this:

CREATE ASSEMBLY XML2HTML 
FROM 'C:\Code\XML2HTML.dll'
WITH PERMISSION_SET = SAFE;

Then we need to map a SQL Function to the CLR Function in the DLL.

CREATE FUNCTION [dbo].[ApplyXslTransform](@inputXML [xml], @inputTransform [xml])   
RETURNS [xml] WITH EXECUTE AS CALLER                                                                                      
AS                                                                                                                                                                   
EXTERNAL NAME [XML2HTML].[UserDefinedFunctions].[Transform]                                      

Step 11.
Now we can finally send out our HTML Email by executing the following

Declare @xmlDoc XML;
Select @xmlDoc = (Select * from SampleData for xml auto, Root('Sample'), elements)
Declare @xsl XML;
Set @xsl = (Select xsl from XMLTransforms where id = 1)
Declare @messBody nvarchar(max);
Set @messBody = cast((Select dbo.ApplyXslTransform(@xmlDoc, @xsl)) as nvarchar(max))
EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Profile Name Here',
        @recipients = 'someone@sample.com',
        @body = @messBody,
        @body_format = 'HTML',
        @subject = 'Sample Info.' ;

Ok so maybe it isn't the prettiest HTML, but you should be able to see the potential. As you create new and different XSL files and import them into the XMLTransforms table you can change the whole layout of your email just by changing the  id in the
"Set @xsl = (Select xsl from XMLTransforms where id = 1)" statement.
You could return a customized "Thanks For Your Order" email in the format of a letter or an automated "Friendly Reminder" that a customer's account is overdue from your AR system.
Once you have the framework detailed above you are only limited by your HTML skills.
Good Luck!