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

Question

Есть структура в котором два и более значений. Подскажите как возможно получить значение из двух узлов в одно поле. Из узлов с одинаковым boss_type_id вывести person_fullname в один столбец --> Менеджер 1, Менеджер 2

Таблица XML. Пример таблицы взял в ответе пользователя Yitzhak Khabinsky

Тут много примеров работы с XML в SQL (данный вопрос там есть) Практическое руководство по работе с полями XML в MS SQL

DECLARE @tbl TABLE (id BIGINT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<career_reserve SPXML-FORM="x-local://career_reserve.xmd">
    <tutors>
        <tutor>
            <person_position_name>Руководитель</person_position_name>
            <boss_type_id>7337741311213725357</boss_type_id>
        </tutor>
        <tutor>
            <person_position_name>Менеджер 1</person_position_name>
            <boss_type_id>6243324114636993778</boss_type_id>
        </tutor>
        <tutor>
            <person_position_name>Менеджер 2</person_position_name>
            <boss_type_id>6243324114636993778</boss_type_id>
        </tutor>
    </tutors>
</career_reserve>'),
(N'<career_reserve SPXML-FORM="x-local://career_reserve.xmd">
    <tutors>
        <tutor>
            <person_position_name>Менеджер 2</person_position_name>
            <boss_type_id>6243324114636993778</boss_type_id>
        </tutor>
    </tutors>
</career_reserve>');

Запрос к таблице.

DECLARE 
     @boss_type_id BIGINT = 7337741311213725357
    ,@hrbr_type_id BIGINT = 6243324114636993778

SELECT 
         b.tutor_boss.value('(person_position_name/text())[1]', 'VARCHAR(MAX)') AS boss_fullname
        ,b.tutor_boss.value('(person_position_name/text())[1]', 'VARCHAR(MAX)') AS boss_position_name
    FROM @tbl
        CROSS APPLY xmldata.nodes('/career_reserve/tutors/tutor[boss_type_id/text()=sql:variable("@boss_type_id")]') AS b(tutor_boss)
        CROSS APPLY xmldata.nodes('/career_reserve/tutors/tutor[boss_type_id/text()=sql:variable("@hrbr_type_id")]') AS hr(tutor_hrbr)
    WHERE xmldata.exist('/career_reserve/tutors/tutor/boss_type_id[text()=sql:variable("@boss_type_id")]') = 1
        AND xmldata.exist('/career_reserve/tutors/tutor/boss_type_id[text()=sql:variable("@hrbr_type_id")]') = 1

Answer 1

Попробуйте следующее решение. Оно будет работать, начиная с SQL Server 2017.

SQL

-- DDL и образец вставки данных, начало
DECLARE @tbl TABLE (id BIGINT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<career_reserve SPXML-FORM="x-local://career_reserve.xmd">
    <tutors>
        <!-- Это поле отдельное. -->
        <tutor>
            <person_fullname>Руководитель</person_fullname>
            <boss_type_id>7337741311213725357</boss_type_id>
        </tutor>
        <!-- Эти два узла надо скомпоновать и вывести в одно поле Менеджер 1 Менеджер 2. -->
        <tutor>
            <person_fullname>Менеджер 1</person_fullname>
            <boss_type_id>6243324114636993778</boss_type_id>
        </tutor>
        <tutor>
            <person_fullname>Менеджер 2</person_fullname>
            <boss_type_id>6243324114636993778</boss_type_id>
        </tutor>
    </tutors>
</career_reserve>');
-- DDL и образец вставки данных, конец

DECLARE @boss_type_id BIGINT = 6243324114636993778;

-- Method #1
-- основано на STRING_AGG()
;WITH rs AS
(
    SELECT b.tutor_boss.value('(person_fullname/text())[1]', 'NVARCHAR(256)') AS boss_fullname
    FROM @tbl
    CROSS APPLY xmldata.nodes('/career_reserve/tutors/tutor[boss_type_id/text()=sql:variable("@boss_type_id")]') AS b(tutor_boss)
)
SELECT STRING_AGG(boss_fullname, ', ') AS boss_fullname_List
FROM rs;

-- Method #2
-- основано на XQuery
SELECT TRIM(TRAILING ',' FROM tutor_boss.query('
        for $i in tutor[boss_type_id/text()=sql:variable("@boss_type_id")]/person_fullname/text()
        return concat($i, ",")
      ').value('text()[1]', 'NVARCHAR(4000)')) AS boss_fullname_List
FROM @tbl
CROSS APPLY xmldata.nodes('/career_reserve/tutors') AS b(tutor_boss);

Результат

boss_fullname_List
Менеджер 1, Менеджер 2

БЛОГ НА HUSL

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

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