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

Thanks for the answer. I was missing utilizing the OUTPUT portion of
the dynamic SQL to declare the xml as you pointed out, thanks. I did
decide since my output would never have < or > to use your
alternative replace as the final solution. I did need the final output
in xml and did the following. I need to test if other & like &
might create difficulty and force me back to dynamic sql.

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]')
set @xntext = convert(nvarchar(max),@x)
SET @xntext = REPLACE(REPLACE(@xntext, '&lt;', '<'), '&gt;', '>')
set @x = @xntext


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" <tser...@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....- Hide quoted text -

- Show quoted text -

.

Relevant Pages
Loading