Let's have a sample snipet
DECLARE @xml XML = N'
<a abb="122">
<b>
</b>
</a>
<a abb="344">
<b>
</b>
</a>
...
';
SELECT @xml;
--need to update abb to be 888 in @xml here
SELECT @xml;
We can update one attribute at a time as showed here. The new question is: How can we update all at-a-time occurences of attribute abb?
Please help.
You can split the XML to a table variable, replace each node separately and then combine them again.
declare @xml xml =
'<a abb="122">
<b></b>
</a>
<a abb="344">
<b></b>
</a>'
declare @T table (XMLCol xml)
insert into @T
select
a.query('.')
from @xml.nodes('a') a(a)
update @T set
XMLCol.modify('replace value of (/a/@abb)[1] with 888')
set @xml = (select XMLCol as [*]
from @T
for xml path(''))
