TSQL - Динамически анализирует метаданные и значения XML

Question 1

Фон У меня есть столбец XML в моей таблице SQL (с использованием SQL Server). Каждый узел имеет разное количество метаданных. Например, в приведенном ниже примере Шаг № 1 содержит единственное "Нет" в качестве метаданных, в то время как шаг № 2 дополнительно содержит 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>

Ожидаемый Результат

Я хотел бы динамически извлекать эти метаданные, одновременно извлекая значение. В приведенном выше примере это будет выглядеть как приведенная ниже таблица. Важно отметить, что не должно иметь значения, сколько существует тегов метаданных, я хочу, чтобы он прошел через все из них. Некоторые из моих данных имеют более 10 тегов.

Узел	Шаг	Клавиша	Ценность
Шаг	1	Ценность	Шаг № 1
Шаг	2	RBuffer	6000
Шаг	2	Ценность	Шаг № 2
Шаг	3	Макрос	5
Шаг	3	Ценность	Шаг № 3

Работайте до сих пор

До сих пор мне удавалось извлекать метаданные статическим способом:

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)

Что дает следующую таблицу:

Шаг	Макрос	RBuffer	Экшен
1	нулевой	нулевой	Шаг № 1
2	нулевой	6000	Шаг № 2
3	5	нулевой	Шаг № 3

Но я должен явно вызывать каждое значение, а создание столбцов таким образом не масштабируется. Любая помощь будет признательна. Я относительно новичок в такого рода обработке данных в SQL, поэтому объяснения кода были бы полезны.

Question 2

Динамичное решение. Если атрибут "Нет" также является необязательным, а имя узла также меняется,

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

ВОЗВРАТ

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

Ты можешь OUTER APPLY последовательность, содержащая атрибуты и внутренний текст. Затем для каждого из них вы можете использовать local-name(.) чтобы получить имя атрибута.

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

бд<>скрипка<>

Если вы хотите включить все узлы, даже те, которые не являются Step, просто измените первый .nodes Для .nodes('/*')

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

Динамичное решение. Если атрибут "Нет" также является необязательным, а имя узла также меняется,

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

ВОЗВРАТ

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

Ты можешь OUTER APPLY последовательность, содержащая атрибуты и внутренний текст. Затем для каждого из них вы можете использовать local-name(.) чтобы получить имя атрибута.

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

бд<>скрипка<>

Если вы хотите включить все узлы, даже те, которые не являются Step, просто измените первый .nodes Для .nodes('/*')

..элементы шага без текста (узел) и без другого атрибута (но нет) не будут включены
@lptr Ты прав, так и должно быть OUTER APPLY

TSQL - Динамически анализирует метаданные и значения XML

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории