Replacing XML entities or special characters

[lawlerpat]

I am looking for a way to replace the XML entities from varchar type data in a query.
For example if my query returns 'Bob&Mary' I want that to be replaced with 'Bob&Mary'.
My problem is what if the db field already has the replacement in it. I don't want to replace the & that is part of the special element. Or I might end up with 'Bob&Mary' or worse.

Any suggestions?

Thanks

 

[psprague]

Put it into a CDATA tag. No encoding happens inside.