2

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

Santy
  • 85
  • 1
  • 12

2 Answers2

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 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 ... – Santy Sep 29 '11 at 11:55
  • @Santy - Updated answer with some alternatives. – Mikael Eriksson Sep 29 '11 at 12:39
  • How to handle if the name of main node as in your case Example will be ns2:Example? – Suraj Kumar Dec 19 '18 at 12:14
  • @SurajKumar Works just fine as it is. https://data.stackexchange.com/stackoverflow/query/949956/replace-xml – Mikael Eriksson Dec 19 '18 at 12:39
  • Can you help me to correct here- https://rextester.com/MNQ22989 -as I have replaced ns:2zarejestrujStanZgodyAsync with ns2zarejestrujStanZgodyAsync – Suraj Kumar Dec 19 '18 at 12:58
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

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • This answer is related to the question- https://stackoverflow.com/questions/53848168/update-xml-file-in-sql-server#53848168 – Suraj Kumar Dec 19 '18 at 11:16