Systems Engineering and RDBMS

Modifying XML Data in SQL Server

Posted by decipherinfosys on April 14, 2009

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.

One Response to “Modifying XML Data in SQL Server”

  1. […] e) modify() method: This method is new in SQL Server 2008.   As the name also suggests, this method is used do DML operations against a XML instance.  We had covered this here and here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: