XML Data Type .modify() Method in an update statement?

• LerxtDBA

  SSCrazy

  Points: 2190

  More actions

  August 19, 2010 at 12:24 pm

  #95004

  I have an column of XML data type in a table. I need to change the text() node value on one element of the XML column.

  I can pull the data into a variable of type XML, run the following type of statement, and then update the column in the table.

  set @replace_string = 'replace value of (/ROOT/CHILD/ELEMENT/text())[1] with sql:variable("@add_amount")'

  The problem is this starts to feel like RBAR methodology and I'm strongly opposed to that.

  When I try to write a select or update statement that uses the .modify() method inline I get the following error:

  Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context.

  Does anyone know if I can use the .modify() method in a select or update statement?

  An example of what I've tried to do looks like this:

  select xml_field.modify('

  replace value of (/ROOT/CHILD/ELEMENT/text())[1]

  with sql:variable("@add_amount")

  ')

  FROMmy_table

• paulr-605880

  SSC Enthusiast

  Points: 143

  More actions

  October 5, 2010 at 11:34 am

  #1231932

  Kent,

  Did you ever get an answer on this? I'm facing the same problem.

  Paul

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  October 5, 2010 at 1:20 pm

  #1232027

  Would something like the following help?

  declare @t table (xmlcol xml);

  declare @add_amount varchar(20);

  set @add_amount = 'three hundred';

  insert @t values ('<t>

  <v>test</v><v>test2</v>

  </t>

  ')

  update @t

  set xmlcol.modify ('insert <v>test2</v> into (/t)[1]')

  update @t

  set xmlcol.modify ('replace value of (/t/v[1]/text())[1] with "testnew"')

  update @t

  set xmlcol.modify ('replace value of (/t/v[2]/text())[1] with sql:variable("@add_amount")')

  select * from @t

  Russel Loski, MCSE Business Intelligence, Data Platform

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  June 11, 2012 at 11:32 am

  #1499409

  Russel is right. You can't SELECT the results of an XML DML modify method... you should extract the values to a variable, or simply update the XML values directly.

  http://stackoverflow.com/questions/5087950/sql-xquery-how-to-replace-text-in-update-query

  _________________________________
  seth delconte
  http://sqlkeys.com

• Ayeager78

  Old Hand

  Points: 316

  More actions

  August 19, 2012 at 8:48 pm

  #1527434

  Total Newbie Here so I am just taking a stab. Is it Not correct that modifying XML has to be done in a update statement.

  I don't believe from what I was just studying that modify works any other way. Does it?

  I think code would look something like

  Update my_table

  set xml_field.modify('replace value of (/ROOT/CHILD/ELEMENT/text() ) [1]

  with sql:variable("@add_amount")

  Edit * perhaps you will need to make last line

  with {sql:variable("@add_amount")}

  I just discovered the SQL extensions sql:variable, sql:column etc.... in xml_field.query I have to enclose in {}

  Hope my SWAG helps. Let me know...

  --Andy