TSQL - Dynamicznie analizuje metadane i wartości XML

Question 1

Tło Mam kolumny XML w mojej tabeli SQL (z użyciem SQL Server). Każdy węzeł ma inną ilość metadanych. Na przykład w poniższym przykładzie Krok nr 1 zawiera jedyne "Nie" jako metadane, podczas gdy krok nr 2 dodatkowo zawiera 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>

Oczekiwany Wynik

Chciałbym dynamicznie wyodrębnić te metadane, jednocześnie wydobywając wartość. W powyższym przykładzie będzie to wyglądać jak w poniższej tabeli. Ważne jest, aby pamiętać, że nie powinno mieć znaczenia, ile jest znaczników metadanych, chcę, aby przeszedł przez wszystkie z nich. Niektóre z moich danych mają ponad 10 znaczników.

Węzeł	Krok	Klawisz	Wartość
Krok	1	Wartość	Krok nr 1
Krok	2	RBuffer	6000
Krok	2	Wartość	Krok nr 2
Krok	3	Makro	5
Krok	3	Wartość	Krok nr 3

Pracuje do tej pory

Do tej pory udało mi się pobierać metadane statycznym sposób:

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)

Co daje następującą tabelę:

Krok	Makro	RBuffer	Działania
1	zero	zero	Krok nr 1
2	zero	6000	Krok nr 2
3	5	zero	Krok nr 3

Ale muszę jawnie wywoływać każda wartość, a tworzenie kolumn w ten sposób nie jest skalowany. Każda pomoc będzie wdzięczna. Jestem stosunkowo nowy w tego rodzaju przetwarzania danych w SQL, więc podania kodu byłyby pomocne.

Question 2

Dynamiczny rozwiązanie. Jeśli atrybut "Nie" również jest opcjonalne, a nazwa hosta również się zmienia,

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

ZWROT

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

Możesz OUTER APPLY sekwencja zawierająca atrybuty i wewnętrzny tekst. Następnie dla każdego z nich można użyć local-name(.) aby uzyskać nazwę atrybutu.

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);

obd<>skrzypce<>

Jeśli chcesz zawierać wszystkie elementy, nawet te, które nie są Steppo prostu zmień pierwszy .nodes Dla .nodes('/*')

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

Dynamiczny rozwiązanie. Jeśli atrybut "Nie" również jest opcjonalne, a nazwa hosta również się zmienia,

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

ZWROT

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

Możesz OUTER APPLY sekwencja zawierająca atrybuty i wewnętrzny tekst. Następnie dla każdego z nich można użyć local-name(.) aby uzyskać nazwę atrybutu.

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);

obd<>skrzypce<>

Jeśli chcesz zawierać wszystkie elementy, nawet te, które nie są Steppo prostu zmień pierwszy .nodes Dla .nodes('/*')

..elementy kroku bez tekstu (węzeł) i bez innego atrybutu (ale nie ma) nie będą uwzględniane

TSQL - Dynamicznie analizuje metadane i wartości XML

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii