Yesterday we discussed the changes to XQuery by allowing LET statements. Today the last XML enhancement is an "enhancement" to XML DML.

XML Data Manipulation Language (DML) is specific to MS SQL Server 2005 and 2008. It is a very useful feature that allows you to insert nodes to an existing XML instance, delete an element, or replace the value of (sort of an edit.) It works like this:

First let's create a table with an XML data type and insert a piece of XML into it:

Create table XMLTest1
    (
    Speaker_ID int primary key,
    SpeakerXML xml not null
    )

GO

Insert into XMLTest1
Values
(
    1,
    '
    <classes>
        <class name="Writing Secure Code for ASP .NET " />
        <class name="Using XQuery in SQL Server 2008" />
        <class name="SQL Server and Oracle Working Together" />
    </classes>
    '
    )

Now to insert an element into this table it is pretty easy, just use the XML modify method of the XML data type. Since our XML instance is in a SQL Server table we have to use the standard SQL UPDATE and SET syntax with a WHERE clause to get to the piece of XML in our table we are concerned with. You can see how easy it is to add an element to the table here:

Update XMLTest1
Set SpeakerXML.modify(
'insert
<class name="Using Linq to SQL" />
into /classes[1]'
)
Where Speaker_ID=1

If we select * from XMLTest1 WHERE Speaker_ID=1 the XML column looks like this now:

<classes>
  <class name="Writing Secure Code for ASP .NET " />
  <class name="Using XQuery in SQL Server 2008" />
  <class name="SQL Server and Oracle Working Together" />
  <class name="Using Linq to SQL" />
</classes>

SQL Server 2008 has an enhancement to this. You can substitute the XML element with a variable. For example let's create a variable here:

DECLARE @newElement xml
Set @newElement='<class name="ASP.NET Scalability"/>'

Now let's do the same insert as before, however, we will use the @newElement variable. This will enable you to select XML from other areas and stick it into a variable and then insert it into another XML instance. Here is the final piece:

Update XMLTest1
Set SpeakerXML.modify(
'insert
sql:variable("@newElement") 
into /classes[1]'
)
Where Speaker_ID=1

If we select * from XMLTest1 WHERE Speaker_ID=1 the XML column looks like this now:

<classes>
  <class name="Writing Secure Code for ASP .NET " />
  <class name="Using XQuery in SQL Server 2008" />
  <class name="SQL Server and Oracle Working Together" />
  <class name="Using Linq to SQL" />
  <class name="ASP.NET Scalability" />
</classes>

Not the greatest of new features, but a handy dandy feature to say the least.