主页 / 问题 / 1605522
Accepted
Steven Kirke
Asked:2025-01-25 05:08:28 +0800 CST2025-01-25 05:08:28 +0800 CST

如何检查 XML 中的所有节点是否具有所需的值？

  • 772

我有一个 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
sql

1 个回答

  1. Best Answer

    尝试以下基于一个表的简化解决方案。

    值得注意的一点：

    1. 当 SQL Server 将 XML 存储在 XML 数据类型中时，它会删除 XML 序言声明<?xml version="1.0" encoding="utf-8"?>。 XML 被编码为 UNICODE。
    2. XQuery 方法.exist()用于检查表记录中的 XML 中是否存在所需值。
    3. sql:variable("@variableName")允许您在 XPath/XQuery 表达式中使用来自 T-SQL 变量的动态值。
    4. [person_id/text()=sql:variable("@searchFor")]XQuery 方法中的XPath.nodes()谓词过滤掉不需要的<tutor>XML 元素。
    5. 不要忘记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 人员 ID boss类型id 是否负责
    1 74393 0x65D4E4D8749A66AD 0
    • 5

相关问题