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

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