How to make an HTML table version 2

I posted yesterday about a generating an HTML table. Overnight I  had a think as I wasn't happy with the UNION ALL bit of the query and realised you could use sub queries instead.

So here is the simplified version. NoteL I've changed the * for text() this has the same affect but doesn't cause CS to do funny things.

 

select 2 [@cellpadding]

       ,2 [@cellspacing]

       ,1 [@border]

--This returns the header

       ,(select 'Database Table' [text()] for xml path ('th'),type) tr

      

       ,(select 'Entity Count' [text()] for xml path ('th'),type) tr

       ,(select 'Total Rows' [text()] for xml path ('th'),type) tr

--This returns the rows in the table

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

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

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

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

I though you might be able to use the xpath form of the column name i.e. [tr\th] but unfortunately these are merged into one. Although on reading the documentation it refers to merging of next items. So I thought what happens if you stick a NULL value in between columns (NULLs don't appear in XML documents, there non existence implies NULL). Would you believe that this forces the engine to generate a new node.

Some might say that is a bug, well it works for me. We can therefore simplify the query further to the following. This saves a 0.005% in cost :)

 

select 2 [@cellpadding]

       ,2 [@cellspacing]

       ,1 [@border]

--This returns the header

       , (select 'Database Table' [th] , null

               , 'Entity Count' [th] , null

               , 'Total Rows' [th]

               for xml path (''),type) tr

--This returns the rows in the table

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

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

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

           from (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 Tuesday, October 28, 2008 4:44 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