TSQL-XML meta verilerini ve değerlerini dinamik olarak ayrıştırma

Question 1

Arka plân SQL tablomda bir XML sütunum var (SQL Server kullanarak). Her düğümün farklı miktarda meta verisi vardır. Örneğin, aşağıdaki örnekte, 1 numaralı Adımda meta veri olarak yalnızca "Hayır" bulunurken, 2 numaralı Adımda ayrıca RBuffer bulunur.

<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>

Beklenen Çıktı

Bu meta verileri dinamik olarak ayıklamak ve aynı zamanda değeri yakalamak istiyorum. Yukarıdaki örnekte, bu aşağıdaki tabloya benzeyecektir. Önemli olarak, kaç tane meta veri etiketi olduğu önemli olmamalı, hepsinden geçmesini istiyorum. Bazı verilerimin 10'dan fazla etiketi var.

Düğümlü	Adım	Anahtar	Değer
Adım	1	Değer	Adım Numarası 1
Adım	2	Rbuffername	6000
Adım	2	Değer	Adım Numarası 2
Adım	3	Makro	5
Adım	3	Değer	Adım Numarası 3

Şimdiye kadar çalışmak

Şimdiye kadar meta verileri statik bir şekilde çıkarabildim:

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)

Bu aşağıdaki tabloyu verir:

Adım	Makro	Rbuffername	Aksiyon
1	boş	boş	Adım Numarası 1
2	boş	6000	Adım Numarası 2
3	5	boş	Adım Numarası 3

Ancak her değeri açıkça çağırmam gerekiyor ve bu şekilde sütunlar oluşturmak ölçeklenebilir değil. Herhangi bir yardım takdir edilecektir. Sql'de bu tür veri çiğneme konusunda nispeten yeniyim, bu nedenle kod açıklamaları yararlı olacaktır.

Question 2

Dinamik bir çözüm. "Hayır" özniteliği de isteğe bağlıysa ve bir düğüm adı da değişiyorsa,

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

Dönüşler

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

Yapabilirsin OUTER APPLY öznitelikleri ve iç metni içeren bir sıra. Sonra bunların her biri için şunları kullanabilirsiniz local-name(.) bir özniteliğin adını almak için.

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 < >keman<>

Tüm düğümleri, hatta olmayan düğümleri dahil etmek istiyorsanız Step, sadece ilkini değiştir .nodes -e doğru .nodes('/*')

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

Dinamik bir çözüm. "Hayır" özniteliği de isteğe bağlıysa ve bir düğüm adı da değişiyorsa,

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

Dönüşler

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

Yapabilirsin OUTER APPLY öznitelikleri ve iç metni içeren bir sıra. Sonra bunların her biri için şunları kullanabilirsiniz local-name(.) bir özniteliğin adını almak için.

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 < >keman<>

Tüm düğümleri, hatta olmayan düğümleri dahil etmek istiyorsanız Step, sadece ilkini değiştir .nodes -e doğru .nodes('/*')

..metin (düğüm) ve başka bir öznitelik (ancak Hayır) içermeyen adım öğeleri dahil edilmeyecektir

TSQL-XML meta verilerini ve değerlerini dinamik olarak ayrıştırma

Soru

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride