使用存储过程批量替换XML列中Supplier的Number值的问题

你的问题出在SQL Server的modify('replace value of')语法特性上：这个语句每次只能修改单个节点，你加了[1]限定符后，只会定位到第一个匹配的Supplier/@Number节点，所以只有第一个值被更新。要批量替换所有Room节点下Supplier的Number属性，有两种可靠的解决方法：

方法一：WHILE循环逐节点更新 #

通过循环持续查找未更新的节点，直到所有匹配的属性都被替换：

DECLARE @newValue VARCHAR(10) = '456'; -- 替换后的目标值
DECLARE @targetTable NVARCHAR(128) = 'YourTableName'; -- 替换为你的表名
DECLARE @xmlColumn NVARCHAR(128) = 'Data'; -- 替换为你的XML列名

WHILE EXISTS (
    SELECT 1 
    FROM @targetTable 
    WHERE Data.exist('//Room/Supplier/@Number[. != sql:variable("@newValue")]') = 1
)
BEGIN
    UPDATE @targetTable
    SET Data.modify('replace value of (//Room/Supplier/@Number[. != sql:variable("@newValue")])[1] with sql:variable("@newValue")')
    WHERE Data.exist('//Room/Supplier/@Number[. != sql:variable("@newValue")]') = 1;
END

逻辑说明：循环条件会检查是否还有未替换的Number属性，每次更新当前行中第一个未匹配新值的节点，直到所有节点都完成替换。

方法二：拆分节点后重新构建XML（更高效） #

针对你Room数量2-5个的场景，直接拆分XML节点、重新生成带新值的XML是更高效的方案，无需循环：

DECLARE @newValue VARCHAR(10) = '456';

UPDATE t
SET Data = (
    SELECT 
        r.value('@Id', 'INT') AS '@Id',
        (
            SELECT 
                s.value('@Name', 'VARCHAR(50)') AS '@Name',
                @newValue AS '@Number'
            FROM r.nodes('Supplier') AS s(s)
            FOR XML PATH('Supplier'), TYPE
        )
    FROM t.Data.nodes('/Rooms/Room') AS r(r)
    FOR XML PATH('Room'), ROOT('Rooms'), TYPE
)
FROM YourTableName t;

逻辑说明：把原XML中的每个Room节点拆出来，重新生成Supplier节点时直接替换Number为新值，最后将所有节点重新组合成完整的XML结构，一次性完成更新。

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