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 relly 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
DECLARE @bodyHtml NVARCHAR(MAX); SELECT @bodyHtml = STUFF(( SELECT cat.CategoryName AS li FROM Categories (NOLOCK) cat FOR XML PATH('ul') ), 1, 0, ''); SELECT @bodyHtml
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.