How do I search a NTEXT column for XML attributes and update the values? MS SQL 2005

Question

Duplicate: this exact question was asked by the same author in http://stackoverflow.com/questions/1221583/how-do-i-update-a-xml-string-in-an-ntext-column-in-sql-server. Please close this one and answer in the original question.

---

I have a SQL table with 2 columns. ID(int) and Value(ntext)

The value rows have all sorts of xml strings in them.

ID   Value
--   ------------------
1    <ROOT><Type current="TypeA"/></ROOT>
2    <XML><Name current="MyName"/><XML>
3    <TYPE><Colour current="Yellow"/><TYPE>
4    <TYPE><Colour current="Yellow" Size="Large"/><TYPE>
5    <TYPE><Colour current="Blue" Size="Large"/><TYPE>
6    <XML><Name current="Yellow"/><XML>

How do I:

   A. List the rows where <TYPE><Colour current="Yellow",
    bearing in mind that there is an entry
    <XML><Name current="Yellow"/><XML>


   B. Modify the rows that contain
    <TYPE><Colour current="Yellow" to be
    <TYPE><Colour current="Purple"

Thanks! 4 your help

Answer 1

You're going to have to use UPDATETEXT, TEXTPTR and PATINDEX, this article gives a nice SearchAndReplace sproc.