How do i get the desired output dynamically ?

In the source XML i have multiple child nodes for SHIPMENTS. I need to take partial information from them and map them to a new output inside SQL ( i use SSMS ). I only need to output the shipmentno, tripsequence, unloaddate and unloadtime - but for multiple shipments.

Source XML :

<SHIPMENTS>
    <Laden>JAMAICA</Laden>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>SD</Activiteit>
    <TRIPSEQUENCE>10</TRIPSEQUENCE>
    <Tpttype>FRE</Tpttype>
    <SHIPMENTNO>42069</SHIPMENTNO>
</SHIPMENTS>
<SHIPMENTS>
    <Lossen>NEW ZEALANG</Lossen>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>DS</Activiteit>
    <TRIPSEQUENCE>70</TRIPSEQUENCE>
    <Tpttype>DEZ</Tpttype>
    <SHIPMENTNO>42169</SHIPMENTNO>
    <UNLOADDATE>15/12</UNLOADDATE>
    <UNLOADTIME>09:00:00</UNLOADTIME>
</SHIPMENTS>
<SHIPMENTS>
    <Lossen>LONDON</Lossen>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>LO</Activiteit>
    <TRIPSEQUENCE>80</TRIPSEQUENCE>
    <Tpttype>DSZ</Tpttype>
    <SHIPMENTNO>42269</SHIPMENTNO>
    <UNLOADDATE>15/12</UNLOADDATE>
    <UNLOADTIME>09:00:00</UNLOADTIME>
</SHIPMENTS>
<SHIPMENTS>
    <Lossen>LOUISIANA</Lossen>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>DS</Activiteit>
    <TRIPSEQUENCE>90</TRIPSEQUENCE>
    <Tpttype>GRO</Tpttype>
    <SHIPMENTNO>42369</SHIPMENTNO>
    <UNLOADDATE>15/12</UNLOADDATE>
    <UNLOADTIME>09:00:00</UNLOADTIME>
</SHIPMENTS>
<SHIPMENTS>
    <Lossen>KOS</Lossen>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>LO</Activiteit>
    <TRIPSEQUENCE>100</TRIPSEQUENCE>
    <Tpttype>GFS</Tpttype>
    <SHIPMENTNO>42369</SHIPMENTNO>
    <UNLOADDATE>15/12</UNLOADDATE>
    <UNLOADTIME>08:00:00</UNLOADTIME>
</SHIPMENTS>
<SHIPMENTS>
    <Lossen>ROTTERDAM</Lossen>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>LOL</Activiteit>
    <TRIPSEQUENCE>110</TRIPSEQUENCE>
    <Tpttype>GRO</Tpttype>
    <SHIPMENTNO>42469</SHIPMENTNO>
    <UNLOADDATE>15/12</UNLOADDATE>
    <UNLOADTIME>09:00:00</UNLOADTIME>
</SHIPMENTS>
<SHIPMENTS>
    <Lossen>TENERIFE</Lossen>
    <CLIENT>FAKE CLIENT</CLIENT>
    <Activiteit>LO</Activiteit>
    <TRIPSEQUENCE>120</TRIPSEQUENCE>
    <Tpttype>GRO</Tpttype>
    <SHIPMENTNO>42570</SHIPMENTNO>
    <UNLOADDATE>15/12</UNLOADDATE>
    <UNLOADTIME>09:00:00</UNLOADTIME>
</SHIPMENTS>

MISSION : In my Output XML, i need <SHIPMENTS/> To be the parent node, with multiple <SHIPMENT/> children containing the values from the source XML. This is how the output should look

DESIRED OUTPUT :

<SHIPMENTS>
    <SHIPMENT>
        <SHIPMENTNO>UI0010912</SHIPMENTNO>
        <TRIPSEQUENCE>1</TRIPSEQUENCE>
        <UNLOADDATE>20211012</UNLOADDATE>
        <UNLOADTIME>10:00</UNLOADTIME>
    </SHIPMENT>
    <SHIPMENT>
        <SHIPMENTNO>UI0010911</SHIPMENTNO>
        <TRIPSEQUENCE>2</TRIPSEQUENCE>
        <UNLOADDATE>20211012</UNLOADDATE>
        <UNLOADTIME>11:00</UNLOADTIME>
    </SHIPMENT>
    <SHIPMENT>
        <SHIPMENTNO>UI0010913</SHIPMENTNO>
        <TRIPSEQUENCE>3</TRIPSEQUENCE>
        <UNLOADDATE>20211012</UNLOADDATE>
        <UNLOADTIME>15:00</UNLOADTIME>
    </SHIPMENT>
