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

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

paulr-605880 SSC Enthusiast Points: 143 More actions · Answer 1

Kent,

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

Paul

Russel Loski SSCertifiable Points: 7059 More actions · Answer 2

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>

</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 · Answer 3

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 · Answer 4

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