Update attribute value depending upon condition in XML using XQuery

Question

I have the following XML

<Root xmlns:test="http://sample">
   <Values>
      <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="1">
      </Value>
      <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="1">
      </Value>
      <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2">
      </Value>
      <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2">
      </Value>
      <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="1">
      </Value>
   </Values>
</Root>

Here I am trying to update all value elements with type=2 to type = 3.

Please can somebody tell me how this can be done?

Mikael Eriksson · Accepted Answer · 2012-11-28 14:29:43Z

declare @XML xml = 
'<Root xmlns:test="http://sample">
  <Values>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="1"></Value>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="1"></Value>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2"></Value>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2"></Value>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="1"></Value>
  </Values>
</Root>';

while @XML.exist('declare namespace d3p1="http://www.w3.org/2001/XMLSchema-instance";
                  /Root/Values/Value[@d3p1:type = "1"]') = 1
begin
  set @XML.modify('declare namespace d3p1="http://www.w3.org/2001/XMLSchema-instance";
                   replace value of (/Root/Values/Value[@d3p1:type="1"]/@d3p1:type)[1] 
                   with 2')
end

select @XML

Result:

<Root xmlns:test="http://sample">
  <Values>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2" />
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2" />
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2" />
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2" />
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema-instance" d3p1:type="2" />
  </Values>
</Root>

Collectives™ on Stack Overflow

Update attribute value depending upon condition in XML using XQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related