Sqlserver and XML

Insert an XML variable into another

Posted on January 4th, 2008 | by Admin |

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>

*/