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

  • 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 

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



    Tuesday, May 10, 2011 11:46 AM


  • User2059696342 posted



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 11, 2011 11:34 AM