- Introduction
- Creating the Data
- XML Queries
- XML Document Modification
- Summary
XML Document Modification
SQL Server 2005 provides a complete language to manipulate the values in the XML documents (or fragments) stored as the xml data type in SQL tables. The modify method of the xml data type accepts XML Data Modification Language (XML DML) commands (insert, delete, and replace value of) to modify the underlying XML document. You can use the modify method to change the value of an XML variable in a stored procedure, or you could execute UPDATE statements to modify the XML columns in the database.
For example, to modify the Attributes values in the product database, you would use the SQL statement in Listing 7.
Listing 7 Update XML data in the database.
UPDATE Products SET Attributes.modify(’... modification commands ...’) WHERE ... record selection criteria ...
To test the effects of the modify method before applying it to the actual database data, you could use an approach similar to the SQL code in Listing 8, which tests the removal of the Fabric tag from product #2.
Listing 8 Test removal of the Fabric tag from product #2.
DECLARE @X xml SET @X = (SELECT Attributes FROM Products WHERE ProductID = 2) SET @X.modify(’delete //Fabric’) SELECT @X
|
Result |
<root> <Color>Red</Color> <Color>Blue</Color> <Size>XL</Size> <Size>L</Size> <Size>M</Size> </root> |
The modify method supports three XML DML commands:
- The insert command inserts an XML fragment into the specified position in the XML document.
- The delete command (illustrated earlier) deletes the results of an XPATH expression from the XML document. You can use it to delete XML element nodes (tags), attribute nodes (tag attributes), or text nodes (tag values).
- The replace value of command can replace the value of an attribute or text node with the specified string.
For example, if you want to insert an Image tag in product #2 (the T-shirt) indicating the image printed on it, you could use the SQL UPDATE statement in Listing 9.
Listing 9 Insert Image tag into attributes of product #2.
UPDATE Products SET Attributes.modify(’ insert <Image>Comics character</Image> as first into (/root)[1]’) WHERE ProductID = 2
To verify the successful modification, retrieve the whole Attributes column with the SELECT statement in Listing 10.
Listing 10 Retrieve attributes of product #2.
SELECT Attributes FROM Products WHERE ProductID = 2
|
Attributes |
<root> <Image>Comics character</Image> <Color>Red</Color> <Color>Blue</Color> <Size>XL</Size> <Size>L</Size> <Size>M</Size> <Fabric>Cotton</Fabric> </root> |
We’ll conclude this section with a replace value of example. Let’s assume that our marketing department has decided that all the red T-shirts (which we’ll identify by the presence of the Fabric tag) will be sold as being "crimson." The SQL statement in Listing 11 will implement their request.
Listing 11 Replace Red with Crimson.
UPDATE Products SET Attributes.modify(’ replace value of (//Color[text() = "Red"]/text())[1] with "Crimson" ’) WHERE Attributes.exist(’//Fabric’) = 1
The XML DML command is yet again complex due to the singleton requirement:
- The //Color expression selects all Color tags.
- The //Color[text() = "Red"] expression selects all Color tags with the value Red.
- Because the target of the replace value of command is the text node, we have to specify it with the //Color[text() = "Red"]/text() expression.
- Due to the singleton requirement, we have to surround the XPATH expression with parentheses and add the [1] selector.
- Now that we’ve finally selected a single text node that has to be modified, we can set its value to Crimson.