</SHIPMENTS>

THE QUERY : This is the structure and the values that i need per child node :

SET @xml = (
    (SELECT 
N'' AS [TRIP/SHIPMENTS],
        N'' AS [TRIP/SHIPMENTS/SHIPMENT],
        N'UI45048839' AS [TRIP/SHIPMENTS/SHIPMENT/SHIPMENTNO],
        N'1' AS [TRIP/SHIPMENTS/SHIPMENT/TRIPSEQUENCE],
        @XML.query(N'(/SHIPMENTS/UNLOADDATE)[5]').value('.', N'nvarchar(MAX)') AS [TRIP/SHIPMENTS/SHIPMENT/UNLOADDATE],
        @XML.query(N'(/SHIPMENTS/UNLOADTIME)[5]').value('.', N'nvarchar(MAX)') AS [TRIP/SHIPMENTS/SHIPMENT/UNLOADTIME],
        N'' AS [TRIP/SHIPMENTS/SHIPMENT],
        N'UI45048841' AS [TRIP/SHIPMENTS/SHIPMENT/SHIPMENTNO],
        N'2' AS [TRIP/SHIPMENTS/SHIPMENT/TRIPSEQUENCE],
        @XML.query(N'(/SHIPMENTS/UNLOADDATE)[3]').value('.', N'nvarchar(MAX)') AS [TRIP/SHIPMENTS/SHIPMENT/UNLOADDATE],
        @XML.query(N'(/SHIPMENTS/UNLOADTIME)[3]').value('.', N'nvarchar(MAX)') AS [TRIP/SHIPMENTS/SHIPMENT/UNLOADTIME],
        N'UI45048840' AS [TRIP/SHIPMENTS/SHIPMENT/SHIPMENTNO],
        N'3' AS [TRIP/SHIPMENTS/SHIPMENT/TRIPSEQUENCE],
        @XML.query(N'(/SHIPMENTS/UNLOADDATE)[2]').value('.', N'nvarchar(MAX)') AS [TRIP/SHIPMENTS/SHIPMENT/UNLOADDATE],
        @XML.query(N'(/SHIPMENTS/UNLOADTIME)[2]').value('.', N'nvarchar(MAX)') AS [TRIP/SHIPMENTS/SHIPMENT/UNLOADTIME]
        FOR XML PATH (N'SHIPMENTS'), TYPE
    ) AS [SHIPMENTS/SHIPMENT]
FOR XML PATH(N''), ROOT(N'TRIPS'), TYPE
);
SELECT @xml AS [FileContent];

and obviously RETURNS :

<SHIPMENTS>
    <SHIPMENT>
      <SHIPMENTNO>UI45048839</SHIPMENTNO>
      <TRIPSEQUENCE>1</TRIPSEQUENCE>
      <UNLOADDATE>15/12</UNLOADDATE>
      <UNLOADTIME>09:00:00</UNLOADTIME>
      <SHIPMENTNO>UI45048841</SHIPMENTNO>
      <TRIPSEQUENCE>2</TRIPSEQUENCE>
      <UNLOADDATE>15/12</UNLOADDATE>
      <UNLOADTIME>09:00:00</UNLOADTIME>
      <SHIPMENTNO>UI45048840</SHIPMENTNO>
      <TRIPSEQUENCE>3</TRIPSEQUENCE>
      <UNLOADDATE>15/12</UNLOADDATE>
      <UNLOADTIME>09:00:00</UNLOADTIME>
    </SHIPMENT>
</SHIPMENTS>

I don't like my approach as it is mainly hardcoding and repeating the result.
How do i get the desired output dynamically ?

Thank you for your time !

CodePudding user response：

It seems you are massively over-complicating this. You can use .query() to generate constructed XML containing the correct nodes

SELECT @xml.query('
    <SHIPMENTS>
    {
        for $s in /SHIPMENTS
        return
          <SHIPMENT>
              {
                $s/*[
                  local-name() = ("SHIPMENTNO", "TRIPSEQUENCE", "UNLOADDATE", "UNLOADTIME")
                  ]
              }
          </SHIPMENT>
    }
    </SHIPMENTS>
');

db<>fiddle

• This creates a new root SHIPMENTS node.
• Within that, it takes all original SHIPMENTS nodes and creates SHIPMENT nodes from them.
• Each of those only contains the four relevant child nodes. It takes all nodes by using a wildcard, then checks them using local-name().