假設，我們有以下 xml
declare @x xml ='<auditElement>
<field id="12" type="5" name="test" formatstring="">
<choice>45</choice>
</field>
<field id="13" type="5" name="test2" formatstring="">
<choice>100</choice>
</field>
<field id="74" type="8" name="somestring" formatstring="">
<choice>14</choice>
<choice>16</choice>
</field>
</auditElement>
要從選擇中獲取資料，我可以像下面這樣寫
select
field.value('@id','nvarchar(50)') as id,
field.value('@type','nvarchar(50)') as type,
field.value('@name','nvarchar(100)') as name,
field.value('@formatstring','nvarchar(50)') as formatstring,
field.value('choice[1]','nvarchar(50)') as setChoice,
field.value('choice[2]','nvarchar(50)') as setChoice2
from @x.nodes('/auditElement/field') as XMLtable1(field)
但是如果有更多的選擇..就像下面
declare @x xml ='<auditElement>
<field id="12" type="5" name="test" formatstring="">
<choice>45</choice>
</field>
<field id="13" type="5" name="test2" formatstring="">
<choice>100</choice>
</field>
<field id="74" type="8" name="somestring" formatstring="">
<choice>14</choice>
<choice>16</choice>
<choice>1656</choice>
</field>
</auditElement>
我可以通過在我的選擇中再添加一個欄位來獲取資料..
select
field.value('@id','nvarchar(50)') as id,
field.value('@type','nvarchar(50)') as type,
field.value('@name','nvarchar(100)') as name,
field.value('@formatstring','nvarchar(50)') as formatstring,
field.value('choice[1]','nvarchar(50)') as setChoice,
field.value('choice[2]','nvarchar(50)') as setChoice2
field.value('choice[3]','nvarchar(50)') as setChoice3
from @x.nodes('/auditElement/field') as XMLtable1(field)
field.value('choice[3]','nvarchar(50)') as setChoice3除了每當我們有新的選擇時添加之外，還有其他方法嗎？
uj5u.com熱心網友回復：
一種選擇是為每個選擇回傳額外的行而不是額外的列：
SELECT id = XMLtable1.field.value('@id', 'nvarchar(50)'),
type = XMLtable1.field.value('@type', 'nvarchar(50)'),
name = XMLtable1.field.value('@name', 'nvarchar(100)'),
formatstring = XMLtable1.field.value('@formatstring', 'nvarchar(50)'),
Choice = c.choice.value('(text())[1]', 'int'),
ChoiceNo = ROW_NUMBER() OVER(PARTITION BY XMLtable1.field.value('@id', 'nvarchar(50)') ORDER BY c.choice.value('(text())[1]', 'int'))
FROM @x.nodes('/auditElement/field') AS XMLtable1(field)
OUTER APPLY XMLtable1.field.nodes('choice') AS c(Choice);
db<>fiddle 上的示例
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/517703.html
標籤：xmltsql