SQL Server: how do I use modify () to modify XML data in a column with a TEXT data type

Question

I am trying to change some XML values ​​in a database. I can get it to work with columns containing XML that use the XML data type. However, I cannot get it to work with TEXT columns.

In addition, I can SELECT the XML data in TEXT columns (using CAST () to convert it to XML), but still cannot UPDATE.

Example:

UPDATE [xmltest]  
SET [xmltext].modify('replace value of (/data/item[1]/text())[1] with "newvalue"')

Error: It is not possible to call methods in the text.

Is there a way to get this to work on a TEXT column? TONS data is already stored there, so I do not want to request a change in the data type in the column.

Thank!

Sunsu

Answer 1

- , , :

• TEXT XML
• XML

- :

-- declare new local variable, load TEXT into that local var
DECLARE @temp XML

SELECT 
     @temp = CAST(YourColumn AS XML) 
FROM 
     dbo.YourTable
WHERE
     ID = 5     -- or whatever criteria you have

-- make your modification on that local XML var
SET 
   @temp.modify('replace value of (/data/item[1]/text())[1] with "newvalue"') 

-- write it back into the table as TEXT column      
UPDATE 
   dbo.YourTable
SET 
   YourColumn = CAST(CAST(@temp AS VARCHAR(MAX)) AS TEXT)
WHERE
     ID = 5     -- or whatever criteria you have

, !:-)