Re: How can I replace a value in xml with .modify from the variable?

Well you must always use a string literal in the modify method but you can have access to the values in sql columns or sql variables through the use of sql:column() and sql:variable() in your XQuery.

I would suggest that you read about it in Books Online, but here's a quick example. You can replace the value of your ConversationHandle attribute with the value from a sql variable named @handle like this

declare @xml xml, @handle char(36)

set @xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'

SET @handle = newid()

SET @xml.modify('

replace value of (/Tasks/row/@ConversationHandle)[1]

with sql:variable("@handle")

')


I hope this helps

Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

"Farmer" <someone@xxxxxxxxxxxxx> wrote in message news:%23RAddk3AHHA.4428@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for your help.

I am trying to modify a value within an xml. I found that this can be done with .modify but I must use literal for modify command. I need it variablized. Is there any way to do it without sp_executesql?

thanks

declare

@xml varchar(max)

,@xml1 xml

, @ConversationHandle char(36)

set @xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'

SET @xml1 = @xml

SET @ConversationHandle = newid()

This is what I want but using the xml .modify function

select cast(@xml as xml), cast(REPLACE( @xml, 'ConversationHandle=""', 'ConversationHandle="' + @ConversationHandle + '"') as xml)



SET @xml1.modify('

replace value of (/Tasks/row/@ConversationHandle)[1]

with "boo"

')

SELECT @xml1

DECLARE @m varchar(1000)

SET @m ='

replace value of (/Tasks/row/@ConversationHandle)[1]

with "' + cast(@ConversationHandle as char(36)) + '"'

SET @xml1.modify(@m) -- this errors

SELECT @xml1

Relevant Pages