背景 我的 SQL 表中有一個 XML 列（使用 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 | 緩沖區 | 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)
這給出了下表：
| 步 | 宏 | 緩沖區 | 行動 |
|---|---|---|---|
| 1 | 空值 | 空值 | 第 1 步 |
| 2 | 空值 | 6000 | 第 2 步 |
| 3 | 5 | 空值 | 第 3 步 |
但是我必須顯式呼叫每個值并且以這種方式創建列是不可擴展的。任何幫助，將不勝感激。我對 SQL 中的這種資料處理比較陌生，因此對代碼的解釋會有所幫助。
uj5u.com熱心網友回復：
動態解決方案。如果“否”屬性也是可選的，并且節點名稱也在變化，
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
uj5u.com熱心網友回復：
您可以創建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('/*')
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/365261.html
標籤：sql sql-server 查询语句 查询 xquery-sql