SQL Server中XML列参数键值的查询与更新问题

问题描述 #

我有一个名为Rules的表，包含numeric类型的RuleID列和XML类型的XMLData列。以下是RuleID = 1时XMLData的内容：

<EP Name="C3EPIC" ReturnVar="AUTOMDWG">
    <Parameters>
        <Parameter Key="Server" Value='="Rules.MyRules.com"'
                   Description="Server"/>
        <Parameter Key="EPICLIBRARY" Value='="DEVPGM10"' Description="Library"/>
        <Parameter Key="Library" Value='="DEVPGM10"' Description=""/>
        <Parameter Key="ProgramName" Value='="MyProgram"' Description=""/>
        <Parameter Key="Function" Value="=HDWEFUNCA"
                   Description="Hardware MFG MODEL #"/>
        <Parameter Key="Trim" Value="=HDWETRIMA" Description="Hardware Trim"/>
    </Parameters>
    <OutputVariables/>
</EP>

我的两个任务：

1. 找出所有参数键EPICLIBRARY值为="DEVPGM10"的RuleID；
2. 将这些规则中的DEVPGM10修改为QAPGM10。

我尝试了以下查询XML的代码：

DECLARE @xml XML = (SELECT XMLData
                    FROM Rules 
                    WHERE ruleid = 1   
                    FOR XML AUTO, ELEMENTS, ROOT('TopLevel'))

SELECT
    epic.value('@key', 'nvarchar(255)') as AttrKey,    
    epic.value('@value', 'nvarchar(255)') as AttrVal  
FROM
    @xml.nodes('TopLevel/Rules/XMLData/EP/Parameters/Parameter') A(epic)

该代码返回6行，但所有记录的AttrKey和AttrVal均为NULL，我期望得到对应的键值列表，恳请提供指导。

解决方案 #

1. 修复查询返回NULL的问题 #

你的查询返回NULL有两个核心原因：

• 用FOR XML AUTO, ELEMENTS, ROOT('TopLevel')包装XMLData列时，会把原XML内容转为嵌套结构的文本节点，而非可解析的XML节点，导致XPath无法定位；
• XPath属性名区分大小写，你XML中用的是Key/Value（首字母大写），但查询里写的是小写@key/@value。

直接读取XMLData列即可正确查询：

SELECT
    epic.value('@Key', 'nvarchar(255)') as AttrKey,    
    epic.value('@Value', 'nvarchar(255)') as AttrVal  
FROM
    Rules
CROSS APPLY
    XMLData.nodes('/EP/Parameters/Parameter') A(epic)
WHERE
    RuleID = 1;

2. 任务1：筛选符合条件的RuleID #

使用XML的exist()方法直接过滤符合参数条件的记录：

SELECT RuleID
FROM Rules
WHERE
    XMLData.exist('/EP/Parameters/Parameter[@Key="EPICLIBRARY" and @Value=''"DEVPGM10"''']') = 1;

注：SQL中字符串内的单引号需要用双单引号转义，因此属性值里的双引号需要写成'"DEVPGM10"'。

3. 任务2：更新XML中的目标值 #

使用XML的modify()方法结合replace value of语句更新属性值：

-- 仅更新EPICLIBRARY参数
UPDATE Rules
SET XMLData.modify('
    replace value of (/EP/Parameters/Parameter[@Key="EPICLIBRARY"]/@Value)[1]
    with '"QAPGM10"'
')
WHERE
    XMLData.exist('/EP/Parameters/Parameter[@Key="EPICLIBRARY" and @Value=''"DEVPGM10"''']') = 1;

-- 同时更新EPICLIBRARY和Library参数
UPDATE Rules
SET XMLData.modify('
    replace value of (/EP/Parameters/Parameter[@Key="EPICLIBRARY"]/@Value)[1] with '"QAPGM10"';
    replace value of (/EP/Parameters/Parameter[@Key="Library"]/@Value)[1] with '"QAPGM10"'
')
WHERE
    XMLData.exist('/EP/Parameters/Parameter[@Key="EPICLIBRARY" and @Value=''"DEVPGM10"''']') = 1;

内容的提问来源于stack exchange，提问作者Rich Kenyon