XPath 表达式 必须定义单个节点

表达式1

标识要更新其值的节点。它必须仅标识单个节点。也就是说，Expression1 必须是静态单例。如果 XML 是类型化的，则节点的类型必须是简单类型。当选择多个节点时，会引发错误。

使用 SQL 变量可以避免对表达式中的谓词进行硬编码。

DECLARE @Name VARCHAR(50) = 'Password';
DECLARE @NewValue VARCHAR(50) = '********';
SET @MyDoc.modify('
replace value of (//UserField[Name = sql:variable("@Name")]/Value/text())[1]
with sql:variable("@NewValue")
');

迭代可能的值

DECLARE @myDoc XML;
DECLARE @Name VARCHAR(50);
DECLARE @parent VARCHAR(50);
DECLARE @NewValue VARCHAR(50) = '********';
DECLARE @rowid int;

DECLARE @tnames TABLE(
rowid iNT,
names VARCHAR(50),
parent VARCHAR(50)
);

SET @myDoc = '<root>
<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>
</root>
';

--SELECT @myDoc;

INSERT INTO @tnames (rowid, names, parent)
SELECT ROW_NUMBER() OVER (ORDER BY t.xname) AS ROWID, T.xname.value('(Name/text())[1]', 'varchar(max)') as names, T.xname.value('(local-name(.))[1]', 'varchar(max)') as parent
FROM @myDoc.nodes('//*[Name[contains(., "Password")]]') T(xname); 

SELECT * FROM @tnames;

WHILE (SELECT COUNT(1) FROM @tnames) > 0
BEGIN
    SELECT @rowid = rowid, @Name = names, @parent= parent FROM @tnames;
    
    SET @MyDoc.modify('
    replace value of (//*[local-name() = sql:variable("@parent")][Name = sql:variable("@Name")]/Value/text())[1]
    with sql:variable("@NewValue")
    ');
    
    DELETE FROM @tnames where rowid = @rowid;

    --SELECT ROW_NUMBER() OVER (ORDER BY t.xname) AS ROWID, T.xname.value('(Name/text())[1]', 'varchar(max)') as names, T.xname.value('(Value/text())[1]', 'varchar(max)') as value 
    --FROM @myDoc.nodes('//*[local-name() = sql:variable("@parent")][Name = sql:variable("@Name")]') T(xname);
    
END

此外，请对我向微软的建议投赞成票：feedback.azure.com/d365community/idea/... 并且不要错过我的多条评论。

是的，通过切换到以下 XPath 表达式来实现：(//*[contains... 在两个位置。

有什么方法可以告诉它检查任意级别吗？目前，(/*/*[contains... 语法强制它开始查找两级深度。偶尔，我发现 XML 可能深度为 3 级或 1 级。

SQL #2 完成了任务！非常感谢。不管怎样，我稍微修改了代码，以便能够替换值而不管 \'password\' 的大小写。...[contains(upper-case(Name/text())[1]), \'PASSWORD\') ...

正如许多人已经提到的，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;

@siggemannen —— 将其困在一个地方比尝试梳理过去 15 年来拼凑起来的系统并找到可能插入的所有可能的调用组合更容易。:-)

不要以为 sql server xml 修改方法可以替换多个节点，你可能需要手动解析并重新创建 xml 并替换字段。像往常一样，这个问题攻克了错误的问题，为什么要操作 xml，而你可以处理生成此类 xml 的过程