TSQL - 动态解析XML元数据和值

Question

背景我的SQL表中有一个XML列(使用SQL Server(。每个节点都有不同数量的元数据。例如，在下面的例子中，步骤1具有唯一的"；否"；作为元数据，而步骤2另外具有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>

预期输出

我希望在获取值的同时动态地提取这些元数据。对于上面的例子，这看起来像下表。重要的是，不管有多少元数据标签，我都希望它能遍历所有的元数据标签。我的一些数据有10个以上的标签。

值步骤号1值5

节点		步骤	键
步骤	1	值
步骤	2	R缓冲区	6000
步骤	2	第2步
步骤	3	宏
步骤	3	值	步骤编号3

一个动态解决方案。如果"；否"；属性也是可选的，节点名称也是可变的，

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

返回

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

您可以OUTER APPLY一个包含属性和内部文本的序列。然后，对于其中的每一个，都可以使用local-name(.)来获取属性的名称。

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<gt；小提琴

如果要包括所有节点，即使不是Step的节点，也只需将第一个.nodes更改为.nodes('/*')