Textual find and replace within an XML column

Question

Hi All,

I'm trying to find and replace all instances of 'this phrase' with 'that phrase' in an xml type column using T-SQL.

The xml column contains fields that are more than 8000 characters long.

I tried using the Replace function but it doesn't like the XML data type, and the data can't be squeezed into a varchar.

Phil

Answer 1

Whatever size it is, it can be put in a varchar(max) (a datatype new with 2005), which supports all the text-manipulation functions.

Answer 2

I've never done it myself, but it looks like this article should help. It looks like you'll do an Update statement like:

UPDATE [Table]
SET Document.Modify('[namespace declaration];
    Replace Value Of [Xpath expression] with [New Value]')

But I would definately read that article carefully- just skimming it, I may have missed something.