How do I update a XML string in an ntext column in SQL Server?

Question

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

Since it's an NTEXT field, you cannot use any of the usual string functions, unfortunately.

What version of SQL Server are you using??

If you're on SQL Server 2005 and up, you have two options:

• you can cast your NTEXT to NVARCHAR(MAX) and then you can use all the usual string functions like REPLACE, SUBSTRING and so on
• you can cast your NTEXT to XML and use the XML functions available for SQL Server 2005

The first option could look like this:

UPDATE 
  YourTable
SET
  Value = CAST(REPLACE(CAST(Value as NVARCHAR(MAX)), 
                       'Colour="Yellow"', 'Colour="Blue"') AS NTEXT) 
WHERE
  .......

For the second option, see Quasnoi's answer - however, mind you: your XML is a bit odd.....

<TYPE><Colour="Yellow" Size="Large"></TYPE>

is a bit unusual and in my opinion invalid - either the "Colour" is an attribute on the <TYPE> tag

<TYPE Colour="Yellow" Size="Large"></TYPE>

or then <Colour> in itself is a XML tag but then the "Yellow" must be assigned to an attribute:

<TYPE><Colour current="Yellow" Size="Large"></TYPE>

You cannot assign a value directly to the XML tag as you do in your XML, IMHO.

If you're on SQL Server 2000, things will get a lot harder....

Marc

Answer 2

In SQL Server 2005+, using a intermediary temporary table:

DECLARE @q AS TABLE (xid INT NOT NULL, xdoc XML NOT NULL, modified TINYINT NOT NULL DEFAULT 0)

INSERT
INTO    @q (xid, xdoc)
SELECT  id, doc
FROM    mytable

UPDATE  @q
SET     xdoc.modify('replace value of (/TYPE/@Colour)[1] with "blue"'),
        modified = 1
WHERE   xdoc.value('(/TYPE/@Colour)[1]', 'NVARCHAR(MAX)') = 'Yellow'

UPDATE  mytable
SET     doc = CAST(xdoc AS NVARCHAR(MAX))
FROM    @q q
WHERE   id = q.xid
        AND q.modified = 1