SQL Update query to update xml in records based on xml node value ?

Question

User2059696342 posted

I have a column of ntext data type and NOT XML. It stores all xml data. I need to update xml node in the records. It throws an error saying "Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context."

 

begin transaction
declare @Cps_Id int;
set @Cps_Id = 236;
declare @Cps_Message nvarchar(1024);
set @Cps_Message = 'updating cpsia message with smoking'; 

update table_name
set column_name =  CONVERT(xml,content_html).modify('replace value of (/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]/CpsiaMessage/text())[1] with sql:variable("@Cps_Message")') 
WHERE Convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]')=1 

rollback

<root> <ProductInformation> <Name> Truck with Battery Charger</Name> <Description>Fr.</Description> <CPSIA> <CpsiaDetails> <Item> <CpsiaId>456</CpsiaId> <CpsiaMessage>waring</CpsiaMessage> </Item> <Item> <CpsiaId>236</CpsiaId> <CpsiaMessage>to health</CpsiaMessage> </Item> </CpsiaDetails> </CPSIA> </ProductInformation> </root>