TSQL 2005，XML DML - 一次更新两个值? [英] TSQL 2005, XML DML - Update Two Values at once?

问题描述

有没有办法将这两个替换值与 1 个更新语句结合起来?

Is there any way to combine these two replace values with 1 update statement?

UPDATE dbo.MyTable
SET MyXmlColumn.modify('replace value of (/node/@att1)[1] with "1"')
WHERE id = 1

UPDATE dbo.MyTable
SET MyXmlColumn.modify('replace value of (/node/@att2)[1] with "2"')
WHERE id = 1

http://msdn.microsoft.com/en-US/library/ms190675(v=SQL.90).aspx

推荐答案

我不认为你走运了，谢谢.

I dont think you're in luck, Thx.

我尝试了几种句法变体，但没有任何乐趣.例如，显而易见的:

I tried several syntactical variants with no joy. For example, the obvious:

SET MyXmlColumn.modify('...'), MyXmlColumn.modify('...')

产量:

在 SET 中多次指定列名MyXmlColumn"条款.同一列不能分配多个值SET 子句.修改 SET 子句以确保列是只更新一次.如果 SET 子句更新视图的列，则列名MyXmlColumn"可能在视图中出现两次定义.

The column name 'MyXmlColumn' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'MyXmlColumn' may appear twice in the view definition.

但是对于 XML DML 空间中的此错误消息没有任何帮助.

But there's nothing helpful for this error message in the XML DML space.

最重要的是 Expression1 &Expression2 必须是单个元素.

The bottom line is that Expression1 & Expression2 must be single elements.

即使是最彻底的讨论也以循环结束......

Even the most thorough discussions end up looping through it...

http://blogs.msdn.com/b/denisruc/archive/2005/09/19/471562.aspx

对不起.:(

PS:如果你能忍受这些，转换为 VARCHAR(MAX) 然后做一个普通的 REPLACE() 应该可以解决问题.请参阅:我可以在 t-sql 中进行查找/替换吗?

PS: If you can stand the hackiness, casting to VARCHAR(MAX) then doing a normal REPLACE() should do the trick. See: Can I do a find/replace in t-sql?

这篇关于TSQL 2005，XML DML - 一次更新两个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！