SQL Server: How do I use modify() to alter XML data in a column with a TEXT data type
I'm trying to modify some XML values in a database. I can get it to work on columns that contain XML that are using the XML data type. However, I can't get it to work on TEXT columns.
Also, I can SELECT XML data on TEXT columns (by using CAST() to convert it to XML), but still can't UPDATE.
Example:
UPDATE [xmltest]
SET [xmltext].modify('replace value of (/data/item[1]/text())[1] with "newvalue"')
Error: Cannot call methods on text.
Is there some way I can get this to work on a TEXT column? There's already TONS of data stored, so I'd rather not have to request to change the data type 开发者_如何学编程on the column.
Thanks!
Sunsu
You cannot directly modify this - what you can do is a three steps process:
- select the TEXT column from the table into a local XML variable
- modify the XML variable
- write back your changes to the database
Something like this:
-- 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
It's a bit involved, but it works! :-)
精彩评论