Блог

Главная - Вопросы по программированию - Поиск Определенного Дочернего Элемента С Помощью SQL OPENXML

Поиск Определенного Дочернего Элемента С Помощью SQL OPENXML

#sql #xml #openxml

Вопрос:

Учитывая этот 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>phillip_fry@yahoo.com</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

Комментарии:

1. Лучше использовать методы XQuery .nodes() и .value() вместо проприетарного OPENXML.

2. @Ицхакхабинский Я стараюсь избегать XQuery, так как он намного медленнее для запросов, в которых требуется обработать большое количество элементов, как в данном случае.

Ответ №1:

Пожалуйста, попробуйте следующее решение.

Он найдет SSN независимо от своего последовательного положения.

Начиная с SQL Server 2005 и далее, при работе с типом данных XML лучше использовать язык XQuery, основанный на стандартах w3c.

Собственность корпорации Майкрософт OPENXML и ее компаньонов sp_xml_preparedocument и sp_xml_removedocument хранятся только для обратной совместимости с устаревшим SQL Server 2000. Их использование ограничивается лишь несколькими незначительными случаями. Настоятельно рекомендуется переписать свой SQL и переключить его на XQuery.

SQL

 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>phillip_fry@yahoo.com</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       | phillip_fry@yahoo.com | 123 Main St | 414-62-7741 |
 ------------------ ------------ ----------- ----------------------- ------------- -------------
Метки: