Create HTML Table from XML in SQL Server

Question

I'm using SQL Server. I have a table that contains XML of variable length as follows:

Id | TableData
-----------------
1  |   <root><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /></root>
2  |   <root><row RowId="225936" Jobsitestate="MD" Jobsitezipcode="20762" Objective="Target" /></root>

The schema varies row to row. I'm looking to parse this into an HTML table.

I've tried How to convert xml as a table or html table in sqlserver but it returns an empty HTML table.

SELECT Id
,TableData
,dbo.CreateHTMLTable([TableData], NULL, NULL, NULL) AS HTMLData
FROM myTable

I've tried several permutations of CAST AS XML, FOR XML PATH, FOR XML RAW. I'm missing some nuance in this -- what am I failing to grasp?

Please advise.

How about an XSLT transformation on your front end side instead of trying this through SQL Server? — Sander
– Sander, Commented Sep 9, 2020 at 15:51
This data will be used in a merge in Aspose, so I don't have a front end per se. — bstub
– bstub, Commented Sep 9, 2020 at 18:45
Yes - unfortunately I'm a client and don't have access to the C# aspect of my hosting company. I provide a SQL query and a template to a blackbox that merges it. I can pass html to the template via the SQL result, so if I can get SQL to generate a HTML table from the XML data, I have a solution. — bstub
– bstub, Commented Sep 10, 2020 at 15:00

critical_error · Accepted Answer · 2020-09-09 21:10:16Z

Try something like this:

DECLARE @Data TABLE ( Id INT, TableData XML );
INSERT INTO @Data ( Id, TableData ) VALUES
    ( 1, '<root><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /></root>' ),
    ( 2, '<root><row RowId="225936" Jobsitestate="MD" Jobsitezipcode="20762" Objective="Target" /></root>' );

SELECT
    Id,
    ( '<table>' + TableHeaders.th + TableRows.td + '</table>' ) AS HtmlTable
FROM @Data
CROSS APPLY (

    SELECT (

        SELECT 
            '<tr><th>' + STRING_AGG( attr.val.value( 'local-name(.)', 'VARCHAR(50)' ), '</th><th>' ) + '</th></tr>'
        FROM TableData.nodes( '//root/row[1]' ) AS rw( fld )
        CROSS APPLY fld.nodes( '@*' ) attr( val )

    ) AS th

) AS TableHeaders
CROSS APPLY (

    SELECT (
        
        SELECT 
            STRING_AGG( '<tr>' + tds.td + '</tr>', '' ) AS rw
        FROM TableData.nodes( '//root/row' ) AS rw( fld )
        CROSS APPLY ( 
        
            SELECT 
                '<td>' + STRING_AGG( attr.val.value( '.', 'VARCHAR(50)' ), '</td><td>' ) + '</td>' AS td
            FROM fld.nodes( '@*' ) attr( val )

        ) AS tds
    
    ) AS td

) AS TableRows
ORDER BY
    Id;

Returns

+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id |                                                                                            HtmlTable                                                                                            |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | <table><tr><th>RowId</th><th>Attendance</th><th>Jobsitecity</th></tr><tr><td>225936</td><td>250</td><td>Camp Springs</td></tr><tr><td>225936</td><td>250</td><td>Camp Springs</td></tr></table> |
|  2 | <table><tr><th>RowId</th><th>Jobsitestate</th><th>Jobsitezipcode</th><th>Objective</th></tr><tr><td>225936</td><td>MD</td><td>20762</td><td>Target</td></tr></table>                            |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Id 1 HTML Formatted:

<table>
   <tr>
      <th>RowId</th>
      <th>Attendance</th>
      <th>Jobsitecity</th>
   </tr>
   <tr>
      <td>225936</td>
      <td>250</td>
      <td>Camp Springs</td>
   </tr>
   <tr>
      <td>225936</td>
      <td>250</td>
      <td>Camp Springs</td>
   </tr>
</table>

Id 2 HTML Formatted

<table>
   <tr>
      <th>RowId</th>
      <th>Jobsitestate</th>
      <th>Jobsitezipcode</th>
      <th>Objective</th>
   </tr>
   <tr>
      <td>225936</td>
      <td>MD</td>
      <td>20762</td>
      <td>Target</td>
   </tr>
</table>

Note:

I added an extra row to Id 1 to show how this handles multiple rows.

bstub · Accepted Answer · 2020-09-10 18:48:15Z

Critical Error answered my question, but I've included SQL without STRING_AGG for those who are stranded in an older version like myself.

DECLARE @Data TABLE ( Id INT, TableData XML );
INSERT INTO @Data ( Id, TableData ) VALUES
    ( 1, '<root><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /><row RowId="225936" Attendance="250" Jobsitecity="Camp Springs" /></root>' ),
    ( 2, '<root><row RowId="225936" Jobsitestate="MD" Jobsitezipcode="20762" Objective="Target" /></root>' );

SELECT Id
  ,('<table>' + TableHeaders.th + TableRows.td + '</table>') AS HtmlTable
FROM @Data
CROSS APPLY (
  SELECT (
      '<tr><th>' + STUFF((
          SELECT '</th><th>' + attr.val.value('local-name(.)', 'NVARCHAR(50)')
          FROM TableData.nodes('//root/row[1]') AS rw(fld)
          CROSS APPLY fld.nodes('@*') attr(val)
          FOR XML PATH('')
            ,TYPE
          ).value('.', 'NVARCHAR(MAX)'), 1, 9, '') + '</th></tr>'
      ) AS th
  ) AS TableHeaders
CROSS APPLY (
  SELECT (
      SELECT '<tr>' + STUFF((
            SELECT '</tr><tr>' + tds.td AS rw
            FROM TableData.nodes('//root/row') AS rw(fld)
            CROSS APPLY (
              SELECT '<td>' + STUFF((
                    SELECT '</td><td>' + attr.val.value('.', 'NVARCHAR(50)')
                    FROM fld.nodes('@*') attr(val)
                    FOR XML PATH('')
                      ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 9, '') + '</td>' AS td
              ) AS tds
            FOR XML PATH('')
              ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 9, '') + '</tr>'
      ) AS td
  ) AS TableRows
ORDER BY Id;

Collectives™ on Stack Overflow

Create HTML Table from XML in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related