从SQL Server上的XML中选择值

Question

我一直在尝试解析以下XML以提取STATNAME&定子直流在SQL Server 中

set @xml= '
<ENVELOPE>
<STATNAME>ABC</STATNAME>
<STATVALUE>
<STATDIRECT>2</STATDIRECT>
<STATCANCELLED></STATCANCELLED>
</STATVALUE>
<STATNAME>XYZ</STATNAME>
<STATVALUE>
<STATDIRECT>10</STATDIRECT>
<STATCANCELLED>2</STATCANCELLED>
</STATVALUE>
<STATNAME>LMN</STATNAME>
<STATVALUE>
<STATDIRECT>20</STATDIRECT>
<STATCANCELLED>2</STATCANCELLED>
</STATVALUE>
</ENVELOPE>

使用XML节点提取值

SELECT
t.v.value('(STATNAME/text())[1]','VARCHAR(100)') AS VOUCHERTYPE
, i.p.value('(STATDIRECT/text())[1]','VARCHAR(100)') AS VCOUNT
FROM @XML.nodes('/ENVELOPE') AS t(v) 
CROSS APPLY @XML.nodes('/ENVELOPE/STATVALUE') AS i(p)

但这只是重复XML的第一个值，而不是迭代到下一行

输出

VOUCHERTYPE   VCOUNT
ABC           2
ABC           2
ABC           2

预期输出

VOUCHERTYPE   VCOUNT
ABC           2
XYZ           10
LMN           20

Answer 1

请尝试以下解决方案。

它使用节点顺序比较运算符">gt">

点击此处查看：节点顺序比较运算符

SQL

DECLARE @xml XML = 
N'<ENVELOPE>
<STATNAME>ABC</STATNAME>
<STATVALUE>
<STATDIRECT>2</STATDIRECT>
<STATCANCELLED></STATCANCELLED>
</STATVALUE>
<STATNAME>XYZ</STATNAME>
<STATVALUE>
<STATDIRECT>10</STATDIRECT>
<STATCANCELLED>2</STATCANCELLED>
</STATVALUE>
<STATNAME>LMN</STATNAME>
<STATVALUE>
<STATDIRECT>20</STATDIRECT>
<STATCANCELLED>2</STATCANCELLED>
</STATVALUE>
</ENVELOPE>';
SELECT c.value('(./text())[1]', 'VARCHAR(20)') AS VOUCHERTYPE
, c.value('let $i := . return (/ENVELOPE/STATVALUE/STATDIRECT[. >> $i]/text())[1]', 'INT') AS VCOUNT
FROM @xml.nodes('/ENVELOPE/STATNAME') AS t(c);

输出

VOUCHERTYPE	VCOUNT
ABC	2
XYZ	10
LMN	20