如何使用T-SQL批量更新XML文档中指定属性的元素值

需求：将XML中所有带有Store_ID="13"属性的元素，把该属性值改为99。示例XML如下：

declare @x xml 
select @x = N'
    <Games>
      <Game>
        <Place City="LAS" State="NV" />
        <Place City="ATL" State="GA" />
        <Store Store_ID="12" Price_ID="162" Description="Doom" />
        <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
        <Store Store_ID="13" Price_ID="167" Description="Demons v3" />
        <Store Store_ID="13" Price_ID="123" Description="Whatever" />
      </Game>
    </Games>
'
select @x 

已知可通过XQuery定位目标元素：

select t.c.query('.') 
from @x.nodes('.//*[@Store_ID="13"]') as t(c)

单个节点更新可使用replace value of指定索引：

SET @x.modify('  
  replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]  
  with "99"  
');  
SELECT @x;

但直接结合sql:variable的循环会触发错误：

-- 错误实现
declare @numberOfElements int 
select @numberOfElements = count(*) 
from ( 
    select element = t.c.query('.') 
    from @x.nodes('.//*[@Store_ID="13"]') as t(c)
) x 

declare @i int = 1
while @i <= @numberOfElements begin 
    SET @x.modify('  
      replace value of (.//*[@Store_ID="13"]/@Store_ID)[sql:variable("@i")]  
      with "99"  
    ');  
    set @i = @i + 1 ;  
end 
SELECT @x;

错误信息：

Msg 2337, Level 16, State 1, Line 31
XQuery [modify()]: The target of 'replace' must be at most one node, found 'attribute(Store_ID,xdt:untypedAtomic) *'

以下是两种可行的批量更新方案：

方案一：循环更新首个匹配节点 #

无需预先统计节点数量，每次更新第一个符合条件的节点，直到无匹配项：

declare @x xml 
select @x = N'
    <Games>
      <Game>
        <Place City="LAS" State="NV" />
        <Place City="ATL" State="GA" />
        <Store Store_ID="12" Price_ID="162" Description="Doom" />
        <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
        <Store Store_ID="13" Price_ID="167" Description="Demons v3" />
        <Store Store_ID="13" Price_ID="123" Description="Whatever" />
      </Game>
    </Games>
'

while @x.exist('.//*[@Store_ID="13"]') = 1
begin
    SET @x.modify('  
      replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]  
      with "99"  
    ');  
end

SELECT @x;

此方法规避了sql:variable导致的节点集解析问题，每次仅处理单个明确节点，不会触发错误。

方案二：重新构造XML（高效批量处理） #

通过XQuery直接生成新XML，一次性替换所有目标属性，无需循环：

declare @x xml 
select @x = N'
    <Games>
      <Game>
        <Place City="LAS" State="NV" />
        <Place City="ATL" State="GA" />
        <Store Store_ID="12" Price_ID="162" Description="Doom" />
        <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
        <Store Store_ID="13" Price_ID="167" Description="Demons v3" />
        <Store Store_ID="13" Price_ID="123" Description="Whatever" />
      </Game>
    </Games>
'

set @x = @x.query('
    element Games {
        for $g in /Games/Game
        return element Game {
            for $node in $g/node()
            return
                if ($node[self::Store/@Store_ID="13"]) then
                    element Store {
                        for $attr in $node/@*
                        return
                            if ($attr/local-name()="Store_ID") then
                                attribute Store_ID {"99"}
                            else
                                $attr,
                        $node/node()
                    }
                else
                    $node
        }
    }
')

SELECT @x;

该方法通过递归遍历节点，对符合条件的<Store>元素替换属性，一次性完成所有更新，性能优于循环，适合复杂XML或大量节点场景。

表中XML列的批量更新 #

若需更新表中多行XML列，可基于上述方案调整：

方法一（游标+循环更新） #

-- 假设表为GameStores，XML列为GameData
DECLARE @ID int
DECLARE cur CURSOR FOR SELECT ID FROM GameStores WHERE GameData.exist('.//*[@Store_ID="13"]') = 1

OPEN cur
FETCH NEXT FROM cur INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE EXISTS(SELECT 1 FROM GameStores WHERE ID=@ID AND GameData.exist('.//*[@Store_ID="13"]')=1)
    BEGIN
        UPDATE GameStores
        SET GameData.modify('
            replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]
            with "99"
        ')
        WHERE ID=@ID
    END
    FETCH NEXT FROM cur INTO @ID
END

CLOSE cur
DEALLOCATE cur

方法二（直接构造XML批量更新） #

UPDATE GameStores
SET GameData = GameData.query('
    element Games {
        for $g in /Games/Game
        return element Game {
            for $node in $g/node()
            return
                if ($node[self::Store/@Store_ID="13"]) then
                    element Store {
                        for $attr in $node/@*
                        return
                            if ($attr/local-name()="Store_ID") then
                                attribute Store_ID {"99"}
                            else
                                $attr,
                        $node/node()
                    }
                else
                    $node
        }
    }
')
WHERE GameData.exist('.//*[@Store_ID="13"]') = 1

此方式无需游标，直接利用XQuery生成新XML，更新效率更高。

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