T-SQL循环生成多行XML块仅获最后结果，求解决方法

因应用限制仅能使用T-SQL构建用于Web服务的XML报文，需根据临时表的每行数据生成对应结构的<contentBody>XML块并拼接。

临时表数据 #

期望生成的XML结果 #

<contentBody>
  <exportShipment>
    <customsDocument>
      <Type>IN</Type>
      <documentNumber>UK22G1234567</documentNumber>
    </customsDocument>
    <equipment>
      <equipmentNumber>00BJV1</equipmentNumber>
      <reference>ST99999</reference>
    </equipment>
  </exportShipment>
</contentBody>
<contentBody>
  <exportShipment>
    <customsDocument>
      <Type>IN</Type>
      <documentNumber>UK22G8910112</documentNumber>
    </customsDocument>
    <equipment>
      <equipmentNumber>00ABC2</equipmentNumber>
      <reference>ST12345</reference>
    </equipment>
  </exportShipment>
</contentBody>

当前测试代码 #

DECLARE @Counter INT, @MaxId INT, @number VARCHAR(50), @CrossingNr VARCHAR(50), @Equipment VARCHAR(50), @contentbody XML

DECLARE @tbl TABLE (row INT, numbers VARCHAR(50), crossingnumber VARCHAR(100), equipment VARCHAR(100), salesorder VARCHAR(50))
INSERT INTO @tbl (row, numbers, crossingnumber, equipment, salesorder)  
    VALUES
    (1, 'UK22G1234567', 'ST99999', '00BJV1', '12345-1'),
    (2, 'UK22G8910112', 'ST12345', '00ABC2', '98765-1')

SELECT @Counter = MIN(row) , @MaxId = MAX(row) FROM @tbl
WHILE(@Counter IS NOT NULL AND @Counter <= @MaxId)
    BEGIN
        SELECT @number = numbers FROM @tbl WHERE row = @Counter
        SELECT @CrossingNr = crossingnumber FROM @tbl WHERE row = @Counter
        SELECT @Equipment = equipment FROM @tbl WHERE row = @Counter

        SELECT @contentbody = (
            (SELECT 
                (SELECT 
                    (SELECT 
                        "Type" = 'IN',
                        "documentNumber" = @Number
                    FOR XML PATH('customsDocument'), TYPE),
                    (SELECT 
                        "equipmentNumber" =  @Equipment,
                        "reference" = @CrossingNr
                    FOR XML PATH('equipment'), TYPE)
                FOR XML PATH('exportShipment'), TYPE)
            FOR XML PATH('contentBody'), TYPE)
            )
        SET @Counter  = @Counter  + 1        
    END

SELECT * FROM @tbl
SELECT @contentbody

当前问题：循环遍历逻辑正常，但每次循环会覆盖@contentbody变量，最终仅能得到最后一行的XML结果，无法实现所有XML块的拼接。

问题根源 #

每次循环中直接给@contentbody赋值，会覆盖之前存储的XML内容，而XML变量本身不支持直接追加操作。需要将其转换为字符串类型进行拼接，之后可按需转回XML类型。

修改后的循环代码 #

DECLARE @Counter INT, @MaxId INT, @number VARCHAR(50), @CrossingNr VARCHAR(50), @Equipment VARCHAR(50), @contentbody NVARCHAR(MAX) = '' -- 改为字符串变量存储

DECLARE @tbl TABLE (row INT, numbers VARCHAR(50), crossingnumber VARCHAR(100), equipment VARCHAR(100), salesorder VARCHAR(50))
INSERT INTO @tbl (row, numbers, crossingnumber, equipment, salesorder)  
    VALUES
    (1, 'UK22G1234567', 'ST99999', '00BJV1', '12345-1'),
    (2, 'UK22G8910112', 'ST12345', '00ABC2', '98765-1')

SELECT @Counter = MIN(row) , @MaxId = MAX(row) FROM @tbl
WHILE(@Counter IS NOT NULL AND @Counter <= @MaxId)
    BEGIN
        SELECT @number = numbers FROM @tbl WHERE row = @Counter
        SELECT @CrossingNr = crossingnumber FROM @tbl WHERE row = @Counter
        SELECT @Equipment = equipment FROM @tbl WHERE row = @Counter

        -- 生成当前行的XML并转换为字符串，追加到@contentbody
        SET @contentbody = @contentbody + CAST((
            (SELECT 
                (SELECT 
                    (SELECT 
                        "Type" = 'IN',
                        "documentNumber" = @Number
                    FOR XML PATH('customsDocument'), TYPE),
                    (SELECT 
                        "equipmentNumber" =  @Equipment,
                        "reference" = @CrossingNr
                    FOR XML PATH('equipment'), TYPE)
                FOR XML PATH('exportShipment'), TYPE)
            FOR XML PATH('contentBody'), TYPE) AS NVARCHAR(MAX))
        
        SET @Counter  = @Counter  + 1        
    END

-- 输出最终拼接结果，可选择字符串或XML类型
SELECT @contentbody AS FinalXMLString
-- SELECT CAST(@contentbody AS XML) AS FinalXML

更优方案：避免循环，直接用集合式XML生成 #

T-SQL的FOR XML支持直接基于表数据批量生成目标XML，无需循环，效率更高：

DECLARE @tbl TABLE (row INT, numbers VARCHAR(50), crossingnumber VARCHAR(100), equipment VARCHAR(100), salesorder VARCHAR(50))
INSERT INTO @tbl (row, numbers, crossingnumber, equipment, salesorder)  
    VALUES
    (1, 'UK22G1234567', 'ST99999', '00BJV1', '12345-1'),
    (2, 'UK22G8910112', 'ST12345', '00ABC2', '98765-1')

SELECT 
    (SELECT 
        (SELECT 
            'IN' AS "Type",
            t.numbers AS "documentNumber"
        FOR XML PATH('customsDocument'), TYPE),
        (SELECT 
            t.equipment AS "equipmentNumber",
            t.crossingnumber AS "reference"
        FOR XML PATH('equipment'), TYPE)
    FOR XML PATH('exportShipment'), TYPE)
FROM @tbl t
FOR XML PATH('contentBody'), TYPE, ROOT('') -- ROOT('')去掉默认根节点

该方案直接通过表数据生成所有<contentBody>块，结果与期望完全一致，且性能优于循环写法。

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