TSQL - Parse XML de metadatos y valores de forma dinámica

Question 1

De fondo Tengo una columna XML en mi tabla de SQL (utilizando SQL Server). Cada nodo tiene una cantidad de metadatos. Por ejemplo, en el ejemplo de abajo, el Paso Número 1 es el único "No" como metadatos, mientras que, el Paso Número 2, además, tiene 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>

Resultado Esperado

Me gustaría extraer metadatos de forma dinámica mientras que también agarrar el valor. Para el ejemplo anterior, esto se vería de la siguiente tabla. Es importante destacar que, no importa cómo muchas etiquetas de metadatos hay, yo quiero ir a través de todos ellos. Algunos de mis datos tiene 10+ etiquetas.

Nodo	Paso	Clave	Valor
Paso	1	Valor	Paso Número 1
Paso	2	RBuffer	6000
Paso	2	Valor	El Paso Número 2
Paso	3	Macro	5
Paso	3	Valor	Paso Número 3

El trabajo hasta ahora

Hasta ahora, he sido capaz de extraer los metadatos de una manera estática:

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)

Que da la siguiente tabla:

Paso	Macro	RBuffer	Acción
1	NULL	NULL	Paso Número 1
2	NULL	6000	El Paso Número 2
3	5	NULL	Paso Número 3

Pero tengo que llamar explícitamente a cada valor y la creación de las columnas de esta manera no es escalable. Cualquier ayuda se agradece. Yo soy relativamente nuevo en este tipo de datos fotográficos en SQL, por lo que las explicaciones de código sería útil.

Question 2

Una solución dinámica. Si el "No" atributo es opcional y un nombre de nodo está variando así,

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

Devuelve

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

Usted puede OUTER APPLY una secuencia que contiene los atributos y el interior del texto. A continuación, para cada uno de estos, puede utilizar local-name(.) para obtener el nombre de un atributo.

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<>violín

Si desea incluir todos los nodos, incluso los que no son Stepcambie la primera .nodes a .nodes('/*')

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

Una solución dinámica. Si el "No" atributo es opcional y un nombre de nodo está variando así,

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

Devuelve

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

Usted puede OUTER APPLY una secuencia que contiene los atributos y el interior del texto. A continuación, para cada uno de estos, puede utilizar local-name(.) para obtener el nombre de un atributo.

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<>violín

Si desea incluir todos los nodos, incluso los que no son Stepcambie la primera .nodes a .nodes('/*')

..paso sin elementos de texto (nodo) y ningún otro atributo (pero No) no serán incluidos

TSQL - Parse XML de metadatos y valores de forma dinámica

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría