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!