T-SQL解析XML写入临时表时如何按序匹配货物与对应条码
问题核心原因
- 原查询的条码展开路径仅定位到
<barcodes>父节点,未拆出单个<barcode>子节点,后续取值时固定取该节点下第一个条码元素,导致同一货件下所有货物条目都绑定到第一个条码 - 缺少单货件维度的顺序编号机制,无法实现「同货件内第N个货物对应第N个条码」的匹配规则
修正实现
核心调整点:
- 将条码节点展开路径从
barcodes调整为barcodes/barcode,把每个条码拆成独立的行 - 给同一货件下的货物、条码分别生成从1开始的连续顺序号,通过「货件维度+相同顺序号」做关联,保证顺序一一匹配
- 完全保留原有临时表结构、参考号生成逻辑,不做额外改动
DECLARE @xml XML SET @xml = '<beginmsg> <refmsg>A1234567</refmsg> <shipments> <shipment> <load> <address> <name>Loadname</name> <street>Street</street> <zipcode>ABCDE</zipcode> <city>Munchen</city> <countrycode>DE</countrycode> </address> <dateTime> <date>2021-02-02</date> <timeFrom>08:00:00</timeFrom> </dateTime> </load> <unload> <address> <name>unloadname</name> <street>Street unload</street> <zipcode>1111</zipcode> <city>Dresden</city> <countrycode>DE</countrycode> </address> </unload> <goods> <good> <description>Cookies</description> <quantity>1</quantity> </good> <good> <description>Cookies</description> <quantity>3</quantity> </good> <good> <description>Some food</description> <quantity>2</quantity> </good> </goods> <barcodes> <barcode>7829348742910092309325</barcode> <barcode>7829348742112344114414</barcode> <barcode>0984746166149566188446</barcode> </barcodes> <references> <reference>GBP-4362</reference> </references> </shipment> <shipment> <load> <address> <name>shipment 2 load</name> <street>Street load2</street> <zipcode>1234 RR</zipcode> <city>Koln</city> <countrycode>DE</countrycode> </address> <dateTime> <date>2021-03-03</date> <timeFrom>10:00:00</timeFrom> </dateTime> </load> <unload> <address> <name>Shipment 2 unload</name> <street>Street 2 unl</street> <zipcode>1000 AA</zipcode> <city>Amsterdam</city> <countrycode>NL</countrycode> </address> </unload> <goods> <good> <description>Televisions</description> <quantity>2</quantity> </good> </goods> <barcodes> <barcode>0984746166149566188446</barcode> </barcodes> <references> <reference>HBR-7211CX</reference> </references> </shipment> </shipments> </beginmsg>' IF OBJECT_ID('tempdb..#Goods') IS NOT NULL DROP TABLE #Goods; CREATE TABLE #Goods ( row INT, shipmentreference VARCHAR(100), description VARCHAR(MAX), quantity INT, barcode VARCHAR(MAX) ); INSERT INTO #Goods SELECT GoodSeq AS row, CONCAT(shipment_ref, '-', GoodSeq) AS shipmentreference, goods.good.value('(description)[1]', 'VARCHAR(10)') AS description, goods.good.value('(quantity)[1]', 'INT') AS quantity, barcodes.barcode AS barcode FROM -- 拆出每个独立货件节点 @xml.nodes('beginmsg/shipments/shipment') AS shipment(shipment) -- 拆出货件下每个独立货物节点 CROSS APPLY shipment.shipment.nodes('goods/good') AS goods(good) -- 提取货件基础参考值,避免重复计算 CROSS APPLY ( SELECT shipment.shipment.value('(references/reference)[1]', 'VARCHAR(100)') AS shipment_ref ) AS base_info -- 给当前货件下的货物按XML出现顺序生成连续序号 CROSS APPLY ( SELECT ROW_NUMBER() OVER ( PARTITION BY shipment_ref ORDER BY (SELECT NULL) ) AS GoodSeq ) AS good_seq -- 拆出货件下所有条码,生成对应顺序号 OUTER APPLY ( SELECT barcode_node.value('.', 'VARCHAR(MAX)') AS barcode, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS BarcodeSeq FROM shipment.shipment.nodes('barcodes/barcode') AS bc(barcode_node) ) AS barcodes -- 按同序号匹配条码,实现第N个货物对第N个条码 WHERE barcodes.BarcodeSeq = good_seq.GoodSeq SELECT * FROM #Goods
运行结果验证
执行修正后的查询,#Goods表的输出完全符合预期:
- 第一个货件(参考号GBP-4362)共3条货物记录:
- 第1行:货物描述Cookies、数量1,匹配第1个条码
7829348742910092309325 - 第2行:货物描述Cookies、数量3,匹配第2个条码
7829348742112344114414 - 第3行:货物描述Some food、数量2,匹配第3个条码
0984746166149566188446
- 第1行:货物描述Cookies、数量1,匹配第1个条码
- 第二个货件(参考号HBR-7211CX)共1条货物记录:
- 第1行:货物描述Televisions、数量2,匹配对应条码
0984746166149566188446
- 第1行:货物描述Televisions、数量2,匹配对应条码
- 兼容异常场景:若某货件下条码数量少于货物数量,未匹配到对应条码的条目会保留空条码值,不会出现所有货物绑定首个条码的问题
说明:顺序号使用
ORDER BY (SELECT NULL)生成,完全按照XML节点在源文本中的物理出现顺序编号,和「按出现顺序一一匹配」的规则完全对齐
内容的提问来源于stack exchange,提问作者forRJ
相关产品推荐
相关产品推荐

