XML DML INSERT operation in SQL Server

Recently in one of our project the requirement is to insert element within existing XML which is a column(XML Data type) in a table. In other words we have to insert dynamic xml in another xml. I hear the similar requirement in most of the forums...

We can use one of the XML DML operation "INSERT" - Inserts one or more nodes identified by Expression1 as child nodes or siblings of the node identified by Expression2.

Using this Insert operation we can insert only a static nodes as shown below:

-- Declare Local Variables
DECLARE @MainXML XML
DECLARE @NewXML XML

-- Sample XML with employee information
SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

-- Inserting static xml
SET @MainXML.modify('insert (<employee><fname>Test</fname></employee>) as last into (/employees)[1]')

-- To verify the reault
select @MainXML

Now if we want to insert an XML which is available within the local variable @NewXML:

-- Assigning the sample xml to the local variable
SET @NewXML = '<employee><fname>Test</fname></employee>'

Now if we try to insert this xml from the local variable using sql:variable:

SET @MainXML.modify(' insert {sql:variable("@NewXML")} as last into (/employees)[1]')

We will get an error message indicating:

XQuery [modify()]: An expression was expected

In SQL Server 2005 the work around will be:

1. Dynamic Query
2. Combination of the XML DML Operations

Here I am not concentrating on the Dynamic Query approach as it is pretty simple to implement and it may not be a best approach when compared to other.

Following set of queries using DML operation, we can insert XML data from a local variable:

-- Appending both the Main XML and New XML to insert into Main XML with a root node
SET @MainXML = '<root>' + CAST(@MainXML AS VARCHAR(MAX)) + '<new>' + CAST(@NewXML AS VARCHAR(MAX)) + '</new>' + '</root>'

-- Moving employee element from new element to main xml as part of employees element
SET @MainXML.modify(' insert (//new/employee) as last into (/employees)[1]')

-- Deleting the new element
SET @MainXML.modify(' delete (//new)')

-- Steps to move employee element to the end and to delete the root element
SET @MainXML.modify(' insert (//root/employees) after (/root)[1] ')
SET @MainXML.modify(' delete (/root)')

-- Selecting the xml for final verification
SELECT @MainXML

In Summary following is the sample Script in SQL Server 2005 to insert a dynamic XML into another XML:

DECLARE @MainXML XML
DECLARE @NewXML XML


SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

SET @NewXML = '<employee><fname>Test</fname></employee>'

SET @MainXML = '<root>' + CAST(@MainXML AS VARCHAR(MAX)) + '<new>' + CAST(@NewXML AS VARCHAR(MAX)) + '</new>' + '</root>'

SET @MainXML.modify(' insert (//new/employee) as last into (/employees)[1]')

SET @MainXML.modify(' delete (//new)')

SET @MainXML.modify(' insert (//root/employees) after (/root)[1] ')
SET @MainXML.modify(' delete (/root)')

SELECT @MainXML

These many steps are not required for SQL Server 2008 as it supports to insert the dynamic XML directly into another XML. Following is the sample script for SQL Server 2008:

DECLARE @MainXML XML
DECLARE @NewXML XML


SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'


SET @NewXML = '<employee><fname>Test</fname></employee>'

-- One statement in SQL Server 2008 to do all the operation what we mentioned above for SQL Server 2005
SET @MainXML.modify(' insert {sql:variable("@NewXML")} as last into (/employees)[1]')

select @MainXML

This we implemented in most of our applications, hope it will be useful to most of the folks...