使用SQL OPENXML查找特定的子元素

Question

给定此XML:

<ArrayOfCandidate>
<Candidate>
<Candidate_Serial>a4wwj48pypxg</Candidate_Serial>
<Job_Serial>a2wwj48c92qp</Job_Serial>
<Job_Name>Janitor</Job_Name>
<First_Name>Phillip</First_Name>
<Last_Name>Fry</Last_Name>
<Email>[e-mail]</Email>
<Address_1>123 Main St</Address_1>
<Questionnaires>
<Questionnaire>
<Questionnaire_Name>Questionnaire 1</Questionnaire_Name>
<Questionnaire_Serial>a7wwj48rbcwx</Questionnaire_Serial>
<Submit_Date>04/29/2020 10:55 AM</Submit_Date>
<Submit_Date_Timestamp>1588175742</Submit_Date_Timestamp>
<Questions>
<QuestionObject>
<Question>Salary Requirements</Question>
<Value>36.00 per hour</Value>
</QuestionObject>
<QuestionObject>
<Question>Are you eligible to work in the US?</Question>
<Value>Yes</Value>
</QuestionObject>
</Questions>
</Questionnaire>
<Questionnaire>
<Questionnaire_Name>New Employee Information Sheet</Questionnaire_Name>
<Questionnaire_Serial>a7wwj488ril8</Questionnaire_Serial>
<Submit_Date>05/18/2020 11:52 AM</Submit_Date>
<Submit_Date_Timestamp>1589820723</Submit_Date_Timestamp>
<Questions>
<QuestionObject>
<Question>Zip Code</Question>
<Value>86327</Value>
</QuestionObject>
<QuestionObject>
<Question>Phone Number</Question>
<Value>252-915-1623</Value>
</QuestionObject>
<QuestionObject>
<Question>Social Security Number</Question>
<Value>414-62-7741</Value>
</QuestionObject>
</Questions>
</Questionnaire>
</Questionnaires>
</Candidate>
</ArrayOfCandidate>

我已经能够使用<Questionnaire>和<QuestionObject>的索引来找到值，但我不能保证索引总是相同的。

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[2]/Questions/QuestionObject[3]/Value'
) c;

是否可以将<Questionnaire_Name>元素上的查询匹配重写为New Employee Information Sheet的文本，将<Question>元素上的查询匹配重写为Social Security Number的文本

Answer 1

请尝试以下解决方案。

无论SSN的顺序如何，它都会找到SSN。

从SQL Server 2005开始，最好使用基于w3c标准的XQuery语言，同时处理XML数据类型。

Microsoft专有的OPENXML及其同伴sp_xml_preparedocument和sp_xml_removedocument被保留只是为了向后兼容过时的SQL2000服务器。他们的使用减少了，只有极少数的边缘案件。强烈建议您重新编写SQL并将其切换为XQuery。

/p>

DECLARE @xml XML =
N'<ArrayOfCandidate>
<Candidate>
<Candidate_Serial>a4wwj48pypxg</Candidate_Serial>
<Job_Serial>a2wwj48c92qp</Job_Serial>
<Job_Name>Janitor</Job_Name>
<First_Name>Phillip</First_Name>
<Last_Name>Fry</Last_Name>
<Email>[e-mail]</Email>
<Address_1>123 Main St</Address_1>
<Questionnaires>
<Questionnaire>
<Questionnaire_Name>Questionnaire 1</Questionnaire_Name>
<Questionnaire_Serial>a7wwj48rbcwx</Questionnaire_Serial>
<Submit_Date>04/29/2020 10:55 AM</Submit_Date>
<Submit_Date_Timestamp>1588175742</Submit_Date_Timestamp>
<Questions>
<QuestionObject>
<Question>Salary Requirements</Question>
<Value>36.00 per hour</Value>
</QuestionObject>
<QuestionObject>
<Question>Are you eligible to work in the US?</Question>
<Value>Yes</Value>
</QuestionObject>
</Questions>
</Questionnaire>
<Questionnaire>
<Questionnaire_Name>New Employee Information Sheet</Questionnaire_Name>
<Questionnaire_Serial>a7wwj488ril8</Questionnaire_Serial>
<Submit_Date>05/18/2020 11:52 AM</Submit_Date>
<Submit_Date_Timestamp>1589820723</Submit_Date_Timestamp>
<Questions>
<QuestionObject>
<Question>Zip Code</Question>
<Value>86327</Value>
</QuestionObject>
<QuestionObject>
<Question>Phone Number</Question>
<Value>252-915-1623</Value>
</QuestionObject>
<QuestionObject>
<Question>Social Security Number</Question>
<Value>414-62-7741</Value>
</QuestionObject>
</Questions>
</Questionnaire>
</Questionnaires>
</Candidate>
</ArrayOfCandidate>';
SELECT c.value('(Candidate_Serial/text())[1]', 'varchar(50)') as Candidate_Serial
, c.value('(First_Name/text())[1]', 'varchar(50)') as First_Name
, c.value('(Last_Name/text())[1]', 'varchar(50)') as Last_Name
, c.value('(Email/text())[1]', 'varchar(500)') as Email
, c.value('(Address_1/text())[1]', 'varchar(500)') as Address_1
, c.value('(Questionnaires/Questionnaire/Questions/QuestionObject[Question/text()="Social Security Number"]/Value/text())[1]', 'CHAR(11)') as SSN
FROM @xml.nodes('/ArrayOfCandidate/Candidate') AS t(c);

+------------------+------------+-----------+-----------------------+-------------+-------------+
| Candidate_Serial | First_Name | Last_Name |         Email         |  Address_1  |     SSN     |
+------------------+------------+-----------+-----------------------+-------------+-------------+
| a4wwj48pypxg     | Phillip    | Fry       | [e-mail] | 123 Main St | 414-62-7741 |
+------------------+------------+-----------+-----------------------+-------------+-------------+