TSQL-分析XML元和价值动态

Question 1

背景我有一个XML列在我SQL table(使用SQL服务器)。每个节点上有不同数量的元数据。例如，在下面的例子，步骤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	值	步骤1号
步骤	2	RBuffer	6000
步骤	2	值	步骤2号
步骤	3	宏	5
步骤	3	值	步骤3号

迄今为止的工作

迄今为止，我已经能够提取的元数据，在一个静态的方式：

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)

这给下表：

步骤	宏	RBuffer	动作
1	空	空	步骤1号
2	空	6000	步骤2号
3	5	空	步骤3号

但我必须明确地呼吁各个值和创造列的以这种方式并不是扩展。任何帮助，将不胜感激。我是相对较新，这种数据处理在SQL，这样的解释码将是有益的。

Question 2

一个动态的解决方案。如果"没有"的属性是可选择的太和一个节点名称的变化，以及，

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

Question 3

你可以 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<>的小提琴

如果你想要包括所有节点，即使是那些不是 Step，只是改变第一个 .nodes 要 .nodes('/*')

Serg · Answer 1 · 2021-11-23T18:58:17

一个动态的解决方案。如果"没有"的属性是可选择的太和一个节点名称的变化，以及，

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

Charlieface · Answer 2 · 2021-11-23T23:11:26

你可以 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<>的小提琴

如果你想要包括所有节点，即使是那些不是 Step，只是改变第一个 .nodes 要 .nodes('/*')

..步骤的要素没有文字(节点)并没有任何其他属性(但没有)将不被包括在内，

TSQL-分析XML元和价值动态

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别