SQL Server 2019 XQuery解析XML获取payment节点卡顿问题咨询

问题原因 #

你新增的CROSS APPLY语句重复遍历了整个XML的transaction节点，会和原有查询已经拆分出的transaction、trans-item节点产生笛卡尔积，数据量呈指数级上涨，最终导致查询长时间无响应。

解决方案 #

直接复用你第一次拆分得到的x1(trans)（已经是单个transaction节点对象）来提取payment节点，不需要再次解析整个XML的交易结构。

具体修改步骤

1. 删除你新增的两行CROSS APPLY语句：

CROSS APPLY xi.[LoadedXML].nodes('tcpos-export/transactions/transaction') x3(payments)
CROSS APPLY x3.payments.nodes('payment[code/text()]') x4(payment)

2. 新增基于已拆分transaction节点的payment提取语句，根据业务需要选择APPLY类型：
   • 仅保留有付款记录的交易用CROSS APPLY
   • 需保留无付款记录的交易用OUTER APPLY

-- 放在原有CROSS APPLY x1.trans.nodes('trans-item[...]') x2(item) 语句之后即可
CROSS APPLY x1.trans.nodes('payment[code/text()]') x4(payment)

3. 你已经写在SELECT列表中的payment取值语句可以直接保留使用：

payment.value('(code/text())[1]','varchar(100)') 支付编码,
payment.value('(description/text())[1]','varchar(100)') 支付方式

完整修改后查询示例

--procesar articulos del dia de venta
SELECT 
        trans.value('(shop/description/text())[1]','varchar(100)') tienda,
        trans.value('(till/code/text())[1]','varchar(100)') caja,
        trans.value('(cashier/code/text())[1]','varchar(100)') cajero,
        trans.value('(beginning-timestamp/text())[1]','varchar(100)') fecha,
        trans.value('(trans-num/text())[1]','varchar(100)') transaccion,
        item.value('(code/text())[1]','varchar(100)') itemcode,
        item.value('(description/text())[1]','varchar(100)') description,
        item.value('(hash-code/text())[1]','int') hashcode,
        payment.value('(code/text())[1]','varchar(100)') 支付编码,
        payment.value('(description/text())[1]','varchar(100)') 支付方式
FROM [dbo].[XmlImport] xi
CROSS APPLY xi.[LoadedXML].nodes('tcpos-export/transactions/transaction') x1(trans)
CROSS APPLY x1.trans.nodes('trans-item[
    hash-code/text() and
    not( unit-price[contains(text()[1], "-")] ) and
    not( taxable-amount[contains(text()[1], "-")] ) and
    not( delete-operator-id/text() )
]') x2(item)
-- 新增的payment提取语句
CROSS APPLY x1.trans.nodes('payment[code/text()]') x4(payment)
CROSS APPLY (VALUES (
    item.value('(quantity/text())[1]','numeric(10,3)'),
    item.value('(weight/text())[1]','numeric(10,3)'),
    item.value('(vat-code/text())[1]','varchar(100)')
) ) v(quantity, weight, vatcode)
ORDER BY hashcode;

补充优化建议 #

• 如果单个transaction下存在多个trans-item和多个payment，查询结果会出现一对多的关联冗余，建议拆分为三张表分别查询：交易基本信息、商品明细、支付明细，再通过交易唯一标识（比如trans-num）关联，避免数据重复。
• 若需要解析的XML文件普遍较大，可给LoadedXML字段建立XML索引，能大幅提升节点解析速度。

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