Generating HTML from SQL Server Queries

You can produce HTML from SQL because SQL Server has built-in support for outputting XML, and HTML is best understood as a slightly odd dialect of XML that imparts meaning to predefined tags. There are plenty of edge cases where an HTML structure is the most obvious way of communicating tables, lists and directories. Where data is hierarchical, it can make even more sense. William Brewer gives a simple introduction to a few HTML-output techniques.

Can you produce HTML from SQL? Yes, very easily. Would you ever want to? I certainly have had to. The principle is very simple. HTML is really just a slightly odd dialect of XML that imparts meaning to predefined tags. SQL Server has built-in ways of outputting a wide variety of XML. Although I’ve had in the past to output entire websites from SQL, the most natural thing is to produce HTML structures such as tables, lists and directories.

HTML5 can generally be worked on in SQL as if it were an XML fragment. XML, of course, has no predefined tags and is extensible, whereas HTML is designed to facilitate the rendering and display of data. By custom, it has become more forgiving than XML, but in general, HTML5 is based on XML.

Generating Tables from SQL expressions.

In HTML5, tables are best done simply, but using the child elements and structures so that the web designer has full control over the appearance of the table. CSS3 allows you to specify sets of cells within a list of child elements. Individual TD tags, for example, within a table row (TR) can delimit table cells that can have individual styling, but the rendering of the table structure is quite separate from the data itself.

The table starts with an optional caption element, followed by zero or more colgroup elements, followed optionally by a thead element. This header is then followed optionally by a tfoot element, followed by either zero or more tbody elements or one or more tr elements, followed optionally by a tfoot element, but there can be only one tfoot element.

The HTML5 ‘template’ for tables

In SQL Server, one can create the XML for a table like this with this type of query which is in the form of a template with dummy data.

Which produces (after formatting it nicely) this

So, going to AdventureWorks, we can now produce a table that reports on the number of sales for each city, for the top thirty cities.

I’ve left out the tfoot row because I didn’t need that. Likewise colgroup. I use tfoots mostly for aggregate lines, but you are limited to one only at the end, so it is not ideal for anything other than a simple ‘bottom line’.

When this is placed within and html file, with suitable CSS, it can look something like this

This is fine for general purposes, but there often comes a time when you need to highlight particular cells, based on an attribute from the data or a value. We can add, for example, class attributes, or anything else you need but you sacrifice the simplicity of  FOR XML RAW. You will need to use FOR XML PATH, I reckon. This allows you to specify attributes (necessary for images and links) . However, because you are specifying the path in the AS clause, all your contiguous TD names will all get concatenated into one TD element unless you use a hack to indicate that they are separate.  This is easier demonstrated than explained. 

Yes, we have altered the previous SQL to create ‘class’ attributes that have allowed us to colour the rank order numbers we’ve just added so that the best performers are in red, the next best in mauve and the rest in dark blue; first column only. It will look something like this…

sqlhtml

Obviously, you can use this for any HTML tag that requires attribute. An image IMG tag needs its ‘src‘, for example, and the anchor A tag needs its ‘href‘.  The only downside is that you lose the neatness of using RAW.  HTML mixes attributes and elements so I suspect that you will need to use the FOR XML PATH syntax for this sort of work.

Generating directory lists from SQL expressions.

The HTML is for rendering name-value groups such as dictionaries, indexes, definitions, questions and answers and lexicons. The name-value group consists of one or more names (dt elements) followed by one or more values (dd elements). Within a single dl element, there should not be more than one dt element for each name.

We’ll take as an example an excerpt from the excellent SQL Server glossary

This produces a directory list which can be rendered as you wish

Generating hierarchical lists from SQL expressions.

HTML Lists represent probably the most useful way of passing simple hierarchical data to an application. You can actually use directories (DLs) to do this for lists name-value pairs and even tables for more complex data. Here is a simple example of a hierarchical list, generated from AdventureWorks. You’d want to use a recursive  function for anything more complicated. I’ll show you this in a moment. Here is a simple version that just gives you one level

…giving…

There is a more complex hierarchy in AdventureWorks that we can use to show how a hierarchy with arbitrary depth  can be rendered as a list.  Here is how you could do it. Firstly the recursive function

Now we can simply demonstrate how to call it….

and this will give you the list  that then just needs the base <UL> node

Hierarchy

Conclusions

