问题描述
有一个包含 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