Updating Xml Attributes With New Values In A Sql Server 2008 Table

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

Solution 1:

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@Ttable(XMLText xml)

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

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

Solution 2:

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@nodeCountintDECLARE@iintSET@i=1SELECT@nodeCount=@xml.value('count(/Root/Identification/@Age)','int') 

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

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

SET@i=@i+1ENDSELECT@xml

Solution 3:

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@tTABLE (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@tVALUES (@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@numberintselect@number=convert(int, (convert(nvarchar(50), @update_count)))

declare@Nodeintset@Node=1

while @Node<=@numberbeginUPDATE@tSET
   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+1endSELECT*FROM@t;