Extract nested XML data with inconsistent attributres and elements in SQL Server

Question

I have an existing table with 2 columns: unique ID, data in xml format. Latter has nested structure but not standardised (see example below). Would like to extract all xml info for each ID into the format:

ID | Variable | Value  

The existing table is arranged as:

ID |   Data 
---+-------------------------------------------------------
1  |   <ID><Input_CCY style="Input"> USD </Input_CCY>.....
2  |   <ID><Input_CCY style="Input"> GBP </Input_CCY>.....

Data column is of XML type, an example for each row would be as follows, though the number of fields would be different per each ID:

<risk>
  <Input_CCY style="Input">USD</Input_CCY>
  <Input_LastYear style="Output" formula="=SUM(E65:E68)">500</Input_LastYear>
  <table name="info" header_row="1">
    <Row name="" iRow="2">
      <Col style="Input" name="Ref" iCol="1" />AAA</Col>
      <Col style="Input" name="Location" iCol="2">London</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Input" name="Ref" iCol="1" />BBB</Col>
      <Col style="Input" name="Location" iCol="2">Edinburgh</Col>
    ...
    </Row>
    ...
  </table>
  <table name="summary" header_row="1">
    <Row name="" iRow="2">
      <Col style="Output" name="Status" iCol="1" />Amber</Col>
      <Col style="Output" name="Referral_Bonus" iCol="2">No</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Normal" name="Status" iCol="1" />Green</Col>
      <Col style="Normal" name="Referral_Bonus" iCol="2">YES</Col>
    ...
    </Row>
    ...
  </table>
</risk>

I have tried using CROSS APPLY method in SQL to extract the single cells info first, which worked fine:

SELECT 
    T.[ID] as [ID], 
    a.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_1_Name],
    a.b.value('.', 'NVARCHAR(100)') As [Node_1_Value] -- value,
    c.b.value('.', 'NVARCHAR(100)') As [Node_1_Type] -- value
FROM 
    [dbo].[all] AS T
CROSS APPLY 
    T.[data].nodes('/risk/*') AS a(b) 
OUTER APPLY 
    a.b.nodes('@*') AS c(b) 
WHERE
    a.b.value('fn:local-name(.)', 'VARCHAR(100)') != 'table' -- tables

This query returns:

ID  |   Node_1_Name   | Node_1_Value  |  Node_1_Type
----+-----------------+---------------+----------------
1   |   Input_CCY     | USD           |  Input
1   |   Input_LastYear| 500           |  Output
1   |   Input_LastYear| 500           |  =SUM(E65:E68)
2   |   Input_CCY     | GBP           |  Input
...

But when I add in a few more levels of CROSS APPLY to drill down into the nested level for the tables:

SELECT 
    T.[ID] as [ID], 
    a.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_1_Name],
    c.b.value('.', 'NVARCHAR(100)') As [Node_1_Attribute_Value], 
    g.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_2_Name],
    h.b.value('.', 'NVARCHAR(100)') As [Node_2_Attribute_Value], 
    e.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_3_Name], 
    e.b.value('.', 'NVARCHAR(100)') As [Node_3_Element], 
    f.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_3_Attribute], 
    f.b.value('.', 'NVARCHAR(100)') As [Node_3_Attribute_Value] 
FROM 
    [dbo].[all] AS T
CROSS APPLY 
    T.[data].nodes('/risk/*') AS a(b) 
OUTER APPLY 
    a.b.nodes('@*') AS c(b) 
CROSS APPLY 
    T.[data].nodes('/risk/table/*') g(b) 
OUTER APPLY 
    g.b.nodes('@name') h(b) 
CROSS APPLY 
    T.[data].nodes('/risk/table/Row/*') e(b) 
OUTER APPLY 
    e.b.nodes('@name') f(b) 
WHERE
    a.b.value('fn:local-name(.)', 'VARCHAR(100)') = 'table' 
    AND c.b.value('fn:local-name(.)', 'NVARCHAR(100)') = 'name'
    AND f.b.value('fn:local-name(.)', 'NVARCHAR(100)') = 'name' 

