MS SQL XML - Can I use sql:variable() to change insert type from 'into' to 'as first into' ?

Author

Can I use sql:variable() to change insert type from 'into' to 'as first into' ?

firechaser@talk21.com

2005-10-03, 7:24 am

Hi

I'm trying to dynamically affect how text is inserted into an xml
element. I'd like to be able to use the following:

-- ====================
====================
===== --
declare @x xml
,@insert int
,@insertType varchar(20)

set @x = N'
<webpage>
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<info>Here is</info>
</webpage>
';

set @insert = 3;

set @insertType =

case @insert
when 1 then ' into '
when 2 then ' as first into '
when 3 then ' as last into '
when 4 then ' before '
when 5 then ' after '
end

SET @x.modify('insert text {" some text."}

sql:variable("@insertType")

(/webpage/info)[1]');

select @x;
select 'Insert type: "'+@insertType+'"';

-- ====================
====================
===== --

The above doesn't work since sql:variable("@insertType") is not
evaluated to the string ' as last into ' when the query is executed.

I've been able to get it to work by creating a query string and
executing it with sp_executesql but for longish strings it turns into
an apostrophe-escaping nightmare and quickly becomes very difficult to
read and debug.

Does anyone have any ideas how I could get this to work?

Thanks

Michael Rys [MSFT]

2005-10-27, 9:25 am

XQuery (neither our implementation nor the standard) does not provide for a
dynamic evaluation expression. So the only way to achieve what you want, is
to use the SQL Server dynamic SQL evaluation using either EXEC or
sp_executesql.

E.g.,

declare @x xml

,@insert int

,@insertType nvarchar(20)

,@stmtstr nvarchar(max)

set @x = N'

<webpage>

<createdate>Sep 6 2005 11:04AM</createdate>

<title>Themes</title>

<info>Here is</info>

</webpage>

';

set @insert = 3;

set @insertType =

case @insert

when 1 then N' into '

when 2 then N' as first into '

when 3 then N' as last into '

when 4 then N' before '

when 5 then N' after '

end

set @stmtstr = N'DECLARE @x xml; set @x = N'''

+ cast(@x as nvarchar(max))

+ '''; SET @x.modify(''insert text {" some text."}'

+ @insertType

+ N'(/webpage/info)[1]''); select @x';

select @stmtstr;

exec sp_executesql @stmtstr;

select 'Insert type: "'+@insertType+'"';

Best regards

Michael

<firechaser@talk21.com> wrote in message
news:1128331019.225989.284590@g43g2000cwa.googlegroups.com...
> Hi
>
> I'm trying to dynamically affect how text is inserted into an xml
> element. I'd like to be able to use the following:
>
>
> -- ====================
====================
===== --
> declare @x xml
> ,@insert int
> ,@insertType varchar(20)
>
> set @x = N'
> <webpage>
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <info>Here is</info>
> </webpage>
> ';
>
> set @insert = 3;
>
> set @insertType =
>
> case @insert
> when 1 then ' into '
> when 2 then ' as first into '
> when 3 then ' as last into '
> when 4 then ' before '
> when 5 then ' after '
> end
>
> SET @x.modify('insert text {" some text."}
>
> sql:variable("@insertType")
>
> (/webpage/info)[1]');
>
> select @x;
> select 'Insert type: "'+@insertType+'"';
>
> -- ====================
====================
===== --
>
>
> The above doesn't work since sql:variable("@insertType") is not
> evaluated to the string ' as last into ' when the query is executed.
>
> I've been able to get it to work by creating a query string and
> executing it with sp_executesql but for longish strings it turns into
> an apostrophe-escaping nightmare and quickly becomes very difficult to
> read and debug.
>
> Does anyone have any ideas how I could get this to work?
>
> Thanks
>

firechaser@talk21.com

2005-10-27, 9:25 am

Yes, I wasn't sure if dynamic evaluation was supported in sql server's
implementation of XQuery. Thanks for the confirmation - much
appreciated.

Sponsored Links