Updating Xml attributes with new values in a SQL Server 2008 table
29,539
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 @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
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 @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
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 @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;
Author by
SMD
Updated on September 24, 2020Comments
-
SMD over 3 years
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" />
-
SMD over 11 yearsHi Kapil Khandelwal. Thanks very much for your editing.
-
marc_s over 11 yearsSQL = 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.
-
-
SMD over 11 yearsHi 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.