Insert an XML variable into another

If you are using SQL Server 2008 November CTP, you can insert one XML variable into anther using the XQuery insert operator. Here is an example:

-- declare an XML variable

DECLARE @x XML

SET @x = '<Root></Root>'

 

-- create another XML variable

DECLARE @t XML

SELECT @t = (             SELECT TOP 3 name FROM sys.tables FOR XML AUTO)

 

-- insert the second XML variable to the first one

SET @x.modify( '

            insert sql:variable("@t")

            as last into (/Root)[1] ' )

 

-- Let us check the results

SELECT @x

 

/*

<Root>

  <sys.tables name="spt_fallback_db" />

  <sys.tables name="spt_fallback_dev" />

  <sys.tables name="spt_fallback_usg" />

</Root>

*/