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 
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
-