TSQL-การวิเคราะห์ค่า XML ข้อมูลกำกับภาพ&ค่าของ dynamically

Question 1

พื้นหลัง ฉันต้องการ XML คอลัมน์ในภาษา sql ของฉันโต๊ะ(ใช้ภาษา 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>

คาดหวังผลส่งออก

ฉันอยากจะดึงข้อมูลกำกับภาพนี้ dynamically ในขณะที่ยังพอจะเอาติดกลับไปค่า. สำหรับตัวอย่างอยู่เหนือมันคงดูเหมือนใต้โต๊ะ อย่างที่สำคัญมันไม่สำคัญหรอกกี่ข้อมูลกำกับภาพป้ายกำกับมัน,ฉันต้องการให้มันผ่านไปพวกเขาทั้งหมด บางอย่างของข้อมูลของ 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	โพรโทคอล aimcomment	โพรโทคอล aimcomment	ขั้นเบอร์ 1
2	โพรโทคอล aimcomment	6000	ขั้นที่หมายเลข 2
3	5	โพรโทคอล aimcomment	ขั้นเบอร์ 3

แต่ฉันต้องในข้อบังคับของโทรหากันและค่าและสร้างคอลัมน์ในทางที่ไม่ใช่ scalable. ที่เขาแนะนำให้ผูกผ้ากันเปื้อนจะเป็นเกียรติอย่างยิ่ง ฉันค่อนข้างใหม่ต้องการข้อมูล munging ในภาษา sql ดังนั้นคำอธิบายได้หลายทางของรหัสคงต้องการความช่วยเหลือเห.

Question 2

เป็นปรับอัตโนมัติทางออกหรอกนะ ถ้า"ไม่"แอททริบิวต์เป็นตัวเลือกเหมือนกันและโหนดชื่อ varying เช่นกัน

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

เป็นปรับอัตโนมัติทางออกหรอกนะ ถ้า"ไม่"แอททริบิวต์เป็นตัวเลือกเหมือนกันและโหนดชื่อ varying เช่นกัน

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 ข้อมูลกำกับภาพ&ค่าของ dynamically

คำถาม

คำตอบที่ดีที่สุด

ในภาษาอื่นๆ

หน้านี้อยู่ในภาษาอื่นๆ

ดังอยู่ในนี้หมวดหมู่

ดังคำถามอยู่ในนี้หมวดหมู่