The resulting table seems to have tons of duplicates in there. For example the first table would have 30k+ rows (should've been a 8 x 24 table = 192 rows) even for a single ID (i.e. 1 row in the original dataset). I suspect I'm misusing the CROSS APPLY, but can't figure it out.

This is the format I'd like the table elements to be extracted to:

ID   |    Table Name    |   Row#   |    Row Name   |   Col#   |   Col Name    |   Value  | Type (input/output)

Or perhaps I should be using some other functions entirely?

I split the coding for the single-cells and tables nodes as I thought it would be easier to code separately for them first given their structures before re-combining them later on. If there's a way to do it in one hit then that's more than welcome!

Answer 1

The whole thing depends on what you know in advance.:

A mockup to simulate your issue:

DECLARE @YourTable TABLE(ID INT IDENTITY, [Data] XML);
INSERT INTO @YourTable VALUES
(N'<risk>
  <Input_CCY style="Input">USD</Input_CCY>
  <Input_LastYear style="Output" formula="=SUM(E65:E68)">500</Input_LastYear>
  <table name="info" header_row="1">
    <Row name="" iRow="2">
      <Col style="Input" name="Ref" iCol="1" >AAA</Col>
      <Col style="Input" name="Location" iCol="2">London</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Input" name="Ref" iCol="1" >BBB</Col>
      <Col style="Input" name="Location" iCol="2">Edinburgh</Col>
    ...
    </Row>
    ...
  </table>
  <table name="summary" header_row="1">
    <Row name="" iRow="2">
      <Col style="Output" name="Status" iCol="1" >Amber</Col>
      <Col style="Output" name="Referral_Bonus" iCol="2">No</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Normal" name="Status" iCol="1" >Green</Col>
      <Col style="Normal" name="Referral_Bonus" iCol="2">YES</Col>
    ...
    </Row>
    ...
  </table>
</risk>');

--This query will return the columns as EAV.
--This is good, if you do not know the tables and columns in advance.
--I would suggest to load the returned set into a staging table and proceed from there:

SELECT t.ID
      ,t.[Data].value('(/risk/Input_CCY/@style)[1]','nvarchar(100)') AS Input_CCY_Style
      ,t.[Data].value('(/risk/Input_CCY/text())[1]','nvarchar(100)') AS Input_CCY_Content
      ,t.[Data].value('(/risk/Input_LastYear/@style)[1]','nvarchar(100)') AS Input_LastYear_Style
      ,t.[Data].value('(/risk/Input_LastYear/@formula)[1]','nvarchar(100)') AS Input_LastYear_Formula
      ,t.[Data].value('(/risk/Input_LastYear/text())[1]','nvarchar(100)') AS Input_LastYear_Content
      ,tbl.value('@name','nvarchar(250)') AS Table_Name
      ,rw.value('@iRow','int') AS Row_iRow
      ,cl.value('@name','nvarchar(250)') AS [Col_Name]
      ,cl.value('@iCol','nvarchar(250)') AS Col_iCol
      ,cl.value('text()[1]','nvarchar(150)') AS Col_Content
FROM @YourTable t
CROSS APPLY t.[Data].nodes('/risk/table') A(tbl)
CROSS APPLY tbl.nodes('Row') B(rw)
CROSS APPLY rw.nodes('Col') C(cl);

--This query will pick the columns by known names.
--As I put it here, you will get everything, but all columns return as NULL, if they do not exist in the actual table.

SELECT t.ID
      ,t.[Data].value('(/risk/Input_CCY/@style)[1]','nvarchar(100)') AS Input_CCY_Style
      ,t.[Data].value('(/risk/Input_CCY/text())[1]','nvarchar(100)') AS Input_CCY_Content
      ,t.[Data].value('(/risk/Input_LastYear/@style)[1]','nvarchar(100)') AS Input_LastYear_Style
      ,t.[Data].value('(/risk/Input_LastYear/@formula)[1]','nvarchar(100)') AS Input_LastYear_Formula
      ,t.[Data].value('(/risk/Input_LastYear/text())[1]','nvarchar(100)') AS Input_LastYear_Content
      ,tbl.value('@name','nvarchar(250)') AS Table_Name
      ,rw.value('@iRow','int') AS Row_iRow

      ,rw.value('(Col[@name="Ref"]/@iCol)[1]','nvarchar(150)') AS Col_Ref_iCol
      ,rw.value('(Col[@name="Ref"]/@style)[1]','nvarchar(150)') AS Col_Ref_Style
      ,rw.value('(Col[@name="Ref"]/text())[1]','nvarchar(150)') AS Col_Ref_Content

      ,rw.value('(Col[@name="Location"]/@iCol)[1]','nvarchar(150)') AS Col_Location_iCol
      ,rw.value('(Col[@name="Location"]/@style)[1]','nvarchar(150)') AS Col_Location_Style
      ,rw.value('(Col[@name="Location"]/text())[1]','nvarchar(150)') AS Col_Location_Content

      ,rw.value('(Col[@name="Status"]/@iCol)[1]','nvarchar(150)') AS Col_Status_iCol
      ,rw.value('(Col[@name="Status"]/@style)[1]','nvarchar(150)') AS Col_Status_Style
      ,rw.value('(Col[@name="Status"]/text())[1]','nvarchar(150)') AS Col_Status_Content

      ,rw.value('(Col[@name="Referral_Bonus"]/@iCol)[1]','nvarchar(150)') AS Col_Referral_Bonus_iCol
      ,rw.value('(Col[@name="Referral_Bonus"]/@style)[1]','nvarchar(150)') AS Col_Referral_Bonus_Style
      ,rw.value('(Col[@name="Referral_Bonus"]/text())[1]','nvarchar(150)') AS Col_Referral_Bonus_Content
FROM @YourTable t
CROSS APPLY t.[Data].nodes('/risk/table') A(tbl)
CROSS APPLY tbl.nodes('Row') B(rw);

If you know all your tables and columns in advance, I'd suggest to take the second appraoch, but one statement per table separately.