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

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?
ThanksXQuery (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
>|||Yes, I wasn't sure if dynamic evaluation was supported in sql server's
implementation of XQuery. Thanks for the confirmation - much
appreciated.