Background I have an XML column in my SQL table (using SQL Server). Each node has a different amount of metadata. For example, in the below example, Step Number 1 has the only "No" as metadata while, Step Number 2 additionally has RBuffer.
<Step No="1" >Step Number 1</Step>
<Step No="2" RBuffer="6000">Step Number 2</Step>
<Step No="3" Macro="5">Step Number 3</Step>
Expected Output
I'd like to extract this metadata dynamically while also grabbing the value. For the example above, this would look like the below table. Importantly, it shouldn't matter how many metadata tags there are, I want it to go through all of them. Some of my data has 10+ tags.
| Node | Step | Key | Value |
|---|---|---|---|
| Step | 1 | Value | Step Number 1 |
| Step | 2 | RBuffer | 6000 |
| Step | 2 | Value | Step Number 2 |
| Step | 3 | Macro | 5 |
| Step | 3 | Value | Step Number 3 |
Work so far
So far, I've been able to extract the metadata in a static way:
SELECT o.value('@No', 'varchar(32)') [Step]
,o.value('@Macro', 'varchar(32)') [Macro]
,o.value('@RBuffer', 'varchar(32)') [RBuffer]
,o.value('(text())[1]', 'varchar(32)') [Action]
FROM [dbo].[dw_mrd_vss_rundetail_stg] S
CROSS APPLY S.[rundata_detail].nodes('Step') xmlData(o)
Which gives the following table:
| Step | Macro | RBuffer | Action |
|---|---|---|---|
| 1 | NULL | NULL | Step Number 1 |
| 2 | NULL | 6000 | Step Number 2 |
| 3 | 5 | NULL | Step Number 3 |
But I have to explicitly call each value and creating columns in this way isn't scalable. Any help would be appreciated. I am relatively new to this kind of data munging in SQL, so explanations of code would be helpful.
A dynamic solution. If the "No" attribute is optional too and a node name is varying as well,
Declare @xml Xml = '<doc>
<Step No="1" >Step Number 1</Step>
<Step No="2" RBuffer="6000">Step Number 2</Step>
<Step No="3" Macro="5">Step Number 3</Step>
<Step Macro="7">Step Number 4</Step>
<Node No="5">Step Number 5</Node>
</doc>';
select x.*
from @xml.nodes('/doc/*') d(dn)
cross apply (
-- element data and "No" attr
select n.value('local-name(.)', 'varchar(32)') [node], 'Value' [Key], n.value('@No', 'varchar(32)') [Step], n.value('(text())[1]', 'varchar(32)') [Value]
from d.dn.nodes('.') s(n)
union all
-- attributes data but "No"
select n.value('local-name(../.)', 'varchar(32)') [node], n.value('local-name(.)', 'varchar(32)') [Key], n.value('../@No', 'varchar(32)') [Step], n.value ('data(.)', 'varchar(32)') [Value]
from d.dn.nodes('./@*[local-name(.)!="No"]') a(n)
) x
Returns
node Key Step Value
Step Value 1 Step Number 1
Step Value 2 Step Number 2
Step RBuffer 2 6000
Step Value 3 Step Number 3
Step Macro 3 5
Step Value Step Number 4
Step Macro 7
Node Value 5 Step Number 5