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?