TSQL - Analizzare i metadati XML & valori in modo dinamico

Question 1

Sfondo Ho una colonna XML nella mia tabella di SQL (utilizzando SQL Server). Ogni nodo ha una diversa quantità di metadati. Per esempio, nell'esempio di seguito, Passo Numero 1 è l'unico "No" come metadati, mentre, Passo Numero 2, inoltre, 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>

Uscita Prevista

Vorrei estrarre i metadati in modo dinamico mentre anche afferrando il valore. Per l'esempio precedente, questo sarebbe simile alla tabella che segue. Soprattutto, non dovrebbe importa quante tag di metadati ci sono, voglio andare attraverso tutti loro. Alcuni dei miei dati 10+ tag.

Nodo	Passo	Chiave	Valore
Passo	1	Valore	Passo Numero 1
Passo	2	RBuffer	6000
Passo	2	Valore	Passo Numero 2
Passo	3	Macro	5
Passo	3	Valore	Passo Numero 3

Il lavoro finora

Finora, sono stato in grado di estrarre i metadati in modo statico:

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)

Che ha pronunciato la seguente tabella:

Passo	Macro	RBuffer	Azione
1	NULL	NULL	Passo Numero 1
2	NULL	6000	Passo Numero 2
3	5	NULL	Passo Numero 3

Ma devo chiamare in modo esplicito ogni valore, e la creazione di colonne in questo modo non è scalabile. Qualsiasi aiuto sarebbe apprezzato. Io sono relativamente nuovo a questo tipo di dati munging in SQL, in modo da spiegazioni di codice sarebbe utile.

Question 2

Una soluzione dinamica. Se il "No" attributo è opzionale troppo e il nome di un nodo è variabile, così,

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

Restituisce

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

È possibile OUTER APPLY una sequenza che contiene gli attributi e il testo interno. Poi per ognuno di questi, è possibile utilizzare local-name(.) per ottenere il nome di un attributo.

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

Se si desidera includere tutti i nodi, anche quelli che non sono Stepbasta cambiare la prima .nodes per .nodes('/*')

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

Una soluzione dinamica. Se il "No" attributo è opzionale troppo e il nome di un nodo è variabile, così,

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

Restituisce

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

È possibile OUTER APPLY una sequenza che contiene gli attributi e il testo interno. Poi per ognuno di questi, è possibile utilizzare local-name(.) per ottenere il nome di un attributo.

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

Se si desidera includere tutti i nodi, anche quelli che non sono Stepbasta cambiare la prima .nodes per .nodes('/*')

..passo elementi senza testo (nodo) e nessun altro attributo (ma anche No) non saranno inclusi

TSQL - Analizzare i metadati XML & valori in modo dinamico

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria