xml-sql: update multiple nodes

Question

I have the following problem. i have a xml file stored in a sql database. i should change all the VALUE tag values by dividing per 100. here is an extract the structure of the xml:

<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201110180000</HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201110250000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201111020000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201111080000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>20 </VALUE>
  <HEIGHTC_DATE>201111150000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201111290000 </HEIGHTC_DATE>
</HEIGHTC>

I have found the following query:

DECLARE @var varchar(50)
set @var='HEIGHTC'
UPDATE tcdc.dbo.BADM_Xml
SET  xml_badm.modify('replace value of (/ROOT/*[local-name()=sql:variable("@var")]/VALUE/text())[1] with (/ROOT/*[local-name()=sql:variable("@var")]/VALUE)[1] * 0.01')

and it works fine for a single node at a time: is there a way to generalize and update all in a single instruction? thanx in advance diego

Mikael Eriksson · Accepted Answer · 2013-05-23 12:19:06Z

23

replace value of can only update one node at a time.

Find the max number of nodes used in all the XML's you want to update and use the loop variable in the update statement to modify one node at a time.

The where clause checks for the existence if nodes to modify. Without that you would modify every row in the table for each iteration.

declare @I int

select @I = max(xml_badm.value('count(/ROOT/HEIGHTC/VALUE)', 'int'))
from YourTable

while @I > 0 
begin
  update YourTable
  set xml_badm.modify
    ('replace value of ((/ROOT/HEIGHTC/VALUE)[sql:variable("@I")]/text())[1]
      with ((/ROOT/HEIGHTC/VALUE)[sql:variable("@I")]/text())[1] * 0.01')
  where xml_badm.exist('(/ROOT/HEIGHTC/VALUE)[sql:variable("@I")]') = 1
  set @I = @I - 1
end

edited May 23, 2013 at 12:19

answered May 23, 2013 at 12:14

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

user2864740 Over a year ago

Non-practical for any non-trivial number of rows (m x n updates) or when 'max i' varies greatly.

Collectives™ on Stack Overflow

xml-sql: update multiple nodes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related