question

lidanny avatar image
lidanny asked

TSQL Shred XML File with attributes and elements

I have the following xml file: ABC CO ABC CO 012765174 2015-05-01 12:46:08 SomeName 20392 877454 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.
tsqlxml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.