how to update multiple xml node values through a single query

Question

consider the xml

<root>
  <parent id="1">
    <child>
      <field name="1">
        <value>abc</value>
      </field>
      <field name="2">
        <value>cdf</value>
      </field>
      <field name="2">
        <value>xyz</value>
      </field>
      <field name="1">
         <value>uvw</value>
      </field>
    </child>
  </parent>
  <parent id="2">
    <child>
      <field name="1">
        <value>123</value>
      </field>
      <field name="3">
        <value>234</value>
      </field>
      <field name="4">
        <value>34</value>
      </field>
      <field name="1">
        <value>544</value>
      </field>
   </child>
 </parent>

i want to update the text value of 'value' where 'name' attribute of field node is equal to '1'

here is what i have right now....

update newTable
set xmlcol.modify('replace value of(/root/parent/child/field[@name="1"]/value/text()) with "newValue"')

will this work for all occurrence or just first one???

i was doing it earlier but could not get the result right now i am on the run so cant test it straight away — goldsmit409
– goldsmit409, Commented Mar 25, 2014 at 19:07

goldsmit409 · Accepted Answer · 2014-03-27 05:18:51Z

1

for anyone this worked for me.....

BEGIN

declare @I int

;WITH XMLNAMESPACES(DEFAULT 'your namespace')

SELECT @I= xmlcol.value('count(/root/parent/child/field[@name="1"]/value/)', 'int')

FROM newTable

select @I as a

while @I > 0 

  begin

  ;WITH XMLNAMESPACES(DEFAULT 'your namespace')

  update newTable set xmlcol.modify('replace value of (/root/parent/child/field[@name="1"]/value/text()) with "newValue"')

  set @I = @I - 1

end
end

answered Mar 27, 2014 at 5:18

goldsmit409

4784 silver badges26 bronze badges

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

Comments

GriGrim · Accepted Answer · 2014-03-25 21:45:11Z

0

This will work only for the first (second or whatever @i is) occurrence.

declare @i int = 1;

update newTable
set xmlcol.modify('replace value of (/root/parent/child/field[@name="1"]/value/text())[{sql:variable("@i")}] with "newValue"');

You should replace all the values with the new ones in a loop.

answered Mar 25, 2014 at 21:45

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

Collectives™ on Stack Overflow

how to update multiple xml node values through a single query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related