Updating Xml attributes with new values in a SQL Server 2008 table

sql, sql-server-2008, xml, xquery

Solution

From the early versions of your question it looks like your XML actually is on different rows in a table. If that is the case you can use this.

update YourTable set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

Working sample using a table variable.

declare @T table(XMLText xml)

insert into @T values('<Identification Name="John"  Family="Brown"   Age="30" />')
insert into @T values('<Identification Name="Smith" Family="Johnson" Age="35" />') 
insert into @T values('<Identification Name="Jessy" Family="Albert"  Age="60" />')
insert into @T values('<Identification Name="Mike"  Family="Brown"   Age="23" />')
insert into @T values('<Identification Name="Sarah" Family="Johnson" Age="30" />')

update @T set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

select *
from @T

Problem

I have a table in SQL Server 2008 that it has some columns. One of these columns is in Xml format and I want to update some attributes. For example my Xml column's name is `XmlText` and it's value in 5 first rows is such as: ``` <Identification Name="John" Family="Brown" Age="30" /> <Identification Name="Smith" Family="Johnson" Age="35" /> <Identification Name="Jessy" Family="Albert" Age="60" /> <Identification Name="Mike" Family="Brown" Age="23" /> <Identification Name="Sarah" Family="Johnson" Age="30" /> ``` and I want to change all `Age` attributes that are 30 to 40 such as below: ``` <Identification Name="John" Family="Brown" Age="40" /> <Identification Name="Smith" Family="Johnson" Age="35" /> <Identification Name="Jessy" Family="Albert" Age="60" /> <Identification Name="Mike" Family="Brown" Age="23" /> <Identification Name="Sarah" Family="Johnson" Age="40" /> ```

Original source