TSQL 2005，XML DML - 一次更新两个值？

发布于 2024-12-03 15:46:58 字数 431 浏览 2 评论 0原文

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

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

土豪我们做朋友吧 2024-12-10 15:46:58

我不认为你运气好，谢谢。

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

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

产生：

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

但在 XML DML 空间中没有任何对这个错误消息有帮助的信息。

底线是 Expression1 & Expression2 必须是单个元素。

即使是最彻底的讨论最终也会循环遍历...

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

抱歉。 :(

PS：如果你能忍受这种黑客行为，转换为 VARCHAR(MAX) 然后执行正常的 REPLACE() 应该可以解决问题。请参阅：我可以在 t-sql 中进行查找/替换吗？

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('...')

yields:

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.

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

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

Sorry. :(

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?

回复收藏 0 原文

少年亿悲伤 2024-12-10 15:46:58

不可以，正如文档中所述，replace value of 一次只能在单个节点上操作。据我所知，没有解决方法。您需要使用 2 个更新语句。

回复收藏 0 原文

关于从前 2024-12-10 15:46:58

如果您只想更改几个值，则可以使用我在 sqlservercentral。它使用 select 语句来获取数据并对其进行多次操作，并使用单个 update 语句来更改表数据。

并不完美，但在某些用例中可能已经足够好了。基本上，它是循环解决方案的简化版本，用于更改固定数量的值。

DECLARE @temp XML
SET @temp = (SELECT Column_name FROM Table_name WHERE AuthId = @AuthId)

SET @temp.modify('replace value of (/UmAuthDto/Assignment/QueueId/text())[1] with sql:variable("@NewValue")') 
SET @temp.modify('replace value of (/UmAuthDto/Assignment/QueueName/text())[1] with sql:variable("@NewValue")')

UPDATE Table_name
SET Column_name = @temp
WHERE AuthId = @AuthId

If you just have a couple of values you want to change you could use this approach I found at sqlservercentral. It uses a select statement to get the data manipulates it multiple times and uses a single update statement to change the table data.

Not perfect but it might be good enough in some use cases. Basically it is a simplified version of the looping solution for a fixed number of values to change.

DECLARE @temp XML
SET @temp = (SELECT Column_name FROM Table_name WHERE AuthId = @AuthId)

SET @temp.modify('replace value of (/UmAuthDto/Assignment/QueueId/text())[1] with sql:variable("@NewValue")') 
SET @temp.modify('replace value of (/UmAuthDto/Assignment/QueueName/text())[1] with sql:variable("@NewValue")')

UPDATE Table_name
SET Column_name = @temp
WHERE AuthId = @AuthId

回复收藏 0 原文

~没有更多了~