在SQL Server中循环和分析XML

Question

我需要帮助以我在SQL Server中想要的方式循环和解析XML，下面是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>'

以下是我想要检索数据的方式

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

只有ItemId 3有描述和价格，但在我下面的查询中，它同时适用于ItemId 1和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)

Answer 1

PL请尝试以下解决方案。

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

输出

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