Sql-server – Update XML metadata in SQL Server

sql serversql-server-2012xmlxquery

I'm trying to change the value in the XML metadata using SQL Server.
The XML file looks like this:

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MyOldValue">
<SomeTag></SomeTag>

What I want to do is to change xmlns to a new value. I tried casting the xml as nvarchar(max), using replace and then casting back to xml but it didn't work (string truncation).

I tried using XQuery but I kept failing. Can you recommend a solution?

Best Answer

I try this queries is ok.

DECLARE @xml XML='<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MyOldValue"><SomeTag></SomeTag></Root>';

SELECT @xml = CAST( REPLACE(CAST(@xml AS NVARCHAR(MAX)),'MyOldValue','MyNewValue') AS XML);

Select @xml;
Related Question