Как запросить XML в SQL, чтобы найти элемент по частичному совпадению text()

Выражение 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

Как многие уже упоминали, SQL Server не поддерживает обновление нескольких элементов XML за один раз.

Попробуйте следующее решение, основанное на XQuery SQL Server и его выражении 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;