(Print this page)

Generating HTML output from a SQL query
Published date: Monday, October 29, 2018
On: Moer and Éric Moreau's web site

I have been generating various HTML outputs resulting from SQL queries for a long time now. Very often, that output was added to the content of an email body. I have always been using the application layer (either in VB or C#) to HMTLize the data I was querying from the database.

Lately, I found a very easy way to get the HTML result straight from the database server.

Purist DBAs will argue that the server should only return raw data and not do all the formatting. I am with them most of the time but there always an exception!

Figure 1: The resulting HTMLize output in a web browser

No downloadable code this month

It doesn’t happen very often but there won’t be any downloadable code this month. There are no VB or C# code this month. Only a short SQL query which is embedded directly into this article.

The query

So here is the test query.

  1. DECLARE @output VARCHAR(MAX)
  2. SET @output = '<html>' +
  3. '<body>' +
  4. '<style type="text/css">' +
  5. ' table {font-size:9.0pt;font-family:verdana;text-align:left;}' +
  6. ' tr {text-align:left;}' +
  7. ' h3 {display: block; font-size: 15.0pt; font-weight: bold; font-family: verdana; text-align:left; }' +
  8. '</style>' +
  9. '<H1>emoreau.com - Example of HTML-formated output</H1>' +
  10. '<H2>' + CAST(GETDATE() AS VARCHAR(20)) + '</H2>' +
  11. '<table border="1">' +
  12. '<tr> <th>Object ID</th> <th>Name</th> <th>Type Description</th> </tr>' +
  13. CAST(
  14. (SELECT TOP 100 ISNULL(object_id, '') AS 'TD', ISNULL(name, '') AS 'TD', ISNULL(type_desc,'') AS 'TD' , ISNULL(NULL,'') AS 'TD'
  15. FROM sys.objects
  16. ORDER BY type_desc, name
  17. FOR XML RAW('tr'), ELEMENTS
  18. ) AS VARCHAR(MAX)) +
  19. '</table>' +
  20. '</body>' +
  21. '</html>'
  22. SELECT @output

How it works

This demo query outputs the content of the first 100 rows from sys.objects table. The query itself is buried in lines 14 to 16. More on them a bit later.

Almost everything else is related to the HTML rendering.

If you look at lines 4 to 8, you will find that some styles are modified from the default ones. If you want something different, you continue to enhance these lines. If you don’t want anything specific, you can freely remove them.

Lines 9 and 10 are adding some headers to the HTML content.

Line 11 creates a simple table with borders.

Line 12 adds a row to the table containing the columns’ header. You will need to change that line for sure to put your own columns’ name instead of mine.

So, the real meat here of this snippet is really line 14. You might have notice that I gave the alias TD to every field. The reason is simple, those will become the HTML tags for each field (Table Data).

And an interesting fact is that on line 17, we pass TR to the FOR XML RAW clause so that way we automatically get the TR tags (Table Row) on each resulting row.

The best way to understand is surely to run the query into SSMS, save the output into a file and open it in a browser. Now that you have the query in SSMS, the output in your browser, it would be beneficial to also open the output file into a plain old text editor to see all the tags. With these 3 windows opened, you can start having fun and modify the query and HTML formatting to customize it so it fits your specific needs (and your own real data).

Conclusion

I am usually a big fan of formatting data somewhere else than on the database server. But there is some exception where this tip might come in handy. For example, you might want to send a quick email from a job running from the SQL Agent or from a PowerShell script.


(Print this page)