TSQL - Parse XML metadata & valori dinamic

Question 1

Fundal Am un XML coloană în tabel SQL (folosind SQL Server). Fiecare nod are o valoare diferită de metadate. De exemplu, în exemplul de mai jos, Pasul Numărul 1 are numai "Nu" ca metadate în timp ce, Pasul Numărul 2 în plus a 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>

Temperatura De Ieșire

Aș vrea să extragă aceste metadate dinamic în timp ce, de asemenea, hapsân valoare. Pentru exemplul de mai sus, acest lucru ar arăta ca în tabelul de mai jos. Important, nu contează cât de multe tag-uri de metadate sunt, vreau să trec prin toate acestea. Unele dintre datele mele are 10+ tag-uri.

Nod	Pas	Cheie	Valoarea
Pas	1	Valoarea	Pasul Numărul 1
Pas	2	RBuffer	6000
Pas	2	Valoarea	Pasul Numărul 2
Pas	3	Macro	5
Pas	3	Valoarea	Etapa Cu Numarul 3

Munca de până acum

Până acum, am fost capabil de a extrage metadate în mod static:

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)

Care dă următorul tabel:

Pas	Macro	RBuffer	Acțiune
1	NULL	NULL	Pasul Numărul 1
2	NULL	6000	Pasul Numărul 2
3	5	NULL	Etapa Cu Numarul 3

Dar trebuie să spunem în mod explicit fiecare valoare și crearea de coloane în acest fel nu este scalabil. Orice ajutor ar fi apreciat. Sunt relativ nou la acest tip de prelucrarea datelor în SQL, astfel încât explicațiile de cod ar fi de ajutor.

Question 2

O soluție dinamică. Dacă "Nu" atribut este opțional și un nume de nod este variabilă,precum și,

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

Se întoarce

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

Puteți OUTER APPLY o secvență care conține atributele și interioară a textului. Apoi pentru fiecare dintre acestea, puteți utiliza local-name(.) pentru a obține numele unui atribut.

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

Dacă doriți să includeți toate nodurile, chiar și cele care nu sunt Step, schimba doar prima .nodes pentru a .nodes('/*')

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

O soluție dinamică. Dacă "Nu" atribut este opțional și un nume de nod este variabilă,precum și,

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

Se întoarce

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

Puteți OUTER APPLY o secvență care conține atributele și interioară a textului. Apoi pentru fiecare dintre acestea, puteți utiliza local-name(.) pentru a obține numele unui atribut.

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

Dacă doriți să includeți toate nodurile, chiar și cele care nu sunt Step, schimba doar prima .nodes pentru a .nodes('/*')

..pas elemente fără text (nod) și nici un alt atribut (dar Nu) nu vor fi incluse

TSQL - Parse XML metadata & valori dinamic

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie