No result using Nodes() method for XML

Question

I'm struggling to get a result at opening a xml document using the Nodes() method. Here is what I wrote for testing:

SELECT 
    @xml= CAST(XmlDoc AS XML)
FROM 
    OPENROWSET(BULK 'F:\F_3017541.XML' ,SINGLE_BLOB) AS tab(XmlDoc)

SELECT @xml;--Result 

select  
     col.value('(ShipmentNo)[1]', 'varchar(150)') as ShipmentNO ,
     col.value('(DomShipmentNo)[1]', 'varchar(150)') as DomShipmentNo
       
from @xml.nodes('/Shipment') AS tab(col)

The @xml variable will store the following schema:

<FreightAdviceMsg Version="1.0" Sender="Test" ShippingUnit="DE">
  <Shipment DomShipmentNo="Shipno123" ShipmentNo="123456" ShippingDate="2020-08-04" >
    <Packages>
      <Package PkgNo="987654" DomPkgNo="654321" PalletType="PC" GrossWeight="3.7" Volume="0" />
    </Packages>
  </Shipment>
</FreightAdviceMsg>

I do not get any results with the above query (no error, only no results).

Is there anything I missed? At the end of the day, I only want to store all shipment and packages Infos in a MSSQL table.

Did I use the wrong method? Unfortunately, I have not worked much with xml in the past, therefore I'm happy with any comment.

Thanks in advance :-)

Answer 1

You're close! Try this:

DECLARE @xml XML = 
'<FreightAdviceMsg Version="1.0" Sender="Test" ShippingUnit="DE">
  <Shipment DomShipmentNo="Shipno123" ShipmentNo="123456" ShippingDate="2020-08-04" >
    <Packages>
      <Package PkgNo="987654" DomPkgNo="654321" PalletType="PC" GrossWeight="3.7" Volume="0" />
    </Packages>
  </Shipment>
</FreightAdviceMsg>';

SELECT  
     col.value('@ShipmentNo', 'varchar(150)') AS ShipmentNO,
     col.value('@DomShipmentNo', 'varchar(150)') AS DomShipmentNo
FROM @xml.nodes('//FreightAdviceMsg/Shipment') AS tab(col);

Returns

+------------+---------------+
| ShipmentNO | DomShipmentNo |
+------------+---------------+
|     123456 | Shipno123     |
+------------+---------------+