我一直在尝试解析以下XML以提取SQL Server中的STATNAME和STATDIRECT的值

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