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

推荐答案

我不认为您很幸运，Thx.

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:如果您可以忍受hackhackiness，则将其转换为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屋！