WHILE loop to get 2 blocks of XML

Question

With T-SQL (I cannot use C# or any other scripting language due to application limitations) I am 'building' an XML message to post to a webservice.

A snippet within the XML message must be applied multiple times (depending on the amount of rows).

Below is a example of what I am trying to achieve: I am creating a temporary table, put in two rows.

row	numbers	crossingnumber	equipment	salesorder
1	UK22G1234567	ST99999	00BJV1	12345-1
2	UK22G8910112	ST12345	00ABC2	67891-1

For each row I am trying to get a XML block with the information per row. The expected result is:

<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>

For testing I am using the code below:

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

So I am looping through the rows which is okay. But I am only getting the latest result, and I want to get both concatenated in some way.

What am I missing here?

Answer 1

You don't need a WHILE, you can just use a single FOR 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
  [exportShipment/customsDocument/Type] = 'IN',
  [exportShipment/customsDocument/documentNumber] = t.numbers,
  [exportShipment/equipment/equipmentNumber] = t.equipment,
  [exportShipment/equipment/reference] = t.crossingnumber
FROM @tbl t
FOR XML PATH('contentBody'), TYPE;

If you need each row as a separate XML document, you can do it in a correlated subquery.

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 
      [customsDocument/Type] = 'IN',
      [customsDocument/documentNumber] = t.numbers,
      [equipment/equipmentNumber] = t.equipment,
      [equipment/reference] = t.crossingnumber
    FOR XML PATH('exportShipment'), ROOT('contentBody'), TYPE
  )
FROM @tbl t;

db<>fiddle