x

TSQL Shred XML File with attributes and elements

I have the following xml file:

 <?xml version="1.0" encoding="UTF-8"?>
 <it:itSendNote xmlns:cmn="urn:locallink:mapper:sl:commontypes"
     xmlns:it="urn:locallink:mapper:sl:Item_track">
     <it:ControlFileHeader>
         <it:FileSenderNumber>ABC CO</it:FileSenderNumber>
         <it:FileReceiverNumber>ABC CO</it:FileReceiverNumber>
         <it:FileControlNumber>012765174</it:FileControlNumber>
         <it:FileDate>2015-05-01</it:FileDate>
         <it:FileTime>12:46:08</it:FileTime>
     </it:ControlFileHeader>  
     <it:MessageBody>
         <cmn:ShipmentIdentifiers>
             <cmn:SomeElement>SomeName</cmn:SomeElement>
             <cmn:ShipmentId type="ShipmentNumber">20392</cmn:ShipmentId>
             <cmn:ShipmentId type="BillOfLading">877454</cmn:ShipmentId>
         </cmn:ShipmentIdentifiers>
     </it:MessageBody>
 </it:itSendNote>


I can get most of what I need with the following: DECLARE @XmlFile XML

 SELECT  @XmlFile = BulkColumn
 FROM    OPENROWSET(BULK 'C:Test.xml', SINGLE_BLOB) x;
 WITH XMLNAMESPACES (
  'urn:locallink:mapper:sl:commontypes' AS cmn,
  'urn:locallink:mapper:sl:Item_track' AS it 
 )
 SELECT
  n.value('it:FileSenderNumber[1]','VARCHAR(30)') AS FileNum,
  d.value('cmn:SomeElement[1]','VARCHAR(30)') AS SomeElement,
  d.value('cmn:ShipmentId[1]','VARCHAR(30)') AS SomeId
 FROM @XmlFile.nodes('it:itSendNote/it:ControlFileHeader')  x1(n)
 CROSS APPLY @XmlFile.nodes('it:itSendNote/it:MessageBody/cmn:ShipmentIdentifiers') x2(d)

My problem is that for shipment id I get the 20392 but I want to know that it is the shipment number and I also want the bill of lading. I just can't seem to figure it out and I would appreciate any help.

more ▼

asked May 11, 2015 at 05:17 PM in Default

avatar image

lidanny
0 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Are there only ever 2 ShipmentIds? Do you want the results as separate rows or as columns?

 WITH XMLNAMESPACES (
   'urn:locallink:mapper:sl:commontypes' AS cmn,
   'urn:locallink:mapper:sl:Item_track' AS it 
  )
  SELECT
   n.value('it:FileSenderNumber[1]','VARCHAR(30)') AS FileNum,
   d.value('cmn:SomeElement[1]','VARCHAR(30)') AS SomeElement,
   d.value('cmn:ShipmentId[1]/@type', 'varchar(30)') as FirstShipmentIDType,
   d.value('cmn:ShipmentId[1]','VARCHAR(30)') AS FirstShipmentIDValue,
   d.value('cmn:ShipmentId[2]/@type', 'varchar(30)') as SecondShipmentIDType,
   d.value('cmn:ShipmentId[2]','VARCHAR(30)') AS SecondShipmentIDValue
 
  FROM @XmlFile.nodes('it:itSendNote/it:ControlFileHeader')  x1(n)
  CROSS APPLY @XmlFile.nodes('it:itSendNote/it:MessageBody/cmn:ShipmentIdentifiers') x2(d)
more ▼

answered May 12, 2015 at 08:10 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

Thanks Kev! The /@type is the part that hung me up. There are more than two shipments at times and the results should be in rows. I took your modifications and I am in good shape now. Thanks again Dan

May 16, 2015 at 01:20 AM lidanny
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x438
x202

asked: May 11, 2015 at 05:17 PM

Seen: 88 times

Last Updated: May 16, 2015 at 01:20 AM

Copyright 2017 Redgate Software. Privacy Policy