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" />
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
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With