TSQL - Analisar o XML de metadados e valores dinamicamente

Question 1

Fundo Eu tenho uma coluna de XML no SQL tabela (usando o SQL Server). Cada nó tem uma quantidade diferente de metadados. Por exemplo, no exemplo abaixo, o Passo Número 1 tem o único "Não" como metadados enquanto, o Passo Número 2, além disso tem 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>

Saída Esperada

Eu gostaria de extrair metadados de forma dinâmica enquanto também agarrando o valor. Para o exemplo acima, isto seria como mostra a tabela abaixo. Importante, não importa quantas marcas de metadados existem, eu quero que ele vá através de todos eles. Alguns dos meus dados tem 10+ tags.

Nó	Passo	Chave	Valor
Passo	1	Valor	Passo Número 1
Passo	2	RBuffer	6000
Passo	2	Valor	Passo Número 2
Passo	3	Macro	5
Passo	3	Valor	Passo Número 3

O trabalho até agora

Até agora, eu fui capaz de extrair os metadados de forma 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)

O que dá a tabela a seguir:

Passo	Macro	RBuffer	Ação
1	NULL	NULL	Passo Número 1
2	NULL	6000	Passo Número 2
3	5	NULL	Passo Número 3

Mas eu tenho que chamar explicitamente cada valor e a criação de colunas desta forma, não é escalável. Qualquer ajuda seria apreciada. Eu sou relativamente novo para este tipo de adulterar dados em SQL, portanto, explicações de código seria útil.

Question 2

Uma solução dinâmica. Se o "Não" atributo é opcional, e um nome de nó é variável,bem como,

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

Retorna

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

Você pode OUTER APPLY uma seqüência contendo os atributos e a interna do texto. Em seguida, para cada um desses, você pode usar local-name(.) para obter o nome de um 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<>violino

Se você quiser incluir todos nós, mesmo aqueles que não são Step, basta alterar o primeiro .nodes para .nodes('/*')

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

Uma solução dinâmica. Se o "Não" atributo é opcional, e um nome de nó é variável,bem como,

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

Retorna

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

Você pode OUTER APPLY uma seqüência contendo os atributos e a interna do texto. Em seguida, para cada um desses, você pode usar local-name(.) para obter o nome de um 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<>violino

Se você quiser incluir todos nós, mesmo aqueles que não são Step, basta alterar o primeiro .nodes para .nodes('/*')

..a etapa elementos sem texto (nó) e nenhum outro atributo (mas Não) não serão incluídos

TSQL - Analisar o XML de metadados e valores dinamicamente

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria