SQL : How to get multiple XML parent node values inside new node dynamically?

Question

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 !

I'm confused about where the data comes from. None of the desired XML seems to match the original. Where does <SHIPMENTNO>UI45048839</SHIPMENTNO><TRIPSEQUENCE>1</TRIPSEQUENCE> come from for example? — Charlieface
– Charlieface, Commented Feb 10, 2022 at 16:53
I changed some values as they contain possible sensible information. — Dennis .VN
– Dennis .VN, Commented Mar 17, 2022 at 12:13

Charlieface · Accepted Answer · 2022-02-11 03:10:52Z

1

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().

edited Feb 11, 2022 at 3:10

answered Feb 10, 2022 at 16:55

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yitzhak Khabinsky Over a year ago

You can use a sequence local-name() = ("SHIPMENTNO","TRIPSEQUENCE","UNLOADDATE","UNLOADTIME")

Charlieface Over a year ago

I had no idea, you live and learn. Thanks

Yitzhak Khabinsky Over a year ago

Good answer, +1 from my side!

Dennis .VN Over a year ago

This is what i was looking for ! Marked as answer. Thanks a lot.

Collectives™ on Stack Overflow

SQL : How to get multiple XML parent node values inside new node dynamically?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related