This is another post to mention one more enhancement made in the next version of SQL Server. In SQL Server 2005 itself, the XML data type supports several methods such as value, exist, modify, query and nodes. These methods allow querying as well as modifying an XML instance. The modify method in SQL Server 2008 now also allows DML on the XML instances. Here is an example:
/********************************************************************
Do the declarations and the assignments
*********************************************************************/
declare @a xml = ‘<root><sku ID=”10″/><sku ID=”20″/></root>’;
declare @b xml = ‘<sku ID=”1″/>’;
/********************************************************************
Do the set and the modify
*********************************************************************/
SET @a.modify (‘insert sql:variable(“@b”) as first into (/root)[1]‘);
select @a;
<root>
<sku ID=”1″ />
<sku ID=”10″ />
<sku ID=”20″ />
</root>
