Create XML/HTML with T-SQL

Generating XML/HTML output in SQL Server

  • Share

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.

Note

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

    

References

  • Share

Disclaimer

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.

Comments for this article

comments powered by Disqus