Modifying XML Data in SQL Server

In response to one of our posts, a reader asked us to provide some examples on how to go about modifying XML data using XQuery in SQL Server.  SQL Server provides an extension method to XQuery called the modify() method.  This can be used to do deletes, inserts or updates.  The modify() method includes commands like insert, delete and “replace value of” in order to be able to make such changes.  Let’s follow this up with an example.  We will use the same employee/department example that we have used before in many of our posts.

declare @x xml
select @x =
'<Dept>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date></hire_date>
</Dept_Emp>
</Dept>'

Let’s now try to insert an additional employee record into this XML.

declare @new xml
select @new =
'<Dept_Emp>
<dept_id>30</dept_id>
<emp_name>Marcia Hayden</emp_name>
<hire_date>04/13/2009</hire_date>
</Dept_Emp>'

set @x.modify(‘insert sql:variable(“@new”) as last into (/Dept)[1]‘)

select @x

What we have done here is that we have inserted a new employee record into the XML.  And by specifying “last into”, we are telling it to add the record at the bottom og the XML.  Here is how the output looks like after doing this:

<Dept>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date />
</Dept_Emp>
<Dept_Emp>
<dept_id>30</dept_id>
<emp_name>Marcia Hayden</emp_name>
<hire_date>04/13/2009</hire_date>
</Dept_Emp>
</Dept>

Now, let’s try to delete that record that we just added.

SET @x.modify ('delete /Dept/*[8]')

Here, we have specified that we want the eighth record to be removed which was the most recent record that we had added.

Now, let’s use the “replace value of” command to update the hiring date of the employee “Diana”:

SET @x.modify ('replace value of (/Dept/Dept_Emp/hire_date[1]/text())[6]
with "04/14/2009"')

SELECT @x

<Dept>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>04/14/2009</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date />
</Dept_Emp>
</Dept>

Now, when I tried to to do this with the data record for the employee Ramesh i.e. tried to update the hire_date for him, it did not work.  Apparently, “replace value of” works only if the element is not empty.  There must be a way – will dig in more and will post an addendum when I figure it out.

Resources:

• BOL entry on the modify() method – here.
• Alex Homer’s articles on 15seconds.com on XML DML support in SQL Server – Part 1, Part 2 and Part 3.
• Limitations of XML DML in SQL Server 2008 – here.
• XML DML and concurrency issues discussion @ MSDN forum – here.

---

Possibly related posts: (automatically generated)

• XML DML Enhancement in SQL Server 2008
• Some more XML enhancements in SQL Server 2008
• Using nodes() method to de-construct XML as relational data