如何更新SQL Server中的ntext列中的XML字符串?

have a SQL table with 2 columns. ID(int) and Value(ntext)

有一个包含2列的SQL表。ID(int)和价值(ntext)

The value rows have all sorts of xml strings in them.

值行中包含各种xml字符串。

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

谢谢!4你的帮助

2 个解决方案

#1

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

在SQL Server 2005+中，使用中间临时表:

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

#2

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

由于它是一个NTEXT字段，不幸的是，您不能使用任何常见的字符串函数。

What version of SQL Server are you using??

您正在使用哪个版本的SQL Server ?

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

如果您使用的是SQL Server 2005或以上版本，您有两个选项:

you can cast your NTEXT to NVARCHAR(MAX) and then you can use all the usual string functions like REPLACE, SUBSTRING and so on
您可以将NTEXT转换为NVARCHAR(MAX)，然后可以使用所有常见的字符串函数，如REPLACE、SUBSTRING等
you can cast your NTEXT to XML and use the XML functions available for SQL Server 2005
您可以将NTEXT转换为XML，并使用SQL Server 2005可用的XML函数

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.....

对于第二种选择，请参阅Quasnoi的答案——然而，请注意:您的XML有点奇怪……

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

或者，< color >本身就是一个XML标记，但是“黄色”必须分配给一个属性:

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

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

不能像在XML IMHO中那样直接将值赋给XML标记。

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

如果你在SQL Server 2000,事情会很多困难....

Marc

马克

#1