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.