SQL Replace Typed XML Data-CodePudding

I'm working with a third party software that stores an XML document of parameters as a column. I'm trying to write a SQL-Server script that will replace the the email address in the XML below.

<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
  <KeyValueOfstringanyType> 
    <Key>Email</Key>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">[email protected]</Value>
</KeyValueOfstringanyType>
</ArrayOfKeyValueOfstringanyType>

So far, the closest I've gotten is this... It runs and says rows were affected but does nothing.

update t 
set XMLColumn.modify('replace value of (/ArrayOfKeyValueOfstringanyType/KeyValueOfstringanyType/Key/Value/string())[1] with "[email protected]"')

After reviewing other posts and Microsoft's documentation (https://docs.microsoft.com/en-us/sql/t-sql/xml/replace-value-of-xml-dml?view=sql-server-ver15#a-replacing-values-in-an-xml-instance --Item D), it seems I'm missing something regarding the namespaces. If I understand the XML correctly, it appears that there are multiple namespaces to declare. After several attempts with no luck, my lack of XML experience has me turning here.

Any help is greatly appreciated!

CodePudding user response：

Please try the following solution.

As you correctly guessed, the culprit was a default namespace.

Also, I had to adjust the XPath expression.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XMLColumn XML);
INSERT INTO @tbl (XMLColumn) VALUES
(N'<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
                                xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <KeyValueOfstringanyType>
        <Key>Email</Key>
        <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema"
               i:type="d3p1:string">[email protected]</Value>
    </KeyValueOfstringanyType>
</ArrayOfKeyValueOfstringanyType>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/2003/10/Serialization/Arrays')
UPDATE @tbl 
SET XMLColumn.modify('replace value of (/ArrayOfKeyValueOfstringanyType/KeyValueOfstringanyType/Value/text())[1] with "[email protected]"');

-- after
SELECT * FROM @tbl;

CodePudding user response：

You muse declare default namespace

DECLARE @XML XML = N'<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
  <KeyValueOfstringanyType> 
    <Key>Email</Key>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">[email protected]</Value>
</KeyValueOfstringanyType>
</ArrayOfKeyValueOfstringanyType> '

set @XML.modify('
declare default element namespace "http://schemas.microsoft.com/2003/10/Serialization/Arrays"; 
replace value of (/ArrayOfKeyValueOfstringanyType/KeyValueOfstringanyType/Value/text())[1] with "[email protected]"')

SELECT @XML