SQL Server parse XML to table - multiple node with the same name and first node should be table columns

Question

I have a source xml like below and I want to convert to SQL table. First "tr" node should be columns for the table and remaining are rows.

<table>
      <tbody>
      <tr>
        <td>Description</td>
        <td>Value</td>
        <td>Reference</td>
        <td>Category</td>
        <td>Review</td>
      </tr>
      <tr>
        <td>Process Param</td>
        <td>|ABCD|</td>
        <td>Step1</td>
        <td>Process</td>
        <td>False</td>
      </tr>
      <tr>
        <td>Config Param</td>
        <td>|EFGH|</td>
        <td>Step2</td>
        <td>Config</td>
        <td>True</td>
      </tr>
      <tr>
        <td>Process Param</td>
        <td>|IJKL|</td>
        <td>Step3</td>
        <td>Process</td>
        <td>False</td>
      </tr>
    </tbody>
  </table>

Expected SQL output: Output

score 0 · Accepted Answer · answered Jul 11 '20 at 16:36

You can extract data from your html/xml structure using the nodes method provided for the xml data type (more info here).

Here is a simple query that should put you on the right track:

select 
     Tbl.Col.value('td[1]', 'varchar(50)')  
    ,Tbl.Col.value('td[2]', 'varchar(50)')  
    ,Tbl.Col.value('td[3]', 'varchar(50)')  
    ,Tbl.Col.value('td[4]', 'varchar(50)')  
    ,Tbl.Col.value('td[5]', 'varchar(50)')  
from @x.nodes('//tr') Tbl(Col)

where @x is a xml variable containing your xml:

declare @x xml = '<table>...

The problem is that the first row (that contains column headers) is returned inside the dataset:

If you want to use values in the first row as column names you'll have to write dynamic xml.

SQL Server parse XML to table - multiple node with the same name and first node should be table columns

1 Answers1