我需要幫助以在 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)
uj5u.com熱心網友回復：
請嘗試以下解決方案。
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 |
-------- ---------- -----------
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514588.html
上一篇：T-SQLSUBSTRINGwithCHARINDEXASLENGTH部分回傳太多文本
下一篇：AzureSQL資料庫sys.resource_stats::LAST_VALUE(storage_in_megabytes)