Generating HTML reports for dynamic table-structures
In the relatively recent past, the task arose of automating the process of generating and sending HTML reports to sales executives for the current month. It just so happened that for each leading person separate tables were created with the information they needed only.
Since, for each report, everything was done manually, which, to put it mildly, was irrational.
It was decided to generate HTML from the server side of the database and form mailing through Database Mail by executing the command sp_send_dbmail .
')
Most of the examples provided on the web created manual markup - this was not a very efficient approach. At the same time, I did not find a universal solution that allows working with a table that has an arbitrary structure.
To fill this gap, I propose to consider my version of the solution.
From the system view, we get the list of columns for the required table:
Next, create a query using dynamic SQL that generates XML :
In this case, columns containing specific data types (for example, UNIQUEIDENTIFIER ) do not include in the generated report:
When executing the request, we get the following HTML markup that was attached to the letter:
In order not to manually execute this script every week, a Job was added to SQL Agent , which automatically generated and sent reports.
I hope that the solution given here will be useful in solving such problems.
PS: The multi-line construction of VALUES appeared only in SQL Server 2008 , therefore, to save time, I give an example of the same script, but for the 2005 server:
Since, for each report, everything was done manually, which, to put it mildly, was irrational.
It was decided to generate HTML from the server side of the database and form mailing through Database Mail by executing the command sp_send_dbmail .
')
Most of the examples provided on the web created manual markup - this was not a very efficient approach. At the same time, I did not find a universal solution that allows working with a table that has an arbitrary structure.
To fill this gap, I propose to consider my version of the solution.
From the system view, we get the list of columns for the required table:
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, create a query using dynamic SQL 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 In this case, columns containing specific data types (for example, UNIQUEIDENTIFIER ) do not include in the generated report:
SELECT name FROM sys.types WHERE user_type_id IN ( 34, 36, 98, 128, 129, 130, 165, 173, 189, 241 ) When executing the request, we get the following HTML markup that was attached to the letter:
<html> <header> <style type="text/css"> ... </style> </header> <body> <table> <tr> <th>column_name1</th> <th>column_name2</th> ... </tr> <tr> <td>column_value1</td> <td>column_value2</td> ... </tr> </table> </body> </html> In order not to manually execute this script every week, a Job was added to SQL Agent , which automatically generated and sent reports.
I hope that the solution given here will be useful in solving such problems.
PS: The multi-line construction of VALUES appeared only in SQL Server 2008 , therefore, to save time, I give an example of the same script, but for the 2005 server:
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 Source: https://habr.com/ru/post/203076/
All Articles