如何通过FOR XML PATH按orderReference合并生成指定结构XML？

要实现每个orderReference对应单个transportbooking节点，并嵌套该订单下所有shipment节点，你可以通过分组外层查询+嵌套子查询生成XML的方式解决，核心是利用FOR XML PATH的嵌套能力和TYPE关键字保留原生XML结构。

1. 模拟数据表示例 #

先创建与你结构匹配的测试临时表：

CREATE TABLE #MockData (
    orderReference VARCHAR(50),
    shipmentReference VARCHAR(50),
    additionalComments VARCHAR(200)
);

INSERT INTO #MockData VALUES
('ORD001', 'SHIP001', 'Comment for shipment 1'),
('ORD001', 'SHIP002', 'Comment for shipment 2'),
('ORD002', 'SHIP003', 'Comment for shipment 3');

2. 正确的XML生成查询 #

SELECT 
    md.orderReference AS '@orderReference', -- 将订单号设为transportbooking的属性
    (
        -- 子查询生成当前订单下的所有shipment节点集合
        SELECT 
            subMd.shipmentReference AS '@shipmentReference',
            subMd.additionalComments AS 'additionalComments'
        FROM #MockData subMd
        WHERE subMd.orderReference = md.orderReference -- 关联外层订单号，确保仅查询当前订单的运单
        FOR XML PATH('shipment'), TYPE -- TYPE关键字保证子查询的XML结果直接嵌套，不被转义为字符串
    ) AS 'shipments' -- 包裹所有shipment的父节点
FROM #MockData md
GROUP BY md.orderReference -- 按订单号分组，确保每个订单仅生成一个transportbooking节点
FOR XML PATH('transportbooking'), ROOT('transportbookings'); -- 指定外层节点和根节点名称

3. 关键逻辑说明 #

• 外层GROUP BY：按orderReference分组，从根源上避免同一订单生成多个transportbooking节点。
• 嵌套子查询+TYPE：子查询筛选当前订单的所有运单数据，生成shipment节点集合；TYPE是核心，它让子查询返回的XML直接合并到外层结果中，不会被转义成纯文本。
• 节点/属性控制：通过AS '@属性名'定义节点属性，FOR XML PATH('节点名')指定XML节点的名称，灵活控制最终XML结构。

输出示例XML #

<transportbookings>
  <transportbooking orderReference="ORD001">
    <shipments>
      <shipment shipmentReference="SHIP001">
        <additionalComments>Comment for shipment 1</additionalComments>
      </shipment>
      <shipment shipmentReference="SHIP002">
        <additionalComments>Comment for shipment 2</additionalComments>
      </shipment>
    </shipments>
  </transportbooking>
  <transportbooking orderReference="ORD002">
    <shipments>
      <shipment shipmentReference="SHIP003">
        <additionalComments>Comment for shipment 3</additionalComments>
      </shipment>
    </shipments>
  </transportbooking>
</transportbookings>

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