Modifying XML Data - SQL Server 2008

SQL Server includes an extension method to XQuery, the modify() method. The modify() method allows you to modify parts of your XML data. You can add or delete subtrees, replace values, or perform similar XML modifications. The modify() method includes Data Manipulation Language (DML) commands such as insert, delete, and replace value of.

SQL Server supports piecemeal XML modification. This means that when you modify your XML document, such as adding elements, changing attributes, or deleting elements, SQL Server performs just the necessary operations on the XML rather than replacing the entire XML document.

Inserting an Element

With the insert command, you can insert XML as the first or last element. You can specify whether to insert the XML before, after, or as a direct descendant of an existing XML element. You can also insert attributes using this method. Without needing to modify the entire document, you can use insert to easily put XML into existing documents. The insert command also allows the user to use a scalar T-SQL variable of type xml within the insert expression. For example, consider the following shopping list:

<List>
<Item>Bananas</Item>
<Item>Apples</Item>
</List>

Suppose we want to insert a value stored in a T-SQL variable into this list as another item. This can be accomplished using the following code:

DECLARE @GroceryList xml
SET @GroceryList='<List><Item>Bananas</Item><Item>Apples</Item></List>'
DECLARE @AdditionalItem xml
SET @AdditionalItem='<Item>Dog Food</Item>'
SET @GroceryList.modify
('insert sql:variable("@AdditionalItem") as last into (/List)[1]')
SELECT @GroceryList

The result of the @GroceryList variable is as follows:

<List>
<Item>Bananas</Item>
<Item>Apples</Item>
<Item>Dog Food</Item>
</List>

Deleting an Element

With the delete command, you can delete XML elements or attributes from your XML document. For example, you might remember that you don’t need dog food after all.

SET @GroceryList.modify ('delete /List/Item[Dog Food]')

This invocation of the delete command within the modify method deletes the dog food item that was added to your grocery list in the preceding section.

Changing a Node Value

The replace value of command allows you to replace a node with a new value that you specify. You can replace only a single node at a time; you cannot select multiple nodes.

The following example inserts and changes the favoritecolor element for person number 3 in the XML document:

--First insert a new value
UPDATE xmltblnew SET people.modify(
'insert <favoriteColor>Red</favoriteColor>
as last into (/people/person[3])[1]')
WHERE pk=1
GO
--Select the data to show the change
SELECT * FROM xmltblnew
GO
--Modify the value
UPDATE xmltblnew SET people.modify(
'replace value of (/people/person[3]/favoriteColor[1]/text())[1]
with "Blue"')
WHERE pk=1
GO
--Select the data to show the change
SELECT * FROM xmltblnew
GO
--Now delete the value
UPDATE xmltblnew SET people.modify(
'delete /people/person[3]/favoriteColor')
WHERE pk=1
GO
--Select the data to show the change
SELECT * FROM xmltblnew
GO

Limitations of XML Modification

You have some limitations when you modify your XML, including the following:

  • For typed or untyped XML, you cannot insert or modify the attributes xmlns, xmlns:*, and xml:base.
  • For typed XML only, the attributes are xsi:nil and xsi:type.
  • For typed or untyped XML, you cannot insert the attribute xml:base.
  • For typed XML, deleting and modifying the xsi:nil attribute will fail, as will modifying the value of the xs:type attribute. For untyped XML, you can modify or delete these attributes.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics