SQL: Emailing Query Results as a HTML Report
Author: jason
Date: 2014-02-27 13:36:46
Category: Musing

Use SQL Database Mail and SQL Agent Jobs if you want to automate the sending of routine/simple HTML reports based on query results.

This method of sending reports uses the XML results of a SELECT Statement inserted into the Body of an Email sent with msdb.dbo.sp_send_dbmail

First I'll build a Temporary Table Variable with Sales Info. Then I'll SELECT out of it with the XML tag.





We'll add some HTML tags and build a big string. Note that I am putting blank entries after each Field in the SELECT statement. This is to trick XML into letting me use the same Field Alias.





Here is the Database Mail code for sending an email. Note that you have to have set up your Mail Profile for this to work.





If you want, you can put this Code into a SQL Agent Job Step. Then it will send out the report whenever you want. Of course, at that point you want to be selecting from an actual table. :)





Here is what the Body of the Email message should look like.





Example SQL showing the output of a SELECT statement using XML PATH
Code

DECLARE @Table AS TABLE ([DateTime] DATE, SalePrice MONEY, SaleQuantity INT)
INSERT INTO @Table VALUES ('1/5/2014', 25.65, 3), ('1/7/2014', 54.00, 2), ('1/3/2014', 89.30, 7)
DECLARE @XML AS NVARCHAR(MAX)
SET @XML = (
SELECT
[DateTime]
, SalePrice
, SaleQuantity
FROM @Table
FOR XML PATH(''), ELEMENTS
)
SELECT @XML AS SalesReport



Example SQL showing the addition of naming each Column TR and compiling a big HTML string to put the query results into
Code

DECLARE @Table AS TABLE ([DateTime] DATE, SalePrice MONEY, SaleQuantity INT)
INSERT INTO @Table VALUES ('1/5/2014', 25.65, 3), ('1/7/2014', 54.00, 2), ('1/3/2014', 89.30, 7)
DECLARE @XML AS NVARCHAR(MAX), @BODY AS NVARCHAR(MAX)
SET @XML = (
SELECT
[DateTime] AS 'TD', ''
, SalePrice AS 'TD', ''
, SaleQuantity AS 'TD'
FROM @Table
FOR XML PATH('TR'), ELEMENTS
)
SET @BODY = '<HTML><BODY><TABLE BORDER=1 CELLSPACING=2><TR>'
SET @BODY = @BODY + '<TH>DateTime</TH><TH>SalePrice</TH><TH>SaleQuantity</TH></TR>'
SET @BODY = @BODY + @XML + '</TABLE></BODY></HTML>'
PRINT @BODY



Example SQL showing sending an Email with Database Mail
Code

EXEC msdb.dbo.sp_send_dbmail
@profile_name='My Mail Group',
@body_format ='HTML',
@recipients='Someone@Example.com',
@subject='Daily Sales Report',
@body=@BODY




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009