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

Question

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" />

SQL = Structured Query Language - it's not a product. We need to know which database system (and which version) you're using - SQL Server, Oracle, IBM DB2, PostgreSQL etc. — marc_s, Sep 22 '12 at 17:45

score 12 · Accepted Answer · answered Sep 23 '12 at 15:35

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

score 8 · Answer 2 · answered Sep 23 '12 at 15:18

Try this:

declare @xml XML

SET @xml = '<Root>
         <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" />
         </Root>'

 DECLARE @nodeCount int
DECLARE @i int

SET @i = 1

SELECT @nodeCount = @xml.value('count(/Root/Identification/@Age)','int') 

PRINT 'Number of nodes found: ' + STR(@nodeCount)

WHILE (@i <= @nodeCount)
BEGIN
Set @xml.modify('replace value of (/Root/Identification/@Age)[.=30][1] with "40"')

SET @i = @i + 1
END

SELECT @xml

EMAI · Answer 3 · 2012-09-23T15:24:39.040

The modify method is your response. But if you need to have a condition you can use if expression in with section of this method.

DECLARE @t TABLE (RecordXML XML);
Declare @xml XML
SET @xml = '<Root>
         <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" />
         </Root>'
INSERT @t VALUES (@xml);


Declare @value nvarchar(50)
DECLARE @oldvalue nvarchar(50)
SET @value = '40'
SET @oldvalue = '30'

Declare @update_count xml
select @update_count = @xml.query('count(/Root/Identification/@Age[.=sql:variable("@oldvalue")])')
Declare @number int
select @number = convert(int, (convert(nvarchar(50), @update_count)))

declare @Node int
set @Node = 1

while @Node <= @number
begin
UPDATE
  @t
SET
   RecordXML.modify('replace value of 
   (/Root/Identification/@Age[.=sql:variable("@oldvalue")])[1] with sql:variable("@value")')
WHERE
  RecordXML.exist('/Root/Identification[@Age=sql:variable("@oldvalue")]') = 1;

set @Node = @Node + 1
end

SELECT * FROM @t;

Hi EMAI, thanks for your answer. But your answer isn't completely correct because it changes only first row but I need a query that changes all age attributes equal 30, therefor last row must changes too. If you write your query with a loop it would be very good. — SMD, Sep 23 '12 at 10:50

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

3 Answers3

Linked