Change attribute value for a specific node in XML using SQL

Question

I'd like to know how to update an XML attribute based on the value of another attribute?

In the following example, I'd like to set b="321" where a="banana":

Declare @t table ( x xml )
insert into @t(x) values ('<n1><n2 a="apple" b="2" /><n2 a="banana" b="21" /></n1>')

This select works well to extract the desired value, but how can it be modified?

select m.n.value('@b', 'int') from @t 
  outer apply x.nodes('/n1/n2') as m(n)
  where m.n.value('@a', 'nvarchar(max)') = 'banana'

This update does nothing...

update @t
  set x.modify('replace value of (/n1/n2/@b)[1] with "321"')
  where x.value('(n1/n2/@b)[1]', 'nvarchar(max)') = 'banana'

select * from @t

This update changes the first node, which isn't the desired result

update @t
  set x.modify
  (
    'replace value of (/n1/n2/@b)[1] with
    (
      if ( (/n1/n2/@a)[1] = "banana" ) then "321"
      else "?help"
    )'
  )

select * from @t

Thanks in advance for any help...

Answer 1

Try it like this

Declare @t table ( x xml )
insert into @t(x) values ('<n1><n2 a="apple" b="2" /><n2 a="banana" b="21" /></n1>');

UPDATE @t SET x.modify('replace value of (/n1/n2[@a="banana"]/@b)[1] with "321"');

SELECT * FROM @t;

The idea in short:

You must tell what you want to replace. In this case the value of the attribute @b in the <n1>-element, where the attribute @a is "banana". This needs a predicate in the XPath. You read it like:

*Find a n1 and below a n2, check for one with an attribute a which is "banana". Yeah, you've found one? Great! No take the attribute b of the first occurance and change it.