MS SQL XML - How can I replace a value in xml with .modify from the variable?

Author

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

Farmer

2006-11-08, 7:14 pm

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(@ConversationHa
ndle as char(36)) + '"'

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

SELECT @xml1

Kent Tegels

2006-11-09, 12:13 am

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/

Farmer

2006-11-09, 7:21 pm

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(@ConversationHa
ndle 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:b87ad741397a8c8
d18706916da0@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/
>
>

Denis Ruckebusch [MSFT]

2006-11-20, 7:21 pm

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@somewhere.com> wrote in message news:%23RAddk3AHHA.4428@TK2MSFTNGP04.phx.gbl...
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(@ConversationHa
ndle as char(36)) + '"'

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

SELECT @xml1

Kevin

2006-11-22, 7:16 pm

> 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(@ConversationHa
ndle 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(@ConversationHa
ndle 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

Sponsored Links