更新 SQL Server 中的 XML 节点值

问题描述

我需要将 GroupID 字段更新为 0.我已经弄清楚如何检索该值，但现在我在更新它时遇到了问题.

I need to update the GroupID field to a 0. I have figured out how to retrieve the value, I am now running into problems updating it.

任何帮助都会很棒！

<ProblemProfile>
  <GroupID>-1</GroupID>
  <LayoutID>1</LayoutID>
  <MyQueries>false</MyQueries>
</ProblemProfile>

Declare @Result xml
set @Result = convert(xml,(select ProfileXML from profiles where id = 23))

SELECT x.value('.', 'int') ID
FROM @Result.nodes('/ProblemProfile/GroupID') as R(x)

更新

我现在需要做的是更新每一行的 GroupID 值为 'foo'

What I need to do now is update every single row's GroupID that has the value of 'foo'

declare @foo int
set @foo = -1

UPDATE  profiles
SET  ProfileXML.modify('replace value of (/ProblemProfile/GroupID/text())[1] with "0"')
WHERE  ProfileXML.value('(/ProblemProfile/GroupID)[1]', 'int') = @foo

这只会更新满足此条件的第一行.我将如何更新每一行?

This is only updating the first row that meets this criteria. How would I update every row?

更新 2该声明有效.事实证明，第一个节点的数据库结构可能不同.一个简单的//GroupID...etc 更新每一行.把我们绊倒的总是愚蠢的小事哈哈.

Update 2That statement works. Turns out the database structure for the first node can be different. A simple //GroupID...etc updated every row. It is always the stupid little things that trip us up haha.

推荐答案

你可以这样做:

UPDATE
   dbo.profiles
SET
   ProfileXML.modify('replace value of (/ProblemProfile/GroupID/text())[1] with "0"')
WHERE
   id = 23

在 SQL Server 2005 XQuery 和 XML-DML 详细了解您可以使用 .modify 关键字做什么(插入、删除、替换等).).

Check out this article on SQL Server 2005 XQuery and XML-DML for more details on what you can do with the .modify keyword (insert, delete, replace etc.).

马克

PS:为了获得价值，这样做会容易得多:

PS: In order to get the value, it would be a lot easier to do just this:

SELECT ProfileXML.value('(/ProblemProfile/GroupID)[1]', 'int') as ID
FROM dbo.profiles
WHERE id = 23

(当然除非您需要将 XML 作为 SQL 变量用于稍后的其他操作)

(unless of course you need the XML as a SQL variable for something else later on)

这篇关于更新 SQL Server 中的 XML 节点值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！