Как проверить все узлы в XML на необходимые значение?

Question

У меня есть XML структура, в которой у необходимого узла есть несколько значений. Вопрос, как проверить каждый узел на необходимое условие? Я не силен в SQL, а путной информации, как работать с нодами не нашел. Например, мне нужен в выборке сотрудник, у которого person_id = 74393. Если я правильно понял [1] это вроде индекса в массиве?

Пример XML:

<?xml version="1.0" encoding="utf-8"?>
    <career_reserve SPXML-FORM="x-local://career_reserve.xmd"></career_reserve>
    <tutors>
            <tutor>
                <person_id>74393</person_id>
                <boss_type_id>0x65D4E4D8749A66AD</boss_type_id>
                <is_responsible>0</is_responsible>
            </tutor>
            <tutor>
                <person_id>11111</person_id>
                <boss_type_id>0x56A4BE34269174F2</boss_type_id>
                <is_responsible>0</is_responsible>
            </tutor>
            <tutor>
                <person_id>22222</person_id>
                <boss_type_id>0x56A4BE34269174F2</boss_type_id>
                <is_responsible>0</is_responsible>
            </tutor>
    </tutors>
</career_reserve>

Пример запроса:

SELECT 
        crs.id,
        tutor.value('(/career_reserve/tutors/tutor/person_fullname)[1]','varchar(max)') as hrbr 
    FROM career_reserves AS crs 
    INNER JOIN career_reserve as cr on crs.id = cr.id
    CROSS APPLY cr.data.nodes('/career_reserve/tutors/tutor') AS q(tutor)
    WHERE crs.code LIKE '%IS-%'
    AND tutor.value('(/career_reserve/tutors/tutor/person_id)[1]','BIGINT') = 74393

Answer 1

Попробуйте следующее упрощенное решение на основе одной таблицы.

Примечательные моменты:

Когда SQL Server сохраняет XML в типе данных XML, он удаляет декларацию XML-пролога <?xml version="1.0" encoding="utf-8"?>. XML кодируется как UNICODE.
Метод XQuery .exist() используется для проверки наличия требуемого значения в XML в записях таблицы.
sql:variable("@variableName") позволяет использовать динамические значения из T-SQL переменной в выражениях XPath/XQuery.
Предикат XPath [person_id/text()=sql:variable("@searchFor")] в методе XQuery .nodes() отфильтровывает ненужные <tutor> XML элементы.
Не забудьте использовать text() в XPath выражениях для повышения производительности. Обеспечивает увеличение скорости на 40%-50%.

SQL

-- DDL и образец вставки данных, начало
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<career_reserve SPXML-FORM="x-local://career_reserve.xmd">
    <tutors>
        <tutor>
            <person_id>74393</person_id>
            <boss_type_id>0x65D4E4D8749A66AD</boss_type_id>
            <is_responsible>0</is_responsible>
        </tutor>
        <tutor>
            <person_id>11111</person_id>
            <boss_type_id>0x56A4BE34269174F2</boss_type_id>
            <is_responsible>0</is_responsible>
        </tutor>
        <tutor>
            <person_id>22222</person_id>
            <boss_type_id>0x56A4BE34269174F2</boss_type_id>
            <is_responsible>0</is_responsible>
        </tutor>
    </tutors>
</career_reserve>'),
(N'<career_reserve SPXML-FORM="x-local://career_reserve.xmd">
    <tutors>
        <tutor>
            <person_id>74770</person_id>
            <boss_type_id>0x65D4E4D8749A66AD</boss_type_id>
            <is_responsible>1</is_responsible>
        </tutor>
    </tutors>
</career_reserve>');
-- DDL и образец вставки данных, конец

DECLARE @searchFor INT = 74393;

SELECT id   
    , tutor.value('(person_id/text())[1]', 'INT') AS person_id
    , tutor.value('(boss_type_id/text())[1]', 'VARCHAR(30)') AS boss_type_id
    , tutor.value('(is_responsible/text())[1]', 'BIT') AS is_responsible
FROM @tbl
CROSS APPLY xmldata.nodes('/career_reserve/tutors/tutor[person_id/text()=sql:variable("@searchFor")]') AS t(tutor)
WHERE xmldata.exist('/career_reserve/tutors/tutor/person_id[text()=sql:variable("@searchFor")]') = 1;

Результат

id	person_id	boss_type_id	is_responsible
1	74393	0x65D4E4D8749A66AD	0

БЛОГ НА HUSL

Как проверить все узлы в XML на необходимые значение?

Ответы (1 шт):