sql - 如何从具有相同名称的多个 XML 节点返回行?
问题描述
以下 XML 在节点“attachDocument”下有多个节点。下面的 SQL 查询只返回来自“attachDocument”的第一个节点的行,而不是第二个节点......
有人可以帮助从“attachDocument”的两个节点返回行。
提前致谢...
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)
解决方案
请尝试以下解决方案。
值得注意的笔记:
- 默认命名空间的正确声明可以全面简化 XPath 表达式。默认命名空间不需要前缀。
- 最好不要
//
在 XPath 表达式中使用。它会导致遍历整个 XML。完全合格的路径对性能更好。 - 将 text() 添加到 XML 元素的 XPath 表达式可以提高性能。
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);
输出
+----------+-----------------+--------------------------------------+-----------------------+----------------+-----------------------+---------------------------------------------+
| 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 |
+----------+-----------------+--------------------------------------+-----------------------+----------------+-----------------------+---------------------------------------------+