Generating HTML Reports for Dynamic Table Structures

Introduction

This tip describes the most convenient way of process automation of generating HTML reports for dynamic table-structures and sending them to different people.

Background

In the relatively recent past, there was a task - to automate the process of generating HTML reports with current month's sales results and sending them to all the managers. It just so happened that for each manager a table was created with specific information required for them.

Since every action was performed manually for each report, we decided to take advantage of dbForge Studio capabilities, that allowed to export groups of tables in the HTML format.

However, mail delivery still had to be generated manually, that, to put it mildly, was irrational.

Using the Code

It was decided to generate HTML on the server side and build the mail delivery list via Database Mail by executing the sp_send_dbmail command.

Most examples on the Web were about manual HTML markup creating — it was less than an efficient approach. Thus, I haven't found a unified solution, that would allow to work with dynamic table-structures.

To fill in this gap, here is my solution.

DECLARE @object_name SYSNAME      
      , @object_id INT      
      , @SQL NVARCHAR(MAX)

SELECT @object_name = '[dbo].[Products]'
, @object_id = OBJECT_ID(@object_name)

SELECT @SQL = 'SELECT 
      [header/style/@type] = ''text/css''
    , [header/style] = ''
        table {border-collapse:collapse;} 
        td, table {
            border:1px solid silver;            
            padding:3px;
        }
        th, td {
            vertical-align: top; 
            font-family: Tahoma; 
            font-size: 8pt; 
            text-align: left;
        }''
    , body = (
SELECT * FROM (
    SELECT tr = (
        SELECT * FROM (
            VALUES ' +
            STUFF(CAST((
                SELECT ', (''' + c.name + ''')'
                FROM sys.columns c WITH(NOLOCK)
                WHERE c.[object_id] = @object_id
                    AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189,
241)
                ORDER BY c.column_id
                FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, '') + '
        ) t (th)
        FOR XML PATH('''')
    )
    UNION ALL  
    SELECT (
        SELECT * FROM (
            VALUES' + STUFF(CAST((
            SELECT ', ' + 
                CASE WHEN c.is_nullable = 1
                    THEN '(ISNULL(' ELSE '(' END +
                CASE WHEN TYPE_NAME(c.system_type_id) _
                	NOT IN ('nvarchar', 'nchar', 'varchar', 'char') 
                   
                    THEN 'CAST(' + '[' + c.name + '] AS NVARCHAR(MAX))' ELSE '[' + c.name
+ ']' END +
                CASE WHEN c.is_nullable = 1 
                    THEN ',''''))' ELSE ')' END
            FROM sys.columns c WITH(NOLOCK)
            WHERE c.[object_id] = @object_id
                AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241)
            ORDER BY c.column_id
            FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, ' ') + '
    ) t (td) 
    FOR XML PATH(''''), TYPE) 
    FROM ' + @object_name + '
) t
FOR XML PATH(''''), ROOT(''table''), TYPE
)
FOR XML PATH(''''), ROOT(''html''), TYPE'

PRINT @SQL
EXEC sys.sp_executesql @SQL

Next, use dynamic SQL to create a query, that generates XML:

SELECT 
      [header/style/@type] = 'text/css'
    , [header/style] = 'css style ...'
    , body = (
        SELECT * 
        FROM (
            SELECT tr = (
                SELECT * 
                FROM (
                    VALUES ('column_name1', 'column_name2', ...)
                ) t (th)
                FOR XML PATH('')
            )
            UNION ALL 
            SELECT (
                SELECT * 
                FROM (
                    VALUES ([column_value1], [column_value2], ...) 
                )t (td) 
                FOR XML PATH(''), TYPE
            ) 
            FROM [table]
        ) t
        FOR XML PATH(''), ROOT('table'), TYPE
    )
FOR XML PATH(''), ROOT('html'), TYPE

At this, columns containing specific data types (e.g., UNIQUEIDENTIFIER) are not included into the generated report:

SELECT name 
FROM sys.types
WHERE user_type_id IN (
    34, 36, 98, 
    128, 129, 130, 
    165, 173, 189, 241
)

On query execution, we get the following HTML markup, that is attached to the mail:

<html>
  <header>
    <style type="text/css">
        ...
    </style>
  </header>
  <body>
      
        ...
    <table>
      <tbody><tr>
        <th>column_name1</th>
        <th>column_name2</th></tr>
      <tr>
        <td>column_value1</td>
        <td>column_value2</td></tr></tbody></table>
  </body>
</html>

In order not to execute this script manually every week, a Job was added to SQL Agent, that automatically generated and sent reports.

I hope the solution provided here will be useful to resolve similar tasks.

P.S.: The VALUES multiline statement appeared only in SQL Server 2008. So here is an example of the same script for SQL Server 2005:

DECLARE @object_name SYSNAME
      , @object_id INT
      , @SQL NVARCHAR(MAX)

SELECT @object_name = '[dbo].[Products]'
     , @object_id = OBJECT_ID(@object_name)

SELECT @SQL = 'SELECT 
      [header/style/@type] = ''text/css''
    , [header/style] = ''
        table {border-collapse:collapse;} 
        td, table {
            border:1px solid silver; 
            padding:3px;
        }
        th, td {
            vertical-align: top; 
            font-family: Tahoma; 
            font-size: 8pt; 
            text-align: left;
        }''
    , body = (
SELECT * FROM (
    SELECT tr = (
        SELECT * FROM (
            ' +
            STUFF(CAST((
                SELECT ' UNION ALL SELECT ''' + c.name + ''''
                FROM sys.columns c WITH(NOLOCK)
                WHERE c.[object_id] = @object_id
                    AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241)
                ORDER BY c.column_id
                FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT th =') + '
        ) t
        FOR XML PATH('''')
    )
    UNION ALL  
    SELECT  (
        SELECT * FROM (
            ' + STUFF(CAST((
            SELECT ' UNION ALL SELECT ' + 
                CASE WHEN c.is_nullable = 1
                    THEN 'ISNULL(' ELSE '' END +
                CASE WHEN TYPE_NAME(c.system_type_id) _
                	NOT IN ('nvarchar', 'nchar', 'varchar', 'char') 
                    THEN 'CAST(' + '[' + c.name + '] _
                    	AS NVARCHAR(MAX))' ELSE '[' + c.name + ']' END +
                CASE WHEN c.is_nullable = 1 
                    THEN ','''')' ELSE '' END
            FROM sys.columns c WITH(NOLOCK)
            WHERE c.[object_id] = @object_id
                AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241)
            ORDER BY c.column_id
            FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT td =') + '
    ) t
    FOR XML PATH(''''), TYPE) 
    FROM ' + @object_name + '
) t
FOR XML PATH(''''), ROOT(''table''), TYPE
)
FOR XML PATH(''''), ROOT(''html''), TYPE'

PRINT @SQL
EXEC sys.sp_executesql @SQL