Re: XQuery SQL 2005 Insert block of xml into existing xml type

It's not going to be much consolation to you, but you have to use dynamic
SQL to do what you're asking, and it ain't pretty:

DECLARE @x XML;
DECLARE @xntext NVARCHAR(MAX);

SELECT @x = N'<People><Person><Name>Ray</Name></Person></People>';

SELECT @xntext = N'<Person>
<Name>Tim</Name>
</Person>';

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET @px.modify(''insert ' + @xntext +
N'
as first into (//People)[1] '')';

EXEC dbo.sp_executesql @sql, N'@px XML OUTPUT', @px = @x OUTPUT;

SET @xntext = N'<Person>
<Name>Jim</Name>
</Person>';

SET @sql = N'SET @px.modify(''insert ' + @xntext +
N'
as first into (//People)[1] '')';

EXEC dbo.sp_executesql @sql, N'@px XML OUTPUT', @px = @x OUTPUT;

SELECT @x;

Alternatively you could cast the result of your original insert to a string
and perform a REPLACE on it:

DECLARE @x XML;
DECLARE @xntext NVARCHAR(MAX);

SELECT @x = N'<People><Person><Name>Ray</Name></Person></People>';
SELECT @xntext = N'<Person><Name>Tim</Name></Person>
<Person><Name>Jim</Name></Person>';

SET @x.modify (N'insert element Person { sql:variable("@xntext") }
as first into (//People)[1]');

SET @xntext = CAST(@x AS NVARCHAR(MAX));
SET @xntext = REPLACE(REPLACE(@xntext, '&lt;', '<'), '&gt;', '>');
SET @x = @xntext;
SELECT @x;

Of course this is dependent on your original data not containing escaped <
and > signs to begin with. Unfortunately there's no good way to do this
"right" with XML DML on SQL 2005.

"ManOnBar" <tserges@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:7cb8d14b-e56e-4f8f-8d34-c33b8a80f820@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am trying to use XQuery in SQL Server 2005 to insert a block of xml,
2 people into an existing XML document. In order to use
modify('insert... I cannot use the type XML, but the Varchar creates
improper tags &lt; instead of < , etc... how can I do this? I even
thought Dynamic SQL, but cannot see it working with code below:(In
reality this block will be much bigger as will the xml doc I am
inserting it into.). I know SQL 2008 will allow the xml datatype in
XQuery, but that does not help me now when I need it.

declare @x xml
declare @xntext varchar(max)
select @x ='<People><Person><Name>Ray</Name></Person></People>
select @xntext = '<Person><Name>Tim</Name></Person><Person><Name>Jim</
Name></Person>'
set @x.modify('insert element Person {sql:variable("@xntext")} as
first into (//People)[1]')
select @x

/*Result is bad
<People>
<Person> <Person>&lt;Person&gt;&lt;Name&gt;Tim&lt;/
Name&gt;&lt;/Person&gt;&lt;Person&gt;&lt;Name&gt;Jim&lt;/Name&gt;&lt;/
Person&gt;</Person>
<Person><Name>Ray</Name...rest is ok....


.

Relevant Pages
Loading