SQL Server：APPLY内能否用WITH？如何用OUTER APPLY遍历XML节点

1. SQL Server中APPLY语句内部能否使用WITH子句？ #

可以。无论是OUTER APPLY还是CROSS APPLY，都允许在其内部的子查询中使用WITH子句（公共表表达式CTE），只要子查询语法符合SQL Server规范即可。示例写法如下：

SELECT *
FROM YourTable t
OUTER APPLY (
    WITH CTE AS (
        SELECT Column1, Column2 FROM RelatedTable WHERE ID = t.ID
    )
    SELECT Column1 FROM CTE
) AS SubQueryAlias

2. 使用OUTER APPLY遍历XML并生成关联结果 #

你当前的尝试代码存在路径关联错误、语法缺失等问题，且OPENXML属于较旧的XML处理方式，推荐使用SQL Server原生XML节点方法实现层级关联，以下是正确方案：

实现代码 #

DECLARE @XML AS XML
SELECT @XML = XMLData FROM XMLwithOpenXML

SELECT
    Customer.value('@CustomerID', 'VARCHAR(50)') AS CustomerID,
    Customer.value('@CustomerName', 'VARCHAR(100)') AS CustomerName,
    Customer.value('(Address/text())[1]', 'VARCHAR(100)') AS Address,
    [Order].value('@OrderID', 'VARCHAR(100)') AS OrderID,
    [Order].value('@OrderDate', 'DATETIME') AS OrderDate,
    OrderDetail.value('@ProductID', 'VARCHAR(100)') AS ProductID,
    OrderDetail.value('@Quantity', 'INT') AS Quantity
FROM @XML.nodes('/ROOT/Customers/Customer') AS T(Customer)
OUTER APPLY Customer.nodes('Orders/Order') AS O([Order])
OUTER APPLY [Order].nodes('OrderDetail') AS OD(OrderDetail)

代码说明 #

1. 第一层通过.nodes('/ROOT/Customers/Customer')提取所有Customer节点
2. 第二层通过OUTER APPLY结合相对路径Orders/Order，关联当前Customer下的所有Order节点，确保层级绑定
3. 第三层通过OUTER APPLY结合相对路径OrderDetail，关联当前Order下的所有明细节点
4. 使用.value()方法提取节点属性或元素值，并指定对应数据类型

内容的提问来源于stack exchange，提问作者Sunil Jadhav