主页 / 问题 / 1610355
Accepted
Steven Kirke
Asked:2025-04-14 20:35:57 +0000 UTC2025-04-14 20:35:57 +0000 UTC

如何从多个 XML 节点获取特定值到一个字段中

  • 772

存在一个包含两个或多个值的结构。告诉我如何将两个节点的值放入一个字段中。从具有相同 boss_type_id 的节点，在一列中输出 person_fullname --> 经理 1，经理 2

XML 表。表格示例取自用户Yitzhak Khabinsky的回答

在 SQL 中使用 XML 的示例有很多（这个问题就在那里）在 MS SQL 中使用 XML 字段的实用指南

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
sql

1 个回答

  1. Best Answer

    请尝试以下解决方案。它将从 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
    • 4

相关问题