Update XML node value in SQL Server

Question

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.

Any help would ge great!

<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)

Update

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?

Update 2 That 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.

marc_s · Accepted Answer · 2013-02-22 20:12:32Z

50

You can do something like this:

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

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

Marc

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

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

edited Feb 22, 2013 at 20:12

answered Sep 17, 2009 at 18:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Remus Rusanu Over a year ago

Actually the use of nodes() and values() is a good practice. In this case doesn't make any difference, but if multiple columns are quested is better to use nodes(): "The combination of the nodes() and value() methods can be more efficient in generating the rowset when it has several columns" technet.microsoft.com/en-us/library/ms187508(SQL.90).aspx

marc_s Over a year ago

@Remus: yes, good point - if you need several values, using nodes() makes total sense, I agree.

choudhury smrutiranjan parida Over a year ago

Excellent. just what I need.

Si8 Over a year ago

What if the node is a dropdownlist, how can I change the selected index? Thanks.

new2ios Over a year ago

Ok, but how to replace value of a node with its current value + some extra text? I googled and found zero examples? Any clue where to look?

Tarek El-Mallah · Accepted Answer · 2018-09-28 18:37:05Z

1

The simplest way to change the text inside element

UPDATE [TableName]
   SET  
      [fieldName].modify('replace value of (root/elementName/text())[1] with "wBob"')
GO

answered Sep 28, 2018 at 18:37

Tarek El-Mallah

4,1252 gold badges35 silver badges47 bronze badges

Collectives™ on Stack Overflow

Update XML node value in SQL Server

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related