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      |

原查询问题分析 #

原查询中，最后一步CROSS APPLY @Request.nodes('/Customer/Order/orderItem')直接从XML根路径获取所有orderItem节点，导致每个orderItem与所有orderData节点产生笛卡尔积关联，错误地将仅属于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/Order/orderData') od(od_col)
OUTER APPLY 
    od.od_col.nodes('orderItem') oi(oi_col)

修正说明 #

1. 移除不必要的根节点遍历，直接定位到/Customer/Order/orderData节点
2. 使用OUTER APPLY替代CROSS APPLY，确保无orderItem的orderData行仍能保留，对应字段返回NULL
3. 核心修正：通过od.od_col.nodes('orderItem')从当前orderData节点下获取其所属的orderItem，保证orderItem与对应的id正确关联

内容的提问来源于stack exchange，提问作者kumar kandula