How to generate a HTML table using FOR XML PATH

I have got the idea from a blog post I read ages ago and I vaguely remember the author had used raw T-SQL with lots of string manipulation. Just recently, I had a need to return result sets as HTML tables, and due to the similarity between HTML and XML, I thought I would try the FOR XML syntax.

Let’s use “msdb” for example and return a HTML table showing the following properties for the database from the sys.databases view:

  • name (sysname)
  • database_id (int)
  • source_database_id (int)
  • owner_sid (varbinary)
  • service_broker_id (uniqueidentifier)

Here we go, I executed the following query to get an idea of what the data looked like:

select 
    name
    , database_id
    , source_database_id
    , owner_sid
    , service_broker_guid 
from sys.databases
where name = 'msdb'

Then I added the FOR XML clause:

select 
    name
    , database_id
    , source_database_id
    , owner_sid
    , service_broker_guid 
from sys.databases
where name = 'msdb'
for xml path

Opened the result set in XML Editor:

<row>
  <name>msdb</name>
  <database_id>4</database_id>
  <owner_sid>AQ==</owner_sid>
  <service_broker_guid>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</service_broker_guid>
</row>

The script looked like HTML but far from a HTML table and where did source_database_id go? According to Books Online:

Columns that Contain a Null Value By Default
By default, a null value in a column maps to the absence of the attribute, node, or element. …

No worries, I can easily handle that:

select 
    name                    as 'td'
    , database_id           as 'td'
    , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td'
    , owner_sid             as 'td'
    , service_broker_guid   as 'td'
from sys.databases
where name = 'msdb'
for xml path('tr'), root('table')

Opened the result set in XML Editor:

<table>
  <tr>
    <td>msdb4NULLAQ==E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Some things worked, but some didn’t. You didn’t think it was that easy right? The <table> and <tr> tags are pretty self explanatory, so I won’t spend too much time on them. More work was obviously required for the <td> tags.

According to Books Online:

Several Columns Share the Same Path Prefix
If several subsequent columns share the same path prefix, they are grouped together under the same name. …

One Column Has a Different Name
If a column with a different name appears in between, it will break the grouping, …

In light of these mapping conditions, I could see a way around the issue I was having with the <td> tags:

  1. Insert a column with a different name in between <td> columns.
  2. Return NULL for the extra columns.

I changed the code slightly:

select 
    name                       as 'td', null
    , database_id              as 'td', null
    , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
    , owner_sid                as 'td', null
    , service_broker_guid      as 'td', null
from sys.databases
where name = 'msdb'
for xml path('tr'), root('table')

Opened the result set in XML Editor:

<table>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Alright! The data was returned in the HTML table format. I saved the script as table.htm and opened it in Internet Explorer:

image

Adding a border and a header row would make the table more user friendly, hence I rolled up my sleeves and played around with the query a bit:

select
    1 as [email protected]'
    ,(
        select
            name                     as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr')
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">&lt;tr&gt;&lt;td&gt;msdb&lt;/td&gt;&lt;td&gt;4&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;td&gt;AQ==&lt;/td&gt;&lt;td&gt;E3391D0A-74CD-4DBA-9603-48A5C9E00CED&lt;/td&gt;&lt;/tr&gt;</table>

The border attribute was added successfully but the angle brackets were gone.

According to Books Online:

TYPE Directive in FOR XML Queries

In SQL Server 2000, the result of a FOR XML query is always directly returned to the client in textual form. With support for the xml data type in SQL Server 2005, you can optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive. This allows you to process the result of a FOR XML query on the server. For example, you can specify an XQuery against it, assign the result to an xml type variable, or write Nested FOR XML queries.

The very last sentence appealed to me since I had used a nested FOR XML query. I added the TYPE directive without hesitation:

select
    1 as [email protected]'
    ,(
        select
            name                     as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Nice! Now the border issue was resolved, it’s time to add the header row:

select
    1 as [email protected]'
    ,(
        select
            'name'                  as 'th', null
            , 'database_id'         as 'th', null
            , 'source_database_id'  as 'th', null
            , 'owner_sid'           as 'th', null
            , 'service_broker_guid' as 'th', null
        for xml path('tr'), type
    )        
    ,(
        select
            name   as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <th>name</th>
    <th>database_id</th>
    <th>source_database_id</th>
    <th>owner_sid</th>
    <th>service_broker_guid</th>
  </tr>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

I tried the HTML in Internet Explorer again:

image

Mission accomplished? According to Bush it was, but not for me, I had one more battle to fight. Notice that owner_sid (varbinary) is represented in base64-encoded format? Well, can we display it in a text format? Yes, we can!

After rummaging through google search results, I found two undocumented system functions on the net:

  • sys.fn_sqlvarbasetostr
  • sys.fn_varbintohexsubstring

The first one accepts a sql_variant and returns a nvarchar(max), in other words, it converts “any” data type to string. It calls the second function if the parameter is of binary/varbinary type.

Run sp_helptext ‘sys.fn_sqlvarbasetostr’ and see for yourself:

image

I couldn’t wait to try the new toy:

select
    1 as [email protected]'
    ,(
        select
            'name'                  as 'th', null
            , 'database_id'         as 'th', null
            , 'source_database_id'  as 'th', null
            , 'owner_sid'           as 'th', null
            , 'service_broker_guid' as 'th', null
        for xml path('tr'), type
    )        
    ,(
        select
            name                                as 'td', null
            , database_id                       as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , sys.fn_sqlvarbasetostr(owner_sid) as 'td', null
            , service_broker_guid               as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <th>name</th>
    <th>database_id</th>
    <th>source_database_id</th>
    <th>owner_sid</th>
    <th>service_broker_guid</th>
  </tr>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>0x01</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Saved the script and viewed in Internet Explorer:

image

Mission accomplished.

–oo00oo–

For more info, please refer to the following blog posts:
Format query output into an HTML table – the easy way
Creating an HTML table from SQL
How to make an HTML table version 2
T-SQL Convert Hex String to Varbinary