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

Mikael Eriksson · Accepted Answer · 2011-09-29 12:39:05Z

3

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

edited Sep 29, 2011 at 12:39

answered Sep 29, 2011 at 11:49

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Santy Over a year ago

Hi Mikael, Thanks for the answer this works well, but i am having another problem here the type attribute is different in each element eg in whole of element A it is x and in whole of Elemnt B it is z and i have to replace all z and x with y .. how can i do this ...

Mikael Eriksson Over a year ago

@Santy - Updated answer with some alternatives.

Suraj Kumar Over a year ago

How to handle if the name of main node as in your case Example will be ns2:Example?

Mikael Eriksson Over a year ago

@SurajKumar Works just fine as it is. data.stackexchange.com/stackoverflow/query/949956/replace-xml

Suraj Kumar Over a year ago

Can you help me to correct here- rextester.com/MNQ22989 -as I have replaced ns:2zarejestrujStanZgodyAsync with ns2zarejestrujStanZgodyAsync

Suraj Kumar · Accepted Answer · 2018-12-19 11:13:53Z

0

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

answered Dec 19, 2018 at 11:13

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

1 Comment

Suraj Kumar Over a year ago

This answer is related to the question- stackoverflow.com/questions/53848168/…

Collectives™ on Stack Overflow

SQL Server XML Replace values in attribute

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related