Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel