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

问题描述

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

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

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

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>

我该怎么做:

A.列出

`<TYPE><Colour current="Yellow",`
    bearing in mind that there is an entry
    <XML><Name current="Yellow"/><XML>

B.修改包含

<TYPE><Colour current="Yellow" to be
<TYPE><Colour current="Purple"

谢谢！4 你的帮助

推荐答案

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

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