I am trying to return data of node "attachDocument" of XML script below. Script below to return 7 rows (7 nodes of “atttachDocument” under consignment node) but should be 8 (one under root node “declaration” label as **Struggling to include **) that I am struggling to return it thru Query... Can someone help query to return all 8 rows of node "attachDocument"?
Thanks in Advance.
XML...
DECLARE @sqlxml XML =
N'<declaration xmlns="http://xyz.IND/NCCS/v2">
<identification>43006267</identification>
<type>ICR</type>
<functionalReference>D2710ICRAT0001</functionalReference>
<transactionType>9</transactionType>
<submitter>
<identifier>40063605C</identifier>
</submitter>
<additionalDocument>
<type>OTH</type>
<identifier>213</identifier>
</additionalDocument>
<additionalInformationMAC>
<accountNumber>123123</accountNumber>
<accountName>Sample MPI Ac Name</accountName>
</additionalInformationMAC>
<carrier>
<identifier>40063605C</identifier>
</carrier>
<consignment>
<sequence>1</sequence>
<additionalInformationHAN>
<text>HANDLING INSTRUCTIONS</text>
</additionalInformationHAN>
<additionalInformationITR>
<departureDate>20170414</departureDate>
<craftName>A.P. MOLLER</craftName>
<transportMode>1</transportMode>
<voyageNumber>123123</voyageNumber>
</additionalInformationITR>
<additionalInformationMAC>
<accountNumber>1233445</accountNumber>
<accountName>Test MPI Account Name</accountName>
</additionalInformationMAC>
<additionalInformationOSP>
<code>N</code>
</additionalInformationOSP>
<additionalInformationOSR>
<text>148952973</text>
</additionalInformationOSR>
<associatedTransportDocument>
<identifier>YH4094034</identifier>
<type>MB</type>
</associatedTransportDocument>
<consignee>
<name>CONSIGNEE</name>
<address>
<city>a fluffy chicken.</city>
<countryCode>IND</countryCode>
<state>That is all.</state>
<line>Once upon a time there was</line>
<postcode>Sorry.</postcode>
</address>
</consignee>
<consignmentItem>
<sequence>1</sequence>
<additionalInformationMAS>
<approvedSystemNumber>323232</approvedSystemNumber>
</additionalInformationMAS>
<commodity>
<cargoDescription>Cargo.</cargoDescription>
<identifier>45612389</identifier>
<commodityIdentifierType>MC</commodityIdentifierType>
<temperature>
<flashpoint>
<quantity>250</quantity>
</flashpoint>
<storageRequirement>
<quantity>10</quantity>
</storageRequirement>
<minimumStorageRequirement>
<quantity>-1</quantity>
</minimumStorageRequirement>
<maximumStorageRequirement>
<quantity>52</quantity>
</maximumStorageRequirement>
</temperature>
</commodity>
<goodsMeasure>
<grossWeight>
<quantity>10</quantity>
<unitOfMeasurement>KGM</unitOfMeasurement>
</grossWeight>
</goodsMeasure>
<packaging>
<sequence>1</sequence>
<quantity>10</quantity>
<type>PF</type>
</packaging>
<transportEquipment>
<identifier>CQDE1928327</identifier>
</transportEquipment>
</consignmentItem>
<consignor>
<name>CONSIGNOR</name>
<address>
<city>fields carefully</city>
<countryCode>AF</countryCode>
<state>you could write</state>
<line>If you use these</line>
<postcode>a story</postcode>
</address>
</consignor>
<deliveryDestination>
<identifier>25002</identifier>
<name>CONSIGNEE</name>
<address>
<city>a fluffy chicken.</city>
<countryCode>IND</countryCode>
<state>That is all.</state>
<line>Once upon a time there was</line>
<postcode>Sorry.</postcode>
</address>
</deliveryDestination>
<freightPaymentMethod>AB</freightPaymentMethod>
<goodsConsignedPlace>AEAJM</goodsConsignedPlace>
<goodsLocation>
<identifier>19258K</identifier>
</goodsLocation>
<loadingLocation>AUABP</loadingLocation>
<deliveryNotificationParty>
<name>Yetta Nuther</name>
<communication>
<value>yettanuther3377@example.invalid</value>
<type>EM</type>
</communication>
</deliveryNotificationParty>
<stuffingEstablishment>
<name>Pack Location Name</name>
<address>
<city>City</city>
<countryCode>IND</countryCode>
<state>My</state>
<line>Street</line>
<postcode>000</postcode>
</address>
</stuffingEstablishment>
<bill>
<identifier>UJ940344</identifier>
<type>BM</type>
</bill>
<transportEquipment>
<sequence>1</sequence>
<additionalInformationMAS>
<approvedSystemNumber>43434343</approvedSystemNumber>
</additionalInformationMAS>
<characteristicsCode>12</characteristicsCode>
<fullness>5</fullness>
<identifier>CQDE1928327</identifier>
<seal>
<sequence>1</sequence>
<identifier>UJ43433434</identifier>
</seal>
<stowPosition>BBBRRTTT</stowPosition>
<containerPackLocation>
<name>Pack Location Name</name>
</containerPackLocation>
</transportEquipment>
<transitDestination>
<identifier>25066</identifier>
</transitDestination>
<attachDocument>
<category>CQD</category>
<mimeCode>image/jpeg</mimeCode>
<URI>efda0520-2e4e-4916-a89a-615e52671e01-location</URI>
<filename>0.jpg</filename>
<sequence>1</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>INV</category>
<mimeCode>image/jpeg</mimeCode>
<URI>4d0a0102-291c-438a-abe6-679bcfc300c6-location</URI>
<filename>0.jpg</filename>
<sequence>2</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>PER</category>
<mimeCode>image/jpeg</mimeCode>
<URI>2f6dcc50-a9d2-4d70-8688-89d7ce636bd4-location</URI>
<filename>0.jpg</filename>
<sequence>3</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>OTH</category>
<mimeCode>image/jpeg</mimeCode>
<URI>850766a2-3c09-42fc-ad6a-20de89a3b255-location</URI>
<filename>0.jpg</filename>
<sequence>4</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>PAC</category>
<mimeCode>image/jpeg</mimeCode>
<URI>1468c726-dbf2-4532-9252-b027e9fc523f-location</URI>
<filename>0.jpg</filename>
<sequence>5</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>BOL</category>
<mimeCode>image/jpeg</mimeCode>
<URI>7942b5a3-93ba-493f-b2b3-cf504b179a17-location</URI>
<filename>0.jpg</filename>
<sequence>6</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>CER</category>
<mimeCode>image/jpeg</mimeCode>
<URI>a2f23eb0-9dab-4851-9929-31b4273f26d5-location</URI>
<filename>0.jpg</filename>
<sequence>7</sequence>
<contentSize>35581</contentSize>
</attachDocument>
</consignment>
<remarks>
<text>Ghost in the shell.</text>
</remarks>
<attachDocument> **Struggling to include ***
<category>OTH</category>
<mimeCode>image/jpeg</mimeCode>
<URI>4d4f5d5b-cbe7-4751-8817-27b05e58ce9e-location</URI>
<filename>0.jpg</filename>
<sequence>1</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<TSWExtension>
<MIGVersion>V1.0</MIGVersion>
</TSWExtension>
</declaration>'
Query...
;WITH XMLNAMESPACES (DEFAULT 'http://xyz.IND/NCCS/v2')
SELECT
response.value('(category/text())[1]','varchar(50)') as Category,
response.value('(mimeCode/text())[1]','varchar(50)') as MimeCode,
response.value('(URI/text())[1]','varchar(50)') as URI,
response.value('(filename/text())[1]','varchar(50)') as [FileName],
response.value('(contentSize/text())[1]','varchar(50)') as ContentSize,
response.value('(sequence/text())[1]','varchar(100)') as Sequence
FROM @sqlxml.nodes('/declaration/consignment/attachDocument') AS T(response);
Please try the following solution.
It will find the <attachDocument> element anywhere under the root of the XML.
SQL
DECLARE @xml XML =
N'<declaration xmlns="http://xyz.IND/NCCS/v2">
<identification>43006267</identification>
<type>ICR</type>
<functionalReference>D2710ICRAT0001</functionalReference>
<transactionType>9</transactionType>
<submitter>
<identifier>40063605C</identifier>
</submitter>
<additionalDocument>
<type>OTH</type>
<identifier>213</identifier>
</additionalDocument>
<additionalInformationMAC>
<accountNumber>123123</accountNumber>
<accountName>Sample MPI Ac Name</accountName>
</additionalInformationMAC>
<carrier>
<identifier>40063605C</identifier>
</carrier>
<consignment>
<sequence>1</sequence>
<additionalInformationHAN>
<text>HANDLING INSTRUCTIONS</text>
</additionalInformationHAN>
<additionalInformationITR>
<departureDate>20170414</departureDate>
<craftName>A.P. MOLLER</craftName>
<transportMode>1</transportMode>
<voyageNumber>123123</voyageNumber>
</additionalInformationITR>
<additionalInformationMAC>
<accountNumber>1233445</accountNumber>
<accountName>Test MPI Account Name</accountName>
</additionalInformationMAC>
<additionalInformationOSP>
<code>N</code>
</additionalInformationOSP>
<additionalInformationOSR>
<text>148952973</text>
</additionalInformationOSR>
<associatedTransportDocument>
<identifier>YH4094034</identifier>
<type>MB</type>
</associatedTransportDocument>
<consignee>
<name>CONSIGNEE</name>
<address>
<city>a fluffy chicken.</city>
<countryCode>IND</countryCode>
<state>That is all.</state>
<line>Once upon a time there was</line>
<postcode>Sorry.</postcode>
</address>
</consignee>
<consignmentItem>
<sequence>1</sequence>
<additionalInformationMAS>
<approvedSystemNumber>323232</approvedSystemNumber>
</additionalInformationMAS>
<commodity>
<cargoDescription>Cargo.</cargoDescription>
<identifier>45612389</identifier>
<commodityIdentifierType>MC</commodityIdentifierType>
<temperature>
<flashpoint>
<quantity>250</quantity>
</flashpoint>
<storageRequirement>
<quantity>10</quantity>
</storageRequirement>
<minimumStorageRequirement>
<quantity>-1</quantity>
</minimumStorageRequirement>
<maximumStorageRequirement>
<quantity>52</quantity>
</maximumStorageRequirement>
</temperature>
</commodity>
<goodsMeasure>
<grossWeight>
<quantity>10</quantity>
<unitOfMeasurement>KGM</unitOfMeasurement>
</grossWeight>
</goodsMeasure>
<packaging>
<sequence>1</sequence>
<quantity>10</quantity>
<type>PF</type>
</packaging>
<transportEquipment>
<identifier>CQDE1928327</identifier>
</transportEquipment>
</consignmentItem>
<consignor>
<name>CONSIGNOR</name>
<address>
<city>fields carefully</city>
<countryCode>AF</countryCode>
<state>you could write</state>
<line>If you use these</line>
<postcode>a story</postcode>
</address>
</consignor>
<deliveryDestination>
<identifier>25002</identifier>
<name>CONSIGNEE</name>
<address>
<city>a fluffy chicken.</city>
<countryCode>IND</countryCode>
<state>That is all.</state>
<line>Once upon a time there was</line>
<postcode>Sorry.</postcode>
</address>
</deliveryDestination>
<freightPaymentMethod>AB</freightPaymentMethod>
<goodsConsignedPlace>AEAJM</goodsConsignedPlace>
<goodsLocation>
<identifier>19258K</identifier>
</goodsLocation>
<loadingLocation>AUABP</loadingLocation>
<deliveryNotificationParty>
<name>Yetta Nuther</name>
<communication>
<value>yettanuther3377@example.invalid</value>
<type>EM</type>
</communication>
</deliveryNotificationParty>
<stuffingEstablishment>
<name>Pack Location Name</name>
<address>
<city>City</city>
<countryCode>IND</countryCode>
<state>My</state>
<line>Street</line>
<postcode>000</postcode>
</address>
</stuffingEstablishment>
<bill>
<identifier>UJ940344</identifier>
<type>BM</type>
</bill>
<transportEquipment>
<sequence>1</sequence>
<additionalInformationMAS>
<approvedSystemNumber>43434343</approvedSystemNumber>
</additionalInformationMAS>
<characteristicsCode>12</characteristicsCode>
<fullness>5</fullness>
<identifier>CQDE1928327</identifier>
<seal>
<sequence>1</sequence>
<identifier>UJ43433434</identifier>
</seal>
<stowPosition>BBBRRTTT</stowPosition>
<containerPackLocation>
<name>Pack Location Name</name>
</containerPackLocation>
</transportEquipment>
<transitDestination>
<identifier>25066</identifier>
</transitDestination>
<attachDocument>
<category>CQD</category>
<mimeCode>image/jpeg</mimeCode>
<URI>efda0520-2e4e-4916-a89a-615e52671e01-location</URI>
<filename>0.jpg</filename>
<sequence>1</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>INV</category>
<mimeCode>image/jpeg</mimeCode>
<URI>4d0a0102-291c-438a-abe6-679bcfc300c6-location</URI>
<filename>0.jpg</filename>
<sequence>2</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>PER</category>
<mimeCode>image/jpeg</mimeCode>
<URI>2f6dcc50-a9d2-4d70-8688-89d7ce636bd4-location</URI>
<filename>0.jpg</filename>
<sequence>3</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>OTH</category>
<mimeCode>image/jpeg</mimeCode>
<URI>850766a2-3c09-42fc-ad6a-20de89a3b255-location</URI>
<filename>0.jpg</filename>
<sequence>4</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>PAC</category>
<mimeCode>image/jpeg</mimeCode>
<URI>1468c726-dbf2-4532-9252-b027e9fc523f-location</URI>
<filename>0.jpg</filename>
<sequence>5</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>BOL</category>
<mimeCode>image/jpeg</mimeCode>
<URI>7942b5a3-93ba-493f-b2b3-cf504b179a17-location</URI>
<filename>0.jpg</filename>
<sequence>6</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<attachDocument>
<category>CER</category>
<mimeCode>image/jpeg</mimeCode>
<URI>a2f23eb0-9dab-4851-9929-31b4273f26d5-location</URI>
<filename>0.jpg</filename>
<sequence>7</sequence>
<contentSize>35581</contentSize>
</attachDocument>
</consignment>
<remarks>
<text>Ghost in the shell.</text>
</remarks>
<attachDocument>**Struggling to include ***
<category>OTH</category>
<mimeCode>image/jpeg</mimeCode>
<URI>4d4f5d5b-cbe7-4751-8817-27b05e58ce9e-location</URI>
<filename>0.jpg</filename>
<sequence>1</sequence>
<contentSize>35581</contentSize>
</attachDocument>
<TSWExtension>
<MIGVersion>V1.0</MIGVersion>
</TSWExtension>
</declaration>';
WITH XMLNAMESPACES (DEFAULT 'http://xyz.IND/NCCS/v2')
SELECT c.value('(category/text())[1]','varchar(50)') as Category
, c.value('(mimeCode/text())[1]','varchar(50)') as MimeCode
, c.value('(URI/text())[1]','varchar(50)') as URI
, c.value('(filename/text())[1]','varchar(50)') as [FileName]
, c.value('(contentSize/text())[1]','varchar(50)') as ContentSize
, c.value('(sequence/text())[1]','varchar(100)') as [Sequence]
from @xml.nodes ('/declaration//attachDocument') AS t(c);
Output
+----------+------------+-----------------------------------------------+----------+-------------+----------+
| Category | MimeCode | URI | FileName | ContentSize | Sequence |
+----------+------------+-----------------------------------------------+----------+-------------+----------+
| CQD | image/jpeg | efda0520-2e4e-4916-a89a-615e52671e01-location | 0.jpg | 35581 | 1 |
| INV | image/jpeg | 4d0a0102-291c-438a-abe6-679bcfc300c6-location | 0.jpg | 35581 | 2 |
| PER | image/jpeg | 2f6dcc50-a9d2-4d70-8688-89d7ce636bd4-location | 0.jpg | 35581 | 3 |
| OTH | image/jpeg | 850766a2-3c09-42fc-ad6a-20de89a3b255-location | 0.jpg | 35581 | 4 |
| PAC | image/jpeg | 1468c726-dbf2-4532-9252-b027e9fc523f-location | 0.jpg | 35581 | 5 |
| BOL | image/jpeg | 7942b5a3-93ba-493f-b2b3-cf504b179a17-location | 0.jpg | 35581 | 6 |
| CER | image/jpeg | a2f23eb0-9dab-4851-9929-31b4273f26d5-location | 0.jpg | 35581 | 7 |
| OTH | image/jpeg | 4d4f5d5b-cbe7-4751-8817-27b05e58ce9e-location | 0.jpg | 35581 | 1 |
+----------+------------+-----------------------------------------------+----------+-------------+----------+