Arbitrary SQL Server Table to XHTML Table

Question

I saw on a website, a guy had returned his database table information as an html table. It was pretty neat. His code is as follows:

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

I would like to see if it is possible to return an html table from one arbitrary SQL Server table. I know that people have differing opinions on whether or not there is a best way to do this, that is not however the point of this question. I am new to SQL and I am just trying to learn my way around it. My code is as follows.

select dataid as [@id], *
from   table_1
for    xml path('tr'), root('table')

It returns something like:

<table>
  <tr id="1">
    <column_1>data</column_1>
    <column_2>data</column_2>
  </tr>
  <tr id="2">
    <column_1>data</column_1>
    <column_2>data</column_2>
  </tr>
</table>

I would prefer output like:

<table>
  <tr id="1">
    <td>data</td>
    <td>data</td>
  </tr>
  <tr id="2">
    <td>data</td>
    <td>data</td>
  </tr>
</table>

Is this doable in an SQL Server Transaction? If so, how would it be done?

Answer 1

maybe this is a good use of XSLT

Answer 2

I'm really not sure this is a good thing to do. The principle of separation of concerns (or "encapsulation") implies a database engineer should be concerned with data, not presentation; that is to say, a SQL query should return information which is then formatted for display by a separate view. Returning HTML from a SQL procedure moves more work to the database server, requires changing the database when a new version of HTML comes around or you want a new table format, confuses the DBA...

I don't think this is a good way of doing business.

Answer 3

It's ugly, but I figured it out...

declare @tds nvarchar(max),
        @sql nvarchar(max),
        @table char(10)
set @sql = 'select(select td from( '
set @table = 'table_2'
set @tds = ''

select @tds = @tds + 'union all select cast([' + Column_name + '] as nvarchar(max)) ' + char(13) + char(10)
FROM    INFORMATION_SCHEMA.Columns
WHERE   TABLE_NAME = @table

set @tds = stuff(@tds, 1, 10, '')
set @tds = stuff(@tds, charindex('))',@tds, 1), 2, ')) td ')

set @sql = @sql + @tds

SET @sql = @sql + ')something_here for xml path(''''), type) from table_2 '
SET     @sql = @sql + ' FOR XML PATH(''tr''), ROOT(''tbody''), TYPE'

EXEC SP_EXECUTESQL @sql