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

Question

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

Sample XML:

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

Duncan Howe · Accepted Answer · 2011-05-10 20:53:29Z

7

You need to use the modify method on 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 could then update the original table by joining the updated temporary table to the original table on the key.

answered May 10, 2011 at 20:53

Duncan Howe

3,03521 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DotNetDeveloper Over a year ago

it throws an error "Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context." when I modified to single update query with the table and NOT using temp table....

DotNetDeveloper Over a year ago

If one direct update query doesn't work ..can you post some sample for "You could then update the original table by joining the updated temporary table to the original table on the key."

DotNetDeveloper Over a year ago

I have updated above code with your solution so that I can get the nearest one and Thank's for your time

Duncan Howe Over a year ago

@user594014: Where to start...? 1. The error: You are using standard update syntax (i.e. update <table> Set <field>=<value>). This is incorrect. The correct syntax is simply update <table> set <xml_field>.modify(...) 2. A single update would work if the data was stored in an xml typed column. As you have an NVARCHAR column, you would need to run a second update (e.g.

update <table> set <nvarchar_column>=convert(nvarchar(1024),<temp_table>.<xml_field> from <table> inner join <temp_table> on <temp_table>.<key_fld>=<table>.<key_field>

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related