Convert SQL Server table into HTML using TSQL

Question

Below code works fine and convert table to HTML. It gives the results as HTML tables but it ignore HEADER (Column names), show only ROW values

I want to display Table-Column as first header in HTML table...

CREATE PROC dbo.usp_ConvertQuery2HTMLTable (
@SQLQuery NVARCHAR(3000))
AS
BEGIN
   DECLARE @columnslist NVARCHAR (1000) = ''
   DECLARE @restOfQuery NVARCHAR (2000) = ''
   DECLARE @DynTSQL NVARCHAR (3000)
   DECLARE @FROMPOS INT
   DECLARE @out table
        (
        out nvarchar(max)
        )


   SET NOCOUNT ON

   SELECT @columnslist += 'ISNULL (' + NAME + ',' + '''' + ' ' + '''' + ')' + ','
   FROM sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)

   SET @columnslist = left (@columnslist, Len (@columnslist) - 1)
   SET @FROMPOS = CHARINDEX ('FROM', @SQLQuery, 1)
   SET @restOfQuery = SUBSTRING(@SQLQuery, @FROMPOS, LEN(@SQLQuery) - @FROMPOS + 1)
   SET @columnslist = Replace (@columnslist, '),', ') as TD,')
   SET @columnslist += ' as TD'
   SET @DynTSQL = CONCAT (
         'SELECT (SELECT '
         , @columnslist
         ,' '
         , @restOfQuery
         ,' FOR XML RAW (''TR''), ELEMENTS, TYPE) AS ''TBODY'''
         ,' FOR XML PATH (''''), ROOT (''TABLE'')'
         )

   PRINT @DynTSQL

   EXEC (@DynTSQL)

   SET NOCOUNT OFF
END

I use [this function](https://stackoverflow.com/a/39487565/6167855) often for sending table results with dbmail, and you can even style it with CSS. If you want to see how I use it, you can check it out on [GitHub](https://github.com/scsimon/sqlserver/blob/master/ufn_createHTMLtable) — S3S, Oct 16 '18 at 13:09
@scsimon - can you add this as ANSWER. Great script, solved my problem — user3657339, Oct 16 '18 at 14:04

score 0 · Accepted Answer · answered Oct 16 '18 at 14:06

There is a script created by Shnugo which can be found here with his comments, and methods to call it. I specifically use to build a HTML table when I need to send results with DBMAIL.

Here's how

/************************************************************************************************
Function to build a HTML table.

Created by Shnugo on SO: https://stackoverflow.com/a/39487565/6167855

Usage:

declare @body xml = 
    (select AdminTools.dbo.ufn_CreateHTMLTable (
    (select * from SomeTable for xml path ('row'),elements xsinil)
    ,null,null,null))

--CSS to make borders, which makes us change datatype
declare @body_html varchar(max)
set @body_html =  
    '<style type="text/css" media="screen,print">
    .center
    {
        text-align: center;
    }
    table,th
    {
        border: 1px solid black;
    }
    table,tr
    {
        border: 1px solid black;
    }
    table,td
    {
        border: 1px solid black;
    }
    </style>' 
    + cast(@body as varchar(max))

Then @body_html for sp_send_dbmail

************************************************************************************************/

CREATE FUNCTION [dbo].[ufn_CreateHTMLTable]
(
    @SelectForXmlPathRowElementsXsinil XML
   ,@tblClass VARCHAR(100) --NULL to omit this class
   ,@thClass VARCHAR(100)  --same
   ,@tbClass VARCHAR(100)  --same
)
RETURNS XML
AS
BEGIN

RETURN 
(
    SELECT @tblClass AS [@class]  
    ,@thClass AS [thead/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
              N'let $first:=/row[2]
                return 
                <tr> 
                {
                for $th in $first/*
                return <th>{if(not(empty($th/@caption))) then xs:string($th/@caption) else local-name($th)}</th>
                }
                </tr>') AS thead
    ,@tbClass AS [tbody/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
               N'for $tr in /row
                 return 
                 <tr>{$tr/@class}
                 {
                 for $td in $tr/*
                 return
                 if(empty($td/@link)) 
                 then <td>{$td/@class}{string($td)}</td>
                 else <td>{$td/@class}<a href="{$td/@link}">{string($td)}</a></td>
                 }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE
) 
END

GO

Convert SQL Server table into HTML using TSQL

1 Answers1