Update XML metadata in SQL Server

Question

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?

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
– RDFozz, Commented May 26, 2017 at 16:41

Su Jun-Ming · Accepted Answer · 2017-05-26 10:44:49Z

1

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;

edited May 26, 2017 at 10:44

answered May 26, 2017 at 9:49

Su Jun-Ming

617 bronze badges

Add a comment |

Stack Exchange Network

Update XML metadata in SQL Server

1 Answer 1

Your Answer

Hot Network Questions

Update XML metadata in SQL Server

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions