Loop and Parse XML in SQL Server-CodePudding

I need help to loop in and parse XML the way I wanted in SQL Server, Below is the XML.

DECLARE @Request XML = 
'<Customer>
<Order>
    <orderData>
        <id>1</id>
    </orderData>
    <orderData>
        <id>2</id>
    </orderData>
    <orderData>
        <id>3</id>
        <orderItem>
            <description>Phone</description>
            <price>299</price>
        </orderItem>
        <orderItem>
            <description>Tablet</description>
            <price>599</price>
        </orderItem>
    </orderData>
</Order>
</Customer>'

Below is how I wanted to retrieve data

|ItemId|ItemDesc |ItemPrice|
|1     |NULL     |NULL     |
|2     |NULL     |NULL     |
|3     |Phone    |299      |
|3     |Tablet   |599      |

Only ItemId 3 has description and price but in my query below it's applied to both itemid 1 and 2

SELECT 
        od.od_col.value('id[1]','int')  AS ItemId
    ,   oi.oi_col.value('description[1]','varchar(250)')AS ItemDesc 
    ,   oi.oi_col.value('price[1]','varchar(250)')  AS ItemPrice            
FROM
@Request.nodes('/Customer')                      cus(cus_col)
CROSS APPLY @Request.nodes('/Customer/Order/orderData') od(od_col)
CROSS APPLY @Request.nodes('/Customer/Order/orderData/orderItem') oi(oi_col)

CodePudding user response：

PLease try the following solution.

SQL

DECLARE @Request XML = 
'<Customer>
    <Order>
        <orderData>
            <id>1</id>
        </orderData>
        <orderData>
            <id>2</id>
        </orderData>
        <orderData>
            <id>3</id>
            <orderItem>
                <description>Phone</description>
                <price>299</price>
            </orderItem>
            <orderItem>
                <description>Tablet</description>
                <price>599</price>
            </orderItem>
        </orderData>
    </Order>
</Customer>';

SELECT p.value('(id/text())[1]','INT')  AS ItemId
    , c.value('(description/text())[1]','VARCHAR(250)')AS ItemDesc 
    , c.value('(price/text())[1]','DECIMAL(10,2)')  AS ItemPrice            
FROM @Request.nodes('/Customer/Order/orderData') AS t1(p)
    OUTER APPLY p.nodes('orderItem') AS t2(c);

Output

 -------- ---------- ----------- 
| ItemId | ItemDesc | ItemPrice |
 -------- ---------- ----------- 
|      1 | NULL     | NULL      |
|      2 | NULL     | NULL      |
|      3 | Phone    | 299.00    |
|      3 | Tablet   | 599.00    |
 -------- ---------- -----------