T-SQL combine based on values with for XML path

Question

I am running stuck in something I can't get my head around. I have some mock-up data to simulate:

DECLARE @tbl TABLE (orderReference VARCHAR(50), shipmentReference VARCHAR(50), additionalComments VARCHAR(200))
DECLARE @finalXML XML

INSERT INTO @tbl
VALUES 
('1234567890', 'shipmentRef1', 'Some additional text'),
('1234567890', 'shipmentRef2', 'Some additional text for shipment ref 2'),
('0987654321', 'shipmentRef999', 'Some additional text for shipment ref 999')

1 table with 3 columns (orderReference, shipmentReference & additionalComments):

orderReference	shipmentReference	additionalComments
1234567890	shipmentRef1	Some additional text
1234567890	shipmentRef2	Some additional text ref 2
0987654321	shipmentRef999	Some additional text for shipment ref 999

With this table I am creating a new XML variable to build up the message I am going to use:

SET @finalXML = (
    SELECT
    "description" = 'This is on top level',
    "transportbookings" = (
        SELECT
            "Order_reference" = i.orderReference,
            "shipments" = (
                    SELECT  
                    "reference" = i.shipmentReference
                FOR XML PATH('shipment'), TYPE
            )
            FROM @tbl i
        FOR XML PATH('transportbooking'), TYPE
    
    )   
    FOR XML PATH ('import'), TYPE
)

At this point it is creating an XML like so:

<import>
  <description>This is on top level</description>
  <transportbookings>
    <transportbooking>
      <Order_reference>0987654321</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef999</reference>
        </shipment>
      </shipments>
    </transportbooking>
    <transportbooking>
      <Order_reference>1234567890</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef1</reference>
        </shipment>
      </shipments>
    </transportbooking>
    <transportbooking>
      <Order_reference>1234567890</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef2</reference>
        </shipment>
      </shipments>
    </transportbooking>
  </transportbookings>
</import>

I am trying to achieve that there are 2 "transportbooking" elements based on the "orderReference" value. So it needs to create 1 "transportbooking" element for "1234567890" with 2 "shipments" elements in it.

So the expected output should be:

<import>
  <description>This is on top level</description>
  <transportbookings>
    <transportbooking>
      <Order_reference>0987654321</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef999</reference>
        </shipment>
      </shipments>
    </transportbooking>
    <transportbooking>
      <Order_reference>1234567890</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef1</reference>
        </shipment>
        <shipment>
          <reference>shipmentRef2</reference>
        </shipment>
      </shipments>
    </transportbooking>
  </transportbookings>
</import>

I've tried to use an outer apply, moved the "FROM @tbl i" to another element, tried to 'GROUP BY' the orderReference column.

I am running in circles, does anyone have the eye-opener for me?

Answer 1

Please try the following solution.

SQL

DECLARE @tbl TABLE (orderReference VARCHAR(50), shipmentReference VARCHAR(50), additionalComments VARCHAR(200));
INSERT INTO @tbl VALUES 
('1234567890', 'shipmentRef1', 'Some additional text'),
('1234567890', 'shipmentRef2', 'Some additional text for shipment ref 2'),
('0987654321', 'shipmentRef999', 'Some additional text for shipment ref 999');

DECLARE @finalXML XML = 
(SELECT 'This is on top level' AS [description]
, (
    SELECT p.orderReference AS [Order_reference]
    , (SELECT c.shipmentReference AS [reference] 
        FROM @tbl AS c
        WHERE p.orderReference = c.orderReference
        FOR XML PATH('shipment'), TYPE, ROOT('shipments'))
FROM @tbl AS p
GROUP BY orderReference
FOR XML PATH('transportbooking'), TYPE, ROOT('transportbookings'))
FOR XML PATH(''), TYPE, ROOT('import'));

-- test
SELECT @finalXML;

Output XML

<import>
  <description>This is on top level</description>
  <transportbookings>
    <transportbooking>
      <Order_reference>0987654321</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef999</reference>
        </shipment>
      </shipments>
    </transportbooking>
    <transportbooking>
      <Order_reference>1234567890</Order_reference>
      <shipments>
        <shipment>
          <reference>shipmentRef1</reference>
        </shipment>
        <shipment>
          <reference>shipmentRef2</reference>
        </shipment>
      </shipments>
    </transportbooking>
  </transportbookings>
</import>