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.

Note

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)

Sql Northwind Categoties

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>
    

References

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.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

.NET

read more

JavaScript

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article