SQL Send Email (2)

Microsoft SQL Sever 2005 supports xml data type. This data type provides a very convenient way to hold data from SQL SELECT statement. Continue from the previous blog, the following codes set xml with data from MyData table(id, name and value three columns):

DECLARE @xml xml;
DECLARE @htmlAfter NVARCHAR(MAX);
..
SET @xml =
-- Checks if count is 0 or not. Avoid NULL value in the result.
CASE
 -- @count is a var to get the count from MyData table.
 WHEN @count is null OR @count = 0 THEN null
 ELSE
 -- Get data from MyData table
 -- Note: the three columns should match the previous
 --       table header column definition (3 cols)
   (SELECT
      td = CAST(t.ID AS VARCHAR), '',
      td = t.NAME, '',
      td = t.VALUE, ''
    FROM MyData t
    FOR XML PATH('tr'), TYPE -- As XML output
   )
END;

SET @htmlAfter = N'</table></div>'

The comments should well explain how to set xml in this example case. The reason I use xml data type is that it can hold large amount of data. I tried to use NVARVAR(MAX) before. In one case, the data retrieved by SELECT are millions rows of data and my var was overflowed with some data missing. When this happened, there was not any errors or indications. It took me a day to figure it out. With xml data type, The problem is gone.

The next article will continue to discuss how to use a SQL Database Project to save results to a file and get back text size information before sending out email.