TSQL - Parse XML metadat a hodnoty dynamicky

Question 1

Pozadí Mám XML sloupec v mé tabulky SQL (pomocí SQL Server). Každý uzel má různé množství metadat. Například, v příkladu níže, Krok Číslo 1 má pouze "Ne" jako metadata, zatímco, Krok Číslo 2 navíc 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čekávaný Výstup

Rád bych, aby extrahovat metadata dynamicky a zároveň popadl hodnotu. Pro výše uvedený příklad by vypadat jako níže uvedená tabulka. Důležitější je, nemělo by záležet na tom, jak mnoho metadata tagy tam jsou, chci, aby to jít přes všechny z nich. Některé z mých dat má 10+ kategorie.

Uzel	Krok	Klíč	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 daleko

Zatím jsem byl schopen extrahovat metadata ve statické způsobem:

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)

Což dává následující tabulka:

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

Ale musím explicitně volat každou hodnotu a vytváření sloupců tímto způsobem není škálovatelné. Jakýkoliv pomoci chtěl bych být ocenil. Jsem poměrně nový na tento druh dat munging v SQL, takže vysvětlení kód by být užitečné.

Question 2

Dynamické řešení. Pokud "Ne" atribut je volitelný příliš a název uzlu je různý, stejně,

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átí

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 sekvence obsahující atributy a vnitřní text. Pak pro každý z těchto, můžete použít local-name(.) získat název atributu.

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

Pokud chcete zahrnout všechny uzly, dokonce i ty, které nejsou Step, stačí změnit první .nodes k .nodes('/*')

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

Dynamické řešení. Pokud "Ne" atribut je volitelný příliš a název uzlu je různý, stejně,

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átí

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 sekvence obsahující atributy a vnitřní text. Pak pro každý z těchto, můžete použít local-name(.) získat název atributu.

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

Pokud chcete zahrnout všechny uzly, dokonce i ty, které nejsou Step, stačí změnit první .nodes k .nodes('/*')

..krok prvků bez textu (uzel) a žádný jiný atribut (ale Ne) nebudou zahrnuty

TSQL - Parse XML metadat a hodnoty dynamicky

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii