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.
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)
15 People are following this question.
