How to return rows from multiple XML nodes with same name?

Question

The following XML have multiple nodes under node "attachDocument". Below SQL query only return rows from first node of "attachDocument" NOT the 2nd one which is...

Can someone help with to return rows from both nodes of "attachDocument".

Thanks in advance...

XML...

set @sqlxml = N'<response xmlns="http://xyz.in/twa/cmm/decl/v2">
<identification>88762431</identification>
<type>RESPONSE</type>
<submitter><identifier>40134916C</identifier></submitter>
<functionalReference>TSW07389555IM1</functionalReference>
<transactionType>24</transactionType>
<attachDocument>
    <category>AAA</category>
    <mimeCode>application/pdf</mimeCode>
    <URI>16f15574-5d5a-4e83-b9ac-2151f10cf2eb</URI>
    <filename>XYZ_B2021_199.pdf</filename>
</attachDocument>
<attachDocument>
    <category>AAB</category>
    <mimeCode>text/plain</mimeCode>
    <URI>1511b476-a2be-4ae5-a54c-0a5dc14759b2</URI>
    <filename>XYZ_B2021_199_xml.txt</filename>
</attachDocument>
<additionalInformationICN><text>Please refer to attached XYZ for Directions</text></additionalInformationICN>
<issueDate>20210331113355</issueDate>
<overallDeclaration>
    <identification>88762431</identification>
    <functionalReference>TSW07389555IM1</functionalReference>
    <submitter>
        <identifier>40134916C</identifier>
    </submitter>
    <responsibleGovernmentAgency>XYZ</responsibleGovernmentAgency>
</overallDeclaration>
<status>
    <agency>XYZ</agency>
    <effectiveDate>20210331113355</effectiveDate>
    <name>B04</name>
    <releaseDate>20210331113355</releaseDate>
</status>
</response>'


SQL Query...
;WITH XMLNAMESPACES ('http://xyz.in/twa/cmm/decl/v2' AS ur)
SELECT
response.value('(//ur:attachDocument/ur:category)[1]','varchar(50)') as Category,
response.value('(//ur:attachDocument/ur:mimeCode)[1]','varchar(50)') as MimeCode,
response.value('(//ur:attachDocument/ur:URI)[1]','varchar(50)') as URI,
response.value('(//ur:attachDocument/ur:filename)[1]','varchar(50)') as [FileName],
response.value('(//ur:status/ur:agency)[1]','varchar(100)') as ResponseAgency,
response.value('(//ur:response/ur:issueDate)[1]','varchar(100)') as ResponseIssueDateTime,
response.value('(//ur:response/ur:additionalInformationICN)[1]','varchar(100)') as ResponseClearanceInstructions
FROM @sqlxml.nodes('//ur:response') AS T(response)

Answer 1

Please try the following solution.

Notable notes:

• A proper declaration of the default namespace simplifies XPath expressions across the board. A default namespace doesn't require a prefix.
• It is better not to use // in the XPath expressions. It causes to traverse the entire XML. A fully qualified path is better for performance.
• Adding text() to the XPath expressions for XML elements improves performance.

SQL

DECLARE @sqlxml XML = 
N'<response xmlns="http://xyz.in/twa/cmm/decl/v2">
    <identification>88762431</identification>
    <type>RESPONSE</type>
    <submitter>
        <identifier>40134916C</identifier>
    </submitter>
    <functionalReference>TSW07389555IM1</functionalReference>
    <transactionType>24</transactionType>
    <attachDocument>
        <category>AAA</category>
        <mimeCode>application/pdf</mimeCode>
        <URI>16f15574-5d5a-4e83-b9ac-2151f10cf2eb</URI>
        <filename>XYZ_B2021_199.pdf</filename>
    </attachDocument>
    <attachDocument>
        <category>AAB</category>
        <mimeCode>text/plain</mimeCode>
        <URI>1511b476-a2be-4ae5-a54c-0a5dc14759b2</URI>
        <filename>XYZ_B2021_199_xml.txt</filename>
    </attachDocument>
    <additionalInformationICN>
        <text>Please refer to attached XYZ for Directions</text>
    </additionalInformationICN>
    <issueDate>20210331113355</issueDate>
    <overallDeclaration>
        <identification>88762431</identification>
        <functionalReference>TSW07389555IM1</functionalReference>
        <submitter>
            <identifier>40134916C</identifier>
        </submitter>
        <responsibleGovernmentAgency>XYZ</responsibleGovernmentAgency>
    </overallDeclaration>
    <status>
        <agency>XYZ</agency>
        <effectiveDate>20210331113355</effectiveDate>
        <name>B04</name>
        <releaseDate>20210331113355</releaseDate>
    </status>
</response>'


;WITH XMLNAMESPACES (DEFAULT 'http://xyz.in/twa/cmm/decl/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('(/response/status/agency/text())[1]','varchar(100)') as ResponseAgency,
    response.value('(/response/issueDate/text())[1]','varchar(100)') as ResponseIssueDateTime,
    response.value('(/response/additionalInformationICN/text/text())[1]','varchar(100)') as ResponseClearanceInstructions
FROM @sqlxml.nodes('/response/attachDocument') AS T(response);

Output

+----------+-----------------+--------------------------------------+-----------------------+----------------+-----------------------+---------------------------------------------+
| Category |    MimeCode     |                 URI                  |       FileName        | ResponseAgency | ResponseIssueDateTime |        ResponseClearanceInstructions        |
+----------+-----------------+--------------------------------------+-----------------------+----------------+-----------------------+---------------------------------------------+
| AAA      | application/pdf | 16f15574-5d5a-4e83-b9ac-2151f10cf2eb | XYZ_B2021_199.pdf     | XYZ            |        20210331113355 | Please refer to attached XYZ for Directions |
| AAB      | text/plain      | 1511b476-a2be-4ae5-a54c-0a5dc14759b2 | XYZ_B2021_199_xml.txt | XYZ            |        20210331113355 | Please refer to attached XYZ for Directions |
+----------+-----------------+--------------------------------------+-----------------------+----------------+-----------------------+---------------------------------------------+