TSQL - Parse XML metadata & values dynamically

Question

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.

@lptr, looks like an answer.

Serg
– Serg

2021-11-23 18:36:06 +00:00
Commented Nov 23, 2021 at 18:36 — Serg
– Serg, Commented Nov 23, 2021 at 18:36

Serg · Accepted Answer · 2021-11-23 18:58:17Z

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

Charlieface · Accepted Answer · 2021-11-23 23:11:26Z

1

You can OUTER APPLY a sequence containing the attributes and the inner text. Then for each of those, you can use local-name(.) to get the name of an attribute.

SELECT
  Node  = x1.step.value('local-name(.)','varchar(20)'),
  Step  = x1.step.value('@No','int'),
  [Key] = x2.vals.value('if (local-name(.) = "") then "Value" else local-name(.)','varchar(20)'),
  Value = x2.vals.value('.','nvarchar(100)')
FROM dw_mrd_vss_rundetail_stg s
CROSS APPLY s.rundata_detail.nodes('/Step') x1(step)
OUTER APPLY x1.step.nodes('(./@*[local-name(.) != "No"], ./text())') x2(vals);

db<>fiddle

If you want to include all nodes, even ones that are not Step, just change the first .nodes to .nodes('/*')

edited Nov 23, 2021 at 23:11

answered Nov 23, 2021 at 21:11

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

2 Comments

lptr Over a year ago

..step elements without text (node) and no other attribute (but No) will not be included

Charlieface Over a year ago

@lptr You're right, needs to be OUTER APPLY

Collectives™ on Stack Overflow

TSQL - Parse XML metadata & values dynamically

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related