You need to enable JavaScript to run this app. T-SQL解析XML写入临时表时如何按序匹配货物与对应条码
优惠活动
大模型
产品
解决方案
定价
更多

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
  • 第二个货件(参考号HBR-7211CX)共1条货物记录:
    • 第1行:货物描述Televisions、数量2,匹配对应条码0984746166149566188446
  • 兼容异常场景:若某货件下条码数量少于货物数量,未匹配到对应条码的条目会保留空条码值,不会出现所有货物绑定首个条码的问题

说明:顺序号使用ORDER BY (SELECT NULL)生成,完全按照XML节点在源文本中的物理出现顺序编号,和「按出现顺序一一匹配」的规则完全对齐

内容的提问来源于stack exchange,提问作者forRJ

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.26 14:36:03