How to rename XML node name in a SQL Server

Question

I have on my database a table with one column storing XML data. Due to changes in the source code we want to rename one specific XML node name and XML namespace. Lets say that I have the XML bellow:

<MediaClass xmlns="MediaClass/1">   
    <Media>
        <Title>Test</Title>
        <Type>Book</Type>
        <Price>1.00</Price>
    </Media>
</MediaClass>

How can I rename the Node MediaClass name to let's say Book and also change the namespace value?

So it can look like as:

<Book xmlns="Book/1">   
    <Media>
        <Title>Test</Title>
        <Type>Book</Type>
        <Price>1.00</Price>
    </Media>
</Book>

I need to do it entirely in T-SQL as this will be used as a migration script. The minimum SQL Server installed on ours customers is SQL Server 2005.

Mikael Eriksson · Accepted Answer · 2011-09-06 09:12:03Z

6

I don't know if this is possible with XML DML

It might work for you to use replace instead.

update YourTable set
  XMLCol = replace(replace(cast(XMLCol as nvarchar(max)), 
                           '<MediaClass xmlns="MediaClass/1">', 
                           '<Book xmlns="Book/1">'), 
                   '</MediaClass>', 
                   '</Book>')

answered Sep 6, 2011 at 9:12

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

4 Comments

Andres Over a year ago

Yeah, that was my first approach indeed, but because my XML are bigger than 8000 characters, I have to use more complicated solution (sqlteam.com/article/search-and-replace-in-a-text-column) which I want to avoid.

Mikael Eriksson Over a year ago

@Andres - That is not a problem when you cast to nvarchar(max).

Andres Over a year ago

Yeah, after trying and guessing I think this is the most possible and feasible approach now. Thanks

Leniel Maccaferri Over a year ago

Amazing... this helped a lot! :)

Collectives™ on Stack Overflow

How to rename XML node name in a SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related