SQL Server查询中如何通过CROSS APPLY提取XML节点指定值

你已经通过CROSS APPLY结合nodes()完成节点逐行拆分，提取节点属性、子节点值只需要对nodes()返回的节点引用调用XML数据类型的value()方法即可：第一个参数传入目标值的XPath路径，第二个参数指定要转换的SQL数据类型。

参考样例XML结构（匹配多账户存储场景）：

<Response>
  <CustomerInfo CustomerId="C001" CustomerName="张三">
    <Account ReportedDate="2024-05-20" AccountType="信用卡" AccountNo="6225****1234" Balance="12580.66" OverdueStatus="N">
      <Transaction TransDate="2024-05-18" TransAmt="299.00" TransDesc="超市消费"/>
      <Transaction TransDate="2024-05-19" TransAmt="1500.00" TransDesc="线上购物"/>
    </Account>
    <Account ReportedDate="2024-05-20" AccountType="储蓄卡" AccountNo="6217****5678" Balance="52690.12" OverdueStatus="N">
      <Transaction TransDate="2024-05-15" TransAmt="8000.00" TransDesc="工资入账"/>
    </Account>
  </CustomerInfo>
</Response>

已实现账户拆行的参考基础写法：

SELECT 
  b.BatchId,
  AccountNode.query('.') AS AccountXml
FROM t_CBResponseBatchDetail b
CROSS APPLY b.ResponseReceivedData.nodes('//Account') AS T(AccountNode)

基础写法：提取Account层级结构化字段 #

提取节点属性时XPath用@属性名格式即可，示例代码如下：

SELECT 
  b.BatchId,
  b.ResponseId, -- 可自行替换为t_CBResponseBatchDetail表需要返回的其他原生业务字段
  -- 提取<Account>节点属性
  AccountNode.value('@ReportedDate', 'date') AS ReportedDate,
  AccountNode.value('@AccountType', 'varchar(50)') AS AccountType,
  AccountNode.value('@AccountNo', 'varchar(30)') AS AccountNo,
  AccountNode.value('@Balance', 'decimal(18,2)') AS AccountBalance,
  AccountNode.value('@OverdueStatus', 'char(1)') AS OverdueStatus
  -- 提取<Account>下的直接子节点值示例（如果有<AccountStatus>正常</AccountStatus>这类子节点）：
  -- AccountNode.value('AccountStatus[1]', 'varchar(20)') AS AccountStatus
FROM t_CBResponseBatchDetail b
CROSS APPLY b.ResponseReceivedData.nodes('//Account') AS T(AccountNode)
-- 过滤空XML避免执行报错
WHERE b.ResponseReceivedData IS NOT NULL

扩展写法：多层级拆分明细 #

如果需要同时提取每个下嵌套的子节点（比如交易明细），可以继续追加CROSS APPLY关联子节点的nodes()结果，实现多层级拆解：

SELECT 
  b.BatchId,
  AccountNode.value('@ReportedDate', 'date') AS ReportedDate,
  AccountNode.value('@AccountNo', 'varchar(30)') AS AccountNo,
  TransNode.value('@TransDate', 'date') AS TransDate,
  TransNode.value('@TransAmt', 'decimal(18,2)') AS TransAmt,
  TransNode.value('@TransDesc', 'varchar(100)') AS TransDesc
FROM t_CBResponseBatchDetail b
CROSS APPLY b.ResponseReceivedData.nodes('//Account') AS T(AccountNode)
CROSS APPLY AccountNode.nodes('Transaction') AS T2(TransNode)
WHERE b.ResponseReceivedData IS NOT NULL

注意事项 #

• value()方法的第二个参数必须是SQL Server内置基础数据类型，不支持自定义数据类型
• XPath建议写相对于当前节点的相对路径，不需要从XML根节点写全路径，查询性能更优
• 如果XML根节点定义了xmlns命名空间，需要先通过WITH XMLNAMESPACES声明对应命名空间前缀再编写XPath，否则会出现提取值全为NULL的情况
• 提取子节点值时XPath末尾必须加[1]显式指定取第一个匹配节点，保证返回单值，否则语法报错；属性提取因为天然单值可省略该标记，加上也不会影响兼容性

内容的提问来源于stack exchange，提问作者Gopi S