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 w
ith .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=""', 'Conv
ersationHandle="' + @.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 @.xml1Hello Farmer,
Off the top of my head, sp_sqlexcutesql is the only was to do this as the
constructor isn't availble in XQuery DML for SQL Server 2005.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent,
I also could not find any better way.
the answer using sp_executesql is:
DECLARE @.SQL nvarchar(max)
SET @.SQL =
'SET @.xml.modify(''replace value of (/Tasks/row/@.ConversationHandle)[1] with
"' + cast(@.ConversationHandle as char(36)) + '"'');'
SELECT @.sql
EXEC sp_executesql
@.stmt = @.sql
,@.params = N'@.xml xml OUTPUT'
,@.xml = @.xml1 OUTPUT
select @.xml1
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741397a8c8d18706916da0@.news.microsoft.com...
> Hello Farmer,
> Off the top of my head, sp_sqlexcutesql is the only was to do this as the
> constructor isn't availble in XQuery DML for SQL Server 2005.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>|||Well you must always use a string literal in the modify method but you can h
ave 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 e
xample. 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.mi
crosoft.com/info/cpyright.htm
"Farmer" <someone@.somewhere.com> wrote in message news:%23RAddk3AHHA.4428@.TK
2MSFTNGP04.phx.gbl...
Thanks for your help.
I am trying to modify a value within an xml. I found that this can be done w
ith .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=""', 'Conv
ersationHandle="' + @.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|||
> 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 variabliz
ed. 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=""', 'Co
nversationHandle="' + @.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
>
>
>
>
> Thanks for your help.
> I am trying to modify a value within an xml. I =
> found that=20
> this can be done with .modify but I must use literal for modify command. =
> I need=20
> it variablized. Is there any way to do it without =
> sp_executesql?
> thanks
> declare
> @.xml varchar(max)
> ,@.xml1 xml
> , @.ConversationHandle char(36)
> set @.xml=3D ''
> SET @.xml1 =3D @.xml
> SET @.ConversationHandle =3D newid()
> This is what I want but using the xml .modify=20
> function
> select cast(@.xml as xml), cast(REPLACE(=20
> @.xml, =
> 'ConversationHandle=3D""', 'ConversationHandle=3D"' + @.ConversationHandle
+ '"') as xml)
> SET @.xml1.modify('
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "boo"
> ')
> SELECT @.xml1
> DECLARE @.m varchar(1000)
> SET @.m =3D'
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "' + cast(@.ConversationHandle as char(36)) + '"'
> SET @.xml1.modify(@.m) -- this =
> errors
> SELECT =
> @.xml1
You can easily do this using SQL variables in the XQuery literal
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with sql:variable("@.ConversationHandle")'
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities