SQL Server：如何使用modify（）来更改具有TEXT数据类型的列中的XML数据

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.

我正在尝试修改数据库中的一些XML值。我可以使用它来处理包含使用XML数据类型的XML的列。但是,我不能让它在TEXT列上工作。

Also, I can SELECT XML data on TEXT columns (by using CAST() to convert it to XML), but still can't UPDATE.

另外,我可以在TEXT列上选择XML数据(通过使用CAST()将其转换为XML),但仍然无法更新。

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.

有什么方法可以让它在TEXT列上工作吗?已经存储了大量数据,因此我不必请求更改列上的数据类型。

Thanks!

Sunsu

1 个解决方案

#1

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

从表中选择TEXT列到本地XML变量

modify the XML variable

修改XML变量

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! :-)

它有点涉及,但它的工作原理! :-)

#1