There are quite a few structures now in HTML5. Even the body tag has subordinate header, nav, section, article, aside, footer, details and summary tags. If you read the W3C Recommendation it bristles with good suggestions for using markup to create structures. The pre tag can use child code, samp and kbd tags to create intelligent formatting. Data in SQL Server can easily generate this sort of structured HTML5 markup. This has obvious uses in indexes, chaptering, glossaries as well as the obvious generation of table-based reports. There is quite a lot of mileage in creating HTML from SQL Server queries

  • 10025 views

  • Rate
    [Total: 13    Average: 4.7/5]
  • ma ku

    simply clever, thumbs up!

  • Cool topic, William! There are some email responses to SQL agent jobs I’ve developed that are essentially ‘mini-reports’ that were well served to be sent via HTML formats for easier readability. Good stuff, thank you for the article!

  • CavemanTechno

    Clever. SQL is a programming language.
    Please do this only with lots of additional memory or DB2 which has different buffer pools for different data streams.
    My concerns is that running programs in SQL hurts the real function of SQL – data retrieval.
    Why? The buffer pool for tables will be used for programming making more of a web server than a database server.
    That said – there are many different customer situations. HTML processing may be a good solution. “Every time I think I’ve seen it all – I find out otherwise 🙂 ”
    Mr. Brewer – Cheers to you and “SQL – its bigger on the inside than on the outside – it is just not blue 🙂 “

    • William Brewer

      These queries are merely generating XML, as do many well-mannered queries. These HTML structures are merely data. I don’t quite see how they would stress out the buffer pools any more than an XML query result, though I agree that DB2s design seems more sensible in having separate XML buffer pools. There is no real extra baggage as compared with other types of XML, or LOB, and although the results are a bit more bulky than TDS, they aren’t going to stress out the network unless someone goes slightly postal with the technique.
      I’ll agree that if you get the design wrong with an outward-facing website, you could end up with a very frazzled server. Mind you, I’ve seen far worse crimes committed on SQL Server via ASP.NET. Most people seem to use these techniques for email-based scheduled reporting, which is pretty-well controlled.

  • brad_schulz

    Nice article, William.

    I talked briefly about this several years ago: http://bradsruminations.blogspot.com/2009/11/xml-paths-of-glory.html

    I use this technique now and then for sending out HTML emails that need tabular information.

    • William Brewer

      Brad, You’ve got some really good techniques there that I haven’t described in this introduction. Anyone who is interested in taking these techniques further should read your blog. Especially useful were your way of creating links, inserting non-breaking spaces, and, more generally, in handling attributes, which I didn’t cover.

  • Nick

    Some 15 years ago I faced this HTML generation challenge on industrial
    scale when I was tasked with writing 40 aspx reports. Obviously that was a
    repetitive task, so I ended up with a single data-driven web page that would
    take parameters for a report and show one or more tables.

    Back then there was no SELECT FOR XML, and in terms of
    requirements (try rowspan straight from the query) and volume, generating HTML
    from database won’t cut.

    These days I have polished with time control that renders proper
    HTML with styles and from DataSet or DataTable object. It even renders controls
    to save user input back into database. I grew so lazy with such productivity
    tool that rarely use what .NET platform has to offer. If someone is longing for
    a gem like that, drop me a line at http://eai.systems

  • Wagner Bezerra

    Hi All

    I’m using a simple code to generate a HTML table in body mail, however, this mail when received com with a very big size to download… Any one can help me to solve this problem with size mail to download..

    My code:

    DECLARE @tableHTML NVARCHAR(MAX) ;

    SET @tableHTML =
    N”+
    N’Work Order Report’ +
    N” +
    N’ INSCRIÇÃO NOME ENDEREÇO TELEFONE ‘ +
    CAST ( ( SELECT td = ACAWADVO_INSCRICAO+”+ACAWADVO_TIPO_INSCRICAO,
    td = ACAWADVO_NOME,”,
    td = Isnull(Cast(ACAWADVO_ENDERECO+’ ‘+ACAWADVO_BAIRRO+’-‘+ACAWADVO_CIDADE+’/’+ACAWADVO_ESTADO +’ CEP:’+ACAWADVO_CEP as Nvarchar(250)),”),
    td = IsNull(ACAWADVO_FONE,”) +’ – ‘+ IsNull(ACAWADVO_CELULAR,”), ”
    FROM BISA_CAW.DBO.ACAWADVO
    where ACAWADVO_SEXO = ‘2’ and ACAWADVO_TIPO_INSCRICAO = ‘D’
    FOR XML PATH(‘tr’), TYPE
    ) AS NVARCHAR(MAX) ) +
    N”+
    N” ;

    –print @tableHTML

    EXEC msdb.dbo.sp_send_dbmail
    @recipients=’wagner@oabpe.org.br’,
    @subject = ‘SQL Errors Report’,
    @body = @tableHTML,
    @body_format = ‘HTML’,
    @profile_name = ‘Mail’

  • ACilliers

    Love the article. Thank you. Would you mind making the CSS Style Sheets available used in these examples?