Selecting values from XML on SQL Server

Question

I have been trying to parse the following XML to extract values of STATNAME & STATDIRECT in 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>

Using XML nodes to extract the values

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)

But this only repeats the first value of the XML instead of iterating to next row

Output

VOUCHERTYPE   VCOUNT
ABC           2
ABC           2
ABC           2

Expected output

VOUCHERTYPE   VCOUNT
ABC           2
XYZ           10
LMN           20

A trick you can do is use `.` to see what is in a location to make sure your xquery is acting on what you expect. So add the these two lines to your query and see what you get `t.v.value('.','VARCHAR(100)') as V_xml, i.p.value('.','VARCHAR(100)') AS i_xml` — Hogan, Oct 01 '22 at 20:14
this concats all the values together for V_xml column `ABCXYZLMN` — rajsx, Oct 01 '22 at 20:18
for a moderate number of nodes : `… from @XML.nodes('/ENVELOPE/STATNAME') AS t(v) cross apply (values(t.v.query('let $v := . return ../STATVALUE[. >> $v][1]'))) as i(p)` — lptr, Oct 01 '22 at 21:32
@lptr thanks man this works but it also gets the STATSCANCELLED tag along with the STATSVALUE tag can you share some references to understand that query part — rajsx, Oct 02 '22 at 10:09

score 1 · Accepted Answer · answered Oct 02 '22 at 00:32

Please try the following solution.

It is using Node Order Comparison Operator ">>"

Check it out here: Node Order Comparison Operators

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);

Output

VOUCHERTYPE	VCOUNT
ABC	2
XYZ	10
LMN	20

Thanks this works but incase there is any STATDIRECT value empty it pulls the next value of the same can we manage to handle that? — rajsx, Oct 02 '22 at 10:07
While asking a question you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) Input XML. (2) Desired output based on the #1 above. Please edit your original question and provide it. — Yitzhak Khabinsky, Oct 02 '22 at 12:47

Selecting values from XML on SQL Server

1 Answers1