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">

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?

  • What happens if you CAST the xml as nvarchar(max) and then back to xml without making any changes. Does this also give you a string truncation error? Also: is the SML file already stored in SQL, or are you loading it to do the conversion? and: where are you running the commands that give you the error" SSMS? an SSIS package? or somewhere else? – RDFozz May 26 '17 at 16:41

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.