TSQL - Parse XML de métadonnées et les valeurs de l'dynamiquement

Question 1

Arrière-plan J'ai une colonne XML dans ma table SQL (à l'aide de SQL Server). Chaque nœud a une quantité différente de métadonnées. Par exemple, dans l'exemple ci-dessous, Étape Numéro 1 est le seul "Non" comme les métadonnées lors, l'Étape Numéro 2 de 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>

Sortie Attendue

J'aimerais extraire les métadonnées de manière dynamique tout en saisissant la valeur. Pour l'exemple ci-dessus, ce serait semblable à la table ci-dessous. Surtout, il ne devrait pas d'importance combien d'étiquettes de métadonnées, il y a, je veux aller à travers eux tous. Certaines de mes données a+ de 10 balises.

Nœud	Étape	Clé	Valeur
Étape	1	Valeur	Numéro De L'Étape 1
Étape	2	RBuffer	6000
Étape	2	Valeur	L'Étape Numéro 2
Étape	3	Macro	5
Étape	3	Valeur	Numéro De L'Étape 3

À ce jour

Jusqu'à présent, j'ai été en mesure d'extraire les métadonnées de manière statique:

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)

Ce qui donne le tableau suivant:

Étape	Macro	RBuffer	Action
1	NULL	NULL	Numéro De L'Étape 1
2	NULL	6000	L'Étape Numéro 2
3	5	NULL	Numéro De L'Étape 3

Mais j'ai appeler explicitement chaque valeur et la création de colonnes dans cette voie n'est pas évolutive. Toute aide serait appréciée. Je suis relativement nouveau à ce type de données munging en SQL, donc les explications de code serait utile.

Question 2

Une solution dynamique. Si le "Non" l'attribut est facultatif trop et un nom de nœud est variable ainsi,

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

Retourne

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

Vous pouvez OUTER APPLY une séquence contenant les attributs et l'intérieur du texte. Puis, pour chacun de ceux-ci, vous pouvez utiliser local-name(.) pour obtenir le nom d'un attribut.

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

Si vous souhaitez inclure tous les nœuds, même ceux qui ne sont pas Step, il suffit de modifier le premier .nodes pour .nodes('/*')

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

Une solution dynamique. Si le "Non" l'attribut est facultatif trop et un nom de nœud est variable ainsi,

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

Retourne

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

Vous pouvez OUTER APPLY une séquence contenant les attributs et l'intérieur du texte. Puis, pour chacun de ceux-ci, vous pouvez utiliser local-name(.) pour obtenir le nom d'un attribut.

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

Si vous souhaitez inclure tous les nœuds, même ceux qui ne sont pas Step, il suffit de modifier le premier .nodes pour .nodes('/*')

..l'étape des éléments sans texte (nœud) et aucun autre attribut (mais Non) ne sera pas inclus

TSQL - Parse XML de métadonnées et les valeurs de l'dynamiquement

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie