如何在SQL中查询XML，替换含password的Name节点对应Value为星号

原始XML数据 #

<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>

需求描述 #

查询上述XML，找到所有包含<Name>子节点且其文本含"password"的元素，将对应<Value>节点的文本替换为星号。示例需替换3处，最终XML如下：

<UserFields>
  <UserField>
    <Name>UserName</Name>
    <Value>test_user</Value>
  </UserField>
  <UserField>
    <Name>Age</Name>
    <Value>24</Value>
  </UserField>
  <UserField>
    <Name>Password</Name>
    <Value>********</Value>
  </UserField>
  <UserField>
    <Name>AgentPassword</Name>
    <Value>********</Value>
  </UserField>
</UserFields>
<ActionAttributes>
  <Action>
   <Name>LoginPassword</Name>
   <Value>********</Value>
  </Action>
</ActionAttributes>

现有代码及问题 #

我写出的代码如下：

SET @MyDoc.modify('
  replace value of (//UserField[Name="Password"]/Value/text())[1]
  with "********"
');

但这段代码存在两个问题：

• 硬编码父元素名称（UserField），无法匹配其他类型的父元素（比如示例中的Action）
• 硬编码<Name>的取值，仅能精确匹配"Password"，无法匹配AgentPassword、LoginPassword这类变体

核心需求逻辑：任何包含<Name>和<Value>子节点的元素，只要<Name>文本含"password"（不区分大小写），就替换对应<Value>的文本为星号。

解决方案 #

SQL Server的XML modify()方法一次只能修改一个节点，因此需要结合循环处理所有匹配项，代码如下：

DECLARE @Count INT;
-- 统计需要替换的节点数量
SELECT @Count = @MyDoc.value('count(//*[contains(lower-case(Name/text()), "password")]/Value)', 'INT');

-- 循环替换每个匹配节点
WHILE @Count > 0
BEGIN
  SET @MyDoc.modify('
    replace value of (//*[contains(lower-case(Name/text()), "password")]/Value/text())[1]
    with "********"
  ');
  SET @Count = @Count - 1;
END;

代码说明 #

• //*：匹配XML中任意元素，解决硬编码父元素的问题
• contains(lower-case(Name/text()), "password")：将<Name>文本转为小写后判断是否包含"password"，实现不区分大小写的模糊匹配，覆盖所有带password的变体
• 循环处理：因为modify()一次只能修改一个节点，通过循环逐个替换所有符合条件的<Value>节点

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