Create XML/HTML with T-SQL
Generating XML/HTML output in SQL Server
Sending email from SQL is not a difficult to achieve, but generating rich HTML content might be. SQL Server itself is not built as a text processor, first approach would be to build HTML by simple concatenating the string elements.
This approach will work, but over time it will become more and more difficult to maintain especially if you need to add layout elements over time.
HTML and XML derived from the same base (Both are markup languages, which represents text data in proper format using tags) so it is easy to generate both formats as they both rely their structure on tags.
For this example Northwind database was used which is available on CODEPLEX website. The link is listed in References section of this page
We will transform Categories table to unordered HTML lis (ul)
Simple select using FOR XML PATH will return the XML document
SELECT CategoryName AS p,Description AS span FROM dbo.Categories FOR XML PATH ('div')
Now to convert it to string, we can just use STUFF and write to NVARCHAR variable
DECLARE @bodyHtml NVARCHAR(MAX); SELECT @bodyHtml = STUFF( ( SELECT CategoryName AS p,Description AS span FROM dbo.Categories FOR XML PATH ('div') ), 1, 0, '' ); SELECT @bodyHtml;
This will generate the following HTML string
<div> <p>Beverages</p> <span>Soft drinks, coffees, teas, beers, and ales</span> </div> <div> <p>Condiments</p> <span>Sweet and savory sauces, relishes, spreads, and seasonings</span> </div> <div> <p>Confections</p> <span>Desserts, candies, and sweet breads</span> </div> <div> <p>Dairy Products</p> <span>Cheeses</span> </div> <div> <p>Grains/Cereals</p> <span>Breads, crackers, pasta, and cereal</span> </div> <div> <p>Meat/Poultry</p> <span>Prepared meats</span> </div> <div> <p>Produce</p> <span>Dried fruit and bean curd</span> </div> <div> <p>Seafood</p> <span>Seaweed and fish</span> </div>
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.