SQL Replace Typed XML Data

Question

I'm working with 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 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://learn.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!

Yitzhak Khabinsky · Accepted Answer · 2021-10-27 15:11:47Z

2

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;

edited Oct 27, 2021 at 15:11

answered Oct 27, 2021 at 15:03

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yitzhak Khabinsky Over a year ago

@Ray, glad to hear that the proposed solution is working for you. Please don't forget to mark it as answer.

Tohm · Accepted Answer · 2021-10-27 15:31:07Z

0

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

answered Oct 27, 2021 at 15:31

Tohm

3051 silver badge5 bronze badges

Collectives™ on Stack Overflow

SQL Replace Typed XML Data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related