如何在SQL中从XML值列中提取多个值

Question

我有一列包含XML数据，如下所示

<SAP status="1" joint="False"><Accs><Acc num="1" suffix="14"/><Acc num="2" suffix="15" /></Accs><Post>04/27/2022</Post><R>0</R></SAP>

如何在SQL中提取Acc num和Suffix？我想要的结果

Acc num , Suffix
1      14
2      15

感谢

Answer 1

此处需要XQuery。您可以使用.nodes将XML分解成单独的行，然后使用.value提取属性值

SELECT
AccNum  = x1.acc.value('@num'   , 'int'),
Suffix  = x1.acc.value('@suffix', 'int')
FROM YourTable t
CROSS APPLY t.xmlColumn.nodes('SAP/Accs/Acc') x1(acc);

如果您还想要来自SAP根节点的数据，那么您可以将一个.nodes馈送到另一个：

SELECT
AccNum  = x2.acc.value('@num'   , 'int'),
Suffix  = x2.acc.value('@suffix', 'int'),
Post    = x1.SAP.value('(Post/text())[1]', 'date'),
R       = x1.SAP.value('(R/text())[1]', 'int')
FROM YourTable t
CROSS APPLY t.xmlColumn.nodes('SAP') x1(SAP)
CROSS APPLY x1.SAP.nodes('Accs/Acc') x2(acc);

db<gt；小提琴