sql server - 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

Answer 1

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