TSQL：如何使用另一个相关表中的 xml 标记的值更新 xml 标记的值？

发布于 2024-09-06 00:01:07 字数 276 浏览 9 评论 0原文

如何使用另一个相关表中 xml 标记的值更新 xml 标记的值？

像这样的东西：

UPDATE v2
 SET
 [xml].modify ('replace value of (//TAG1/text())[1] 
                with "CAST(v1.[xml].query(''//TAG2'') AS NVARCHAR(MAX))"')
FROM 
 table2 v2, 
 table1 v1 
WHERE
 v2.id = v1.id

原文

How can I update the value of an xml tag with the value of an xml tag from another related table?

something like this:

UPDATE v2
 SET
 [xml].modify ('replace value of (//TAG1/text())[1] 
                with "CAST(v1.[xml].query(''//TAG2'') AS NVARCHAR(MAX))"')
FROM 
 table2 v2, 
 table1 v1 
WHERE
 v2.id = v1.id

分享到QQ

分享到微博

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

发布评论

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

扛起拖把扫天下 2024-09-13 00:01:07

我很晚才回答这个问题，但如果您希望将来“批量更新”XML 列，并且您使用的是 SQL 2005+，则可以使用 CTE 来完成此操作：

WITH NewXmlData AS
(
   SELECT v2.Id AS id
      , CAST(v1.[xml].query('//TAG2') AS NVARCHAR(MAX)) AS NewValue
   FROM table2 AS v2
   INNER JOIN table1 AS v1 ON v2.id = v1.id
)
UPDATE v2
SET [xml].modify ('replace value of (//TAG1/text())[1] 
                with sql:column("NewXmlData.NewValue")')
FROM table2 AS v2
INNER JOIN NewXmlData AS nxd ON v2.id = nxd.id

I'm pretty late to this question, but if you're looking to "mass update" an XML column in the future, and you are in SQL 2005+, you can use a CTE to accomplish this:

WITH NewXmlData AS
(
   SELECT v2.Id AS id
      , CAST(v1.[xml].query('//TAG2') AS NVARCHAR(MAX)) AS NewValue
   FROM table2 AS v2
   INNER JOIN table1 AS v1 ON v2.id = v1.id
)
UPDATE v2
SET [xml].modify ('replace value of (//TAG1/text())[1] 
                with sql:column("NewXmlData.NewValue")')
FROM table2 AS v2
INNER JOIN NewXmlData AS nxd ON v2.id = nxd.id

回复收藏 0 原文

邮友 2024-09-13 00:01:07

我认为您不能一步完成此操作 - 但如果您使用的是 SQL Server 2008，则可以分两步完成：

DECLARE @NewValue NVARCHAR(50)

SELECT @NewValue = [xml].value('(//TAG2)[1]', 'NVARCHAR(50)')
FROM dbo.v1 
WHERE id = 1

UPDATE dbo.v2
SET [xml].modify('replace value of (//TAG1/text())[1] with sql:variable("@NewValue")')
WHERE id = 1

在 sql:variable 中指定 sql:variable 的能力code>replace value of XQuery 是 SQL Server 2008 中的一项新功能 - 因此，如果您停留在 2005 年，那么不幸的是，这将不起作用。

I don't think you can do this in a single step - but you can do it in two steps, if you're on SQL Server 2008:

DECLARE @NewValue NVARCHAR(50)

SELECT @NewValue = [xml].value('(//TAG2)[1]', 'NVARCHAR(50)')
FROM dbo.v1 
WHERE id = 1

UPDATE dbo.v2
SET [xml].modify('replace value of (//TAG1/text())[1] with sql:variable("@NewValue")')
WHERE id = 1

The ability to specify a sql:variable in your replace value of XQuery is a new feature in SQL Server 2008 - so if you're stuck on 2005, this won't work, unfortunately.

回复收藏 0 原文