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

Question

I have a ntext and NOT XML data type column. It stores all the XML data. I need to update xml node in records. It gives the error message “Improper use of the xml data type method“ modify. ”In this context, a non-mutator method is expected.

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,column_name).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 

XML example:

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

Answer 1

You need to use the change method for the XML data type.

 begin transaction declare @Cps_Id int; set @Cps_Id = 236; declare @Cps_Message nvarchar(1024); set @Cps_Message = 'updating cpsia message with smoking'; select id, CONVERT(xml,[text]) txt into #tmp from SO5954359 select * from #tmp update #tmp set txt.modify('replace value of (/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]/CpsiaMessage/text())[1] with sql:variable("@Cps_Message")') select * from #tmp drop table #tmp rollback 

You can then update the source table by adding the updated temporary table to the source table on the key.