TSQL - Analyzovať XML metadát & hodnôt dynamicky

Question 1

Pozadie Mám kolónu XML v my SQL tabuľky (pomocou SQL Server). Každý uzol má rôzne množstvo metadát. Napríklad v príklade nižšie, Krok Číslo 1 má iba "Nie" ako metadáta, zatiaľ čo, Krok Číslo 2 okrem toho má 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>

Očakávaný Výstup

Chcel by som, aby tento výpis metadáta dynamicky a zároveň uchopením hodnotu. Pre vyššie uvedený príklad, to by vyzerať ako nižšie uvedená tabuľka. Dôležitejšie je, že by to nemalo ohľadu na to, koľko značiek metadát existuje, chcem to prejsť všetky z nich. Niektoré z mojich údajov má 10+ značiek.

Uzol	Krok	Kľúč	Hodnota
Krok	1	Hodnota	Krok Číslo 1
Krok	2	RBuffer	6000
Krok	2	Hodnota	Krok Číslo 2
Krok	3	Makro	5
Krok	3	Hodnota	Krok Číslo 3

Práce tak ďaleko

Tak ďaleko, bol som schopný extrahovať metadáta v statickej spôsobom:

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)

Ktoré poskytuje nasledujúca tabuľka:

Krok	Makro	RBuffer	Akcia
1	NULL	NULL	Krok Číslo 1
2	NULL	6000	Krok Číslo 2
3	5	NULL	Krok Číslo 3

Ale ja som explicitne volať každú hodnotu a vytváranie stĺpcov v tomto smere nie je merateľné. Akúkoľvek pomoc, chcel by som byť ocenil. Som relatívne nový druh údajov munging v SQL, takže vysvetlenia kód by byť užitočné.

Question 2

Dynamické riešenie. Ak "Žiadny" atribút je voliteľný príliš a uzol meno je rôzne, rovnako,

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

Vráti

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

Môžete OUTER APPLY sled obsahujúci atribúty a vnútorné text. Potom pre každý z nich, môžete použiť local-name(.) ak chcete získať názov atribútu.

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

Ak chcete zahrnúť všetky uzly, dokonca aj tie, ktoré nie sú Stepstačí zmeniť prvý .nodes na .nodes('/*')

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

Dynamické riešenie. Ak "Žiadny" atribút je voliteľný príliš a uzol meno je rôzne, rovnako,

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

Vráti

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

Môžete OUTER APPLY sled obsahujúci atribúty a vnútorné text. Potom pre každý z nich, môžete použiť local-name(.) ak chcete získať názov atribútu.

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

Ak chcete zahrnúť všetky uzly, dokonca aj tie, ktoré nie sú Stepstačí zmeniť prvý .nodes na .nodes('/*')

..krok prvky, bez textu (uzol) a žiadny iný atribút (ale Nie), nebudú zahrnuté

TSQL - Analyzovať XML metadát & hodnôt dynamicky

Otázka

Najlepšiu odpoveď

V iných jazykoch

Táto stránka je v iných jazykoch

Populárne v tejto kategórii

Obľúbené otázky v tejto kategórii