如何使用SQL OPENXML按节点文本匹配查找XML特定子元素

可以实现，直接在XPath路径中加入节点文本匹配条件即可，完全不依赖节点索引顺序，修改后的查询如下：

exec sp_xml_preparedocument @idoc OUTPUT, @XMLData  

select *
from    openxml(@idoc,'/ArrayOfCandidate/Candidate', 1)
with (
      Candidate_Serial      nvarchar(max)   'Candidate_Serial'
    , First_Name            nvarchar(max)   'First_Name'
    , Last_Name             nvarchar(max)   'Last_Name'
    , Email                 nvarchar(max)   'Email'
    , Address_1             nvarchar(max)   'Address_1'
    , SSN                   nvarchar(max)   'Questionnaires/Questionnaire[Questionnaire_Name/text()="New Employee Information Sheet"]/Questions/QuestionObject[Question/text()="Social Security Number"]/Value'
) c;

-- 用完记得释放文档，避免内存泄漏
exec sp_xml_removedocument @idoc

逻辑说明 #

XPath中[]内的内容就是节点筛选规则：

• Questionnaire[Questionnaire_Name/text()="New Employee Information Sheet"] 表示只选取问卷名称为指定值的Questionnaire节点
• QuestionObject[Question/text()="Social Security Number"] 表示只选取问题文本为指定值的QuestionObject节点

更推荐的原生XML查询方案 #

如果你的@XMLData是XML类型变量，更建议使用SQL Server原生XML操作API，无需手动申请/释放文档句柄，性能和稳定性更好，写法如下：

SELECT
    c.value('(Candidate_Serial/text())[1]', 'nvarchar(max)') AS Candidate_Serial,
    c.value('(First_Name/text())[1]', 'nvarchar(max)') AS First_Name,
    c.value('(Last_Name/text())[1]', 'nvarchar(max)') AS Last_Name,
    c.value('(Email/text())[1]', 'nvarchar(max)') AS Email,
    c.value('(Address_1/text())[1]', 'nvarchar(max)') AS Address_1,
    c.value('(Questionnaires/Questionnaire[Questionnaire_Name/text()="New Employee Information Sheet"]/Questions/QuestionObject[Question/text()="Social Security Number"]/Value/text())[1]', 'nvarchar(max)') AS SSN
FROM @XMLData.nodes('/ArrayOfCandidate/Candidate') AS T(c)

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