正如许多人已经提到的，SQL Server 不支持一次性更新多元素 XML。

请尝试以下基于 SQL Server 的 XQuery 及其 FLWOR 表达式的解决方案。

SQL＃1

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl (xml_data) VALUES
(N'<UserFields>
    <UserField>
        <Name>UserName</Name>
        <Value>test_user</Value>
    </UserField>
    <UserField>
        <Name>Age</Name>
        <Value>24</Value>
    </UserField>
    <UserField>
        <Name>Password</Name>
        <Value>test1234!</Value>
    </UserField>
    <UserField>
        <Name>AgentPassword</Name>
        <Value>1234Test!</Value>
    </UserField>
</UserFields>');
-- DDL and sample data population, end

-- just to see
SELECT * 
    , xml_data.query('<UserFields>{
        for $x in /UserFields/*
        return element UserField {
            if (contains(($x/Name/text())[1],"Password")) then
                ($x/*[local-name() != "Value"], element Value {"********"})
            else $x/*
        }
    }</UserFields>') AS modified_xml
FROM @tbl;

-- real deal
UPDATE @tbl
SET xml_data = xml_data.query('<UserFields>{
    for $x in /UserFields/*
    return element UserField {
        if (contains(($x/Name/text())[1],"Password")) then
            ($x/*[local-name() != "Value"], element Value {"********"})
        else $x/*
    }
}</UserFields>');

-- test
SELECT * FROM @tbl;

SQL＃2

对于问题中改变的 XML，我们可以使用不同的方法。

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl (xml_data) VALUES
(N'<UserFields>
  <UserField>
    <Name>UserName</Name>
    <Value>test_user</Value>
  </UserField>
  <UserField>
    <Name>Age</Name>
    <Value>24</Value>
  </UserField>
  <UserField>
    <Name>Password</Name>
    <Value>test1234!</Value>
  </UserField>
  <UserField>
    <Name>AgentPassword</Name>
    <Value>1234Test!</Value>
  </UserField>
</UserFields>
<ActionAttributes>
  <Action>
   <Name>LoginPassword</Name>
   <Value>Test123!</Value>
  </Action>
</ActionAttributes>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @ReplaceWith VARCHAR(10) = '*********';
DECLARE @UPDATE_STATUS BIT = 1;

WHILE @UPDATE_STATUS > 0 
BEGIN
    UPDATE t
    SET xml_data.modify('replace value of 
        (/*/*[contains((Name/text())[1], "Password") and Value/text()!=sql:variable("@ReplaceWith")]/Value/text())[1] 
        with sql:variable("@ReplaceWith")')
    FROM @tbl AS t
    WHERE xml_data.exist('/*/*[contains((Name/text())[1], "Password") and Value/text()!=sql:variable("@ReplaceWith")]')=1;
        
    SET @UPDATE_STATUS = @@ROWCOUNT;
    PRINT @UPDATE_STATUS;
END;

-- after
SELECT * FROM @tbl;

SQL＃3

这个是针对 XML 数据类型的 SQL Server 变量/参数。

DECLARE @xml_data XML =
N'<Event>
    <CaseNumber>PD200731245151</CaseNumber>
    <City>
        <CaseNumber>PD200731245770</CaseNumber>
    </City>
</Event>';

DECLARE @ReplaceWith VARCHAR(10) = '*********';

WHILE (@xml_data.exist('//*[contains((./text())[1], "PD")]') = 1)
BEGIN 
    SET @xml_data.modify('replace value of 
            (//*[contains((./text())[1], "PD")]/text())[1] 
            with sql:variable("@ReplaceWith")');
END;

-- test
SELECT @xml_data;