CODEWITHSUNDEEP
sql-server-2008t-sqlxquery
Ashwani K
Ashwani K

Reputation: 7990

Update attribute value depending upon condition in XML using XQuery

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?

Upvotes: 0

Views: 328

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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>

Upvotes: 2

Related Questions