Not sure why you are storing XML in an ntext column except for legacy reasons, nvarchar(max) or xml column type much better.
I would construct a query that CASTs the ntext to xml type and manipulates it using standard SQL Server xml methods, nodes, value, query etc. Then create a new xml type with modified data and update column.
Currently SQL Server's XQuery update facilities are poor. If you are only updating one row it's okay but for multiple rows a cursor would probably be needed.
Alternatively change to a query that returns XML by CASTing ntext to xml type and manipulate client-side.
None of this should be too taxing but it's still going to take an hour or so depending on your business rules.
Happy to help with parts but not prepared to do all the work unless you wish to employ me on the project :)
--
Joe (
Microsoft MVP - XML)
