SQL Server XML Replace values in attribute

Question

I want to replace the value in an attribute in a XML. This attribute comes multiple times in the xml. How can i replace these all at once

my xml lools some what like below :

 <Example>
  <A>
   <B Type = "x">qqq</B>
   <B Type = "x">www</B>
  </A>
  <C>
   <D Type = "x">aaa</D>
   <D Type = "x">uuu</D>
  </C>
 </Example>

I want to replace all x with y

Answer 1

You can try the following. Here ns2zarejestrujStanZgodyAsync is main node and I have updated the value of idSystemy from 13 to 38.

UPDATE @t
SET yourXML.modify('replace value of 
(/ns2zarejestrujStanZgodyAsync/rejestrujStanZgody/idSystemy/text())[1] with ("38")')

You can check for demo here- XML Node Update

Answer 2

You can not replace all at once using replace value of (XML DML). You have to do it in a loop.

declare @xml xml = '
<Example>
  <A>
   <B Type = "x">qqq</B>
   <B Type = "x">www</B>
  </A>
  <C>
   <D Type = "x">aaa</D>
   <D Type = "x">uuu</D>
  </C>
 </Example>
'

while (select @xml.exist('//*[@Type = "x"]')) = 1
begin
  set @xml.modify('replace value of (//*[@Type = "x"]/@Type)[1] with "y"')
end

select @xml

Result:

<Example>
  <A>
    <B Type="y">qqq</B>
    <B Type="y">www</B>
  </A>
  <C>
    <D Type="y">aaa</D>
    <D Type="y">uuu</D>
  </C>
</Example>

Update

Replace values x and z with y:

while (select @xml.exist('//*[@Type = ("x","z")]')) = 1
begin
  set @xml.modify('replace value of (//*[@Type = ("x","z")]/@Type)[1] with "y"')
end

Replace all values with y:

while (select @xml.exist('//*[@Type != "y"]')) = 1
begin
  set @xml.modify('replace value of (//*[@Type != "y"]/@Type)[1] with "y"')
end