Creating an HTML table from SQL

There used to be a stored proc you could use to create an HTML table from a query. The only time I came across this was on a training course 8 years ago and then I didn't see the point.

With the introduction of XML support in SQL it's eay to create an HTML table, which is good as the stored proc is now deprecated.

Tony's blogged an option http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx I've extended it further using some little tricks.

The outer query returns the table element and its attributes. The attributes are specified by prefixing the column names with "@".

We then use a nested query to return the header. The column name is "hr" which results in elements called <th>, because we are using a path ('') no element is wrapped around each row. This nested query has a name of tr which means the xml fragment the query returns is enclosed in a "tr" element

To put the rows in the table we have to turn each row into a tr element and each column into a td element. This is a challenge because if you give each column an alias of td you only get one element.

However you can use a trick which is to use a subquery that converts a value into an element. The use of the Star tells the engine that this value should be a text node of the element. (We could have used this trick on the header row as well. Each row returned by the query is then turned into a tr element using the path notation.

It is cruical to use the "type" directive when using nested XML. If you don't then you will end up with your XML being encoded and included as text. (If you find odd characters in your XML then this could be the cause)

select 2 [@cellpadding]

      ,2 [@cellspacing]

      ,1 [@border]

 

--This returns the header

        ,(select th

            from (select 'Database Table' th

               union all

                  select 'Entity Count'

               union all

                  select 'Total Rows') d --name of this alias is irrelevant but you have to have one

          for xml path(''),type) tr --tr here defines that the header row will be a tr element

 

--This returns the rows in the table

        ,(select (select dbtable [ *]  for xml path('td'),type),

                 (select entities [ *] for xml path('td'),type),

                 (select rows [ *] for xml path('td'),type)

            from (--We have to use a derived table because we are grouping

                  select dbtable  = object_name( object_id ),

                         entities = count( distinct name ),

                         rows     = count( * )

                    from sys.columns

                group by object_name( object_id )) data --name of this alias is irrelevant but you have to have one

 

           for xml path ('tr'),type) --path('tr') turns each row into a tr element

 for xml path('table'), type



-
Published Sunday, October 26, 2008 10:13 PM by simonsabin

Comments

# How to generate a HTML table using FOR XML PATH | LouieBao.Net

Pingback from  How to generate a HTML table using FOR XML PATH | LouieBao.Net

# Return Select Statement as formatted HTML | PHP Developer Resource

Pingback from  Return Select Statement as formatted HTML | PHP Developer Resource