SQL解析XML时如何获取多同名attachDocument节点的全部数据行

问题根源 #

原有查询存在三个核心逻辑缺陷：

• 仅通过@sqlxml.nodes('//ur:response')把最外层根节点映射为唯一数据集行，整个XML结构只会生成1行结果
• 取值时固定匹配(//ur:attachDocument/xxx)[1]，只抓取路径下第一个出现的附件节点值，后续同名的attachDocument节点会被完全忽略
• 部分公共字段的XPath路径写错（比如查找response下嵌套的子response节点），实际无法取到对应值

修正思路 #

• 调整nodes()函数的定位路径，让每一个独立的attachDocument节点映射为数据集的一行，有多少个同名附件节点就生成多少行XML上下文
• 附件专属字段（分类、MIME类型、URI、文件名）直接从当前行对应的单个attachDocument节点下取值，无需跨层级查找
• 所有公共类字段（响应机构、签发时间、备注信息）从当前attachDocument的上层父级response节点取值，保证每一行都能携带完整的公共信息

修正后可直接运行的SQL代码 #

;WITH XMLNAMESPACES ('http://xyz.in/twa/cmm/decl/v2' AS ur)
SELECT
-- 取当前行对应的附件专属字段
attachDoc.value('(ur:category)[1]','varchar(50)') as Category,
attachDoc.value('(ur:mimeCode)[1]','varchar(50)') as MimeCode,
attachDoc.value('(ur:URI)[1]','varchar(50)') as URI,
attachDoc.value('(ur:filename)[1]','varchar(50)') as [FileName],
-- 从上层父级response节点取公共字段
attachDoc.value('(../ur:status/ur:agency)[1]','varchar(100)') as ResponseAgency,
attachDoc.value('(../ur:issueDate)[1]','varchar(100)') as ResponseIssueDateTime,
attachDoc.value('(../ur:additionalInformationICN/ur:text)[1]','varchar(255)') as ResponseClearanceInstructions
FROM @sqlxml.nodes('/ur:response/ur:attachDocument') AS T(attachDoc)

执行效果 #

返回结果会和附件数量完全匹配：

• 第一行对应category为AAA的PDF附件全量信息
• 第二行对应category为AAB的文本附件全量信息
• 两行都会自动携带相同的响应机构、签发时间、备注类公共字段，不会出现仅返回第一个附件的问题

内容的提问来源于stack exchange，提问作者SQLHoncho