Browse DevX
Sign up for e-mail newsletters from DevX


Making Sense of the XML DataType in SQL Server 2005 : Page 3

In SQL Server 2005, XML becomes a first-class data type. Developers can make minor remote modifications to stored XML documents easily, taking advantage of new support for XML schema-based strong typing, and server-based XML data validation.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Modify Method
Although the XQuery standard does not provide a mechanism for updating XML, SQL Server 2005 supports a way of modifying parts of an XML object in place. This means that you do not have to retrieve an entire XML document just to make changes. To modify a document in place, you use a combination of the modify method and SQL Server 2005's new XML Data Modification Language (XML DML).

The syntax for the Modify method is:


The Modify method takes only a single parameter, the XML DML statement. XML DML is similar, but not identical, to SQL's insert, update and delete syntax. For example, you can modify the XML by using the insert DML statement:

SET @doc.modify(' insert <Pitcher name="Jaret Wright"/> as last into (/Team/Players)[1] ')

You can do the same thing to modify an XML column by calling modify in an UPDATE statement:

-- Modify an XML doc without replacing -- it completely! UPDATE Team SET TeamDoc.modify(' insert <Pitcher name="Jaret Wright"/> as last into (/Team/Players)[1] ') WHERE TeamDoc.exist('/Team[@name="Braves"]') = 1

Notice that the SET clause in this UPDATE statement does not follow the SET x = y pattern that you may be used to from writing SQL in the past. That syntax assumes that you will provide a complete new value to replace the old, which, in the case of XML, means a completely new document to replace the old. When using the XML type, the modify method changes the original document in place. There's no need to generate a completely new and separate document, or for SQL Server to attempt to replace an entire document with every change. The SET syntax in the example reflects the more efficient approach of updating a document in place.

There are three XML DML statements: insert, update, and delete. Not surprisingly, they are used to insert, update, and delete parts of an XML object. The syntax for each is similar to SQL, but with some definite differences.

Here is the syntax for the insert statement:

insert InsertExpression ( {{as first | as last} into | after | before} LocationExpression )

Immediately following the insert statement is the XML that you want to insert (InsertExpression). Next you specify how you want the XML inserted. Your choices are into, after, or before. The before and after clauses instruct the database to insert the InsertExpression as a sibling to the LocationExpression. The use of before or after specifies whether to insert it before or after the LocationExpression:

SET @doc.modify(' insert <Pitcher role="Starter" name="Jaret Wright"/> before (/Team/Players/Pitcher)[1] ')

The into clause inserts the InsertExpression as a child of the LocationExpression. The optional clauses of as start and as last are used to specify the position of the insertion within the children:

-- Insertion within Team SET @doc.modify(' insert <Pitcher role="Starter" name="Jaret Wright"/> into (/Team/Players)[1] ') -- Insertion within Team, specifying it should -- be inserted as the last element SET @doc.modify(' insert <Pitcher role="Starter" name="Jaret Wright"/> as last into (/Team/Players)[1] ')

The syntax for the delete statement is very straightforward:

delete LocationExpression

The LocationExpression specifies what to delete from the XML data. For example, to delete all the Pitchers:

SET @doc.modify('delete /Team/Player/Pitcher')

Because the query specifies all pitcher elements, they will all be deleted. If you want to delete just a single element, you can specify identifying attributes. To delete just the pitcher named John Smoltz, you write the delete statement like so:

SET @doc.modify(' delete /Team/Players/Pitcher[@name="John Smoltz"] ')

You can also tell the delete statement to remove an individual attribute. For example, to delete the role attribute for the pitcher named John Smoltz, the XML DML looks like this:

SET @doc.modify(' delete /Team/Players/Pitcher[ @name="John Smoltz"]/@role')

Lastly, the replace value statement describes changes to make to the XML data. The syntax of the replace value statement is:

replace value of OriginalExpression with ReplacementValue | if

The replace value statement is used to change discrete values in the XML. The only discrete values possible are the literal contents of a tag or the value of an attribute. The OriginalExpression must resolve to a single node or attribute. The ReplacementValue is usually a literal value to replace. Replacing the literal contents of a node requires the XQuery expression using the text() function to specify that you want to replace the text of a node. For example, to replace the inner text for a pitcher, you write the modify statement like this:

DECLARE @doc xml SELECT @doc = ' <Team name="Braves"> <Players> <Pitcher name="John Smoltz" role="Closer"> With team since 1989 </Pitcher> </Players> </Team>' SET @doc.modify(' replace value of (/Team/Players/Pitcher[ @name="John Smoltz"]/text())[1] with "May start in 2005" ')

Modifying an attribute is straightforward: you just need the XQuery expression to resolve to a single attribute. For example, to replace the value of the role attribute for the pitcher named John Smoltz with "Starter," do this:

SET @doc.modify(' replace value of (/Team/Players/Pitcher[ @name="John Smoltz"]/@role)[1] with "Starter" ')

The replace value syntax also supports conditional replacement by using the if…then…else syntax within the with clause of the replace value statement. For example, to replace John Smoltz's role to Starter if he is a Closer, but change it to a Closer if he is not a Starter, you could write the code:

SET @doc.modify(' replace value of (/Team/Players/Pitcher[ @name="John Smoltz"]/@role)[1] with ( if (/Team/Players/Pitcher[ @name="John Smoltz"]/@role = "Closer") then "Starter" else "Closer" ) ')

Nodes Method
The purpose of the nodes method is to allow normalizing of a set of nodes returned by a query into a set of rows in a table-like result set. The syntax of the nodes method is:

nodes (XQuery) Table(Column)

The XQuery is the expression that picks the nodes to be exposed as a result set. The Table and Column are used to specify names in the result set. Note that you can only have one column and that it is automatically of type XML. For example, to query to get each of the pitchers, write the code like this:

DECLARE @doc xml SELECT @doc = ' <Team name="Braves"> <Players> <Pitcher name="John Smoltz" role="Closer"> With team since 1989 </Pitcher> </Players> </Team>' SELECT Team.player.query('.') as Pitcher FROM @doc.nodes('/Team/Players/Pitcher') Team(player)

This results in a single result set containing rows for each of the Pitchers' elements:

Pitcher -------------------------------------------- <Pitcher name="John Smoltz" role="Closer" /> <Pitcher name="Russ Ortiz" role="Starter" /> (2 row(s) affected)

Notice that you used the query method to return these nodes in the result. The reason for this is the results of a nodes method may only be referred to by the XML methods (query, modify, delete, and update) or IS NULL and IS NOT NULL statements.

Gone are the days of needing to pull the entire XML document out of the database as a string, parsing it, making changes, and replacing the entire document.
More ordinarily, you may use the nodes method to break apart XML into a more useful result. For instance, you could get the players' nodes by using the nodes method, and then retrieve them with the value method to get the individual values as scalar data:

SELECT Team.player.value( './@name', 'nvarchar(10)') as Name, Team.player.value('./@role', 'nvarchar(10)') as PlayerRole FROM @doc.nodes('/Team/Players/Pitcher') Team(player)

This results in the following result set:

Name PlayerRole --------------- --------------- John Smoltz Closer Russ Ortiz Starter (2 row(s) affected)

Thanks for your registration, follow us on our social networks to keep up-to-date