Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

XML Type Methods
Up to this point, the examples have shown the XML datatype being used as just a blob of data, but this is where the real power of the XML data type shows itself. The XML data type supports several methods that can be called using the UDT dot syntax (myXml.operation()) syntax. The supported operations are listed in Table 1.

Table 1: XML Datatype methods.
Method Name Description
query Performs a query against the XML and returns the results of the query
exists Performs a query against the XML and returns the value of 1 if there was a result
value Evaluates a query to return a simple value from the XML
modify Executes a change operation on the XML document in place
nodes Allows you to break apart XML into a table structure

For the following sections, you will use a table called Team that contains a row for every team name. In each row, there is a TeamDoc row that contains XML about the team:

CREATE TABLE Team ( TeamID int identity not null, TeamDoc xml DEFAULT '<Team />' NOT NULL )

In the examples, assume that the following XML document exists in the Braves row of the table:

<Team name="Braves"> <Players> <Pitcher name="John Smoltz" role="Closer"/> <Pitcher name="Russ Ortiz" role="Starter" /> <ThirdBase name="Chipper Jones" role="Starter" bats="switch"/> </Players> </Team>

Query Method
You can use XML as a column in tables and views, in T-SQL statements, or as parameters of stored procedures.
The query method allows you to specify an XQuery or XPath expression to evaluate. The result of the query method is an XML data type object. The specific syntax of the query method is:


The first parameter is always an XQuery expression. The following example uses a query to return an XML document with information about each team's pitcher:

SELECT TeamDoc.query('/Team/Players/Pitcher') FROM Team

This produces the following results:

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

The query method allows you to find and return nodes lists that match the XQuery expression you specify. The real power of the query method comes from the XQuery syntax, which is covered in detail later in this article.

Exist Method
The exist method is similar to the query method except that it is used to determine whether a query yields any results. The syntax for the exist method is:


When you use the exist method, it evaluates the query and returns the value of 1 if the query yields any results. For example, this query finds the rows in the team table where the TeamDoc field has starting pitchers:

-- Simple Exist clause SELECT Count(*) FROM Team WHERE TeamDoc.exist( '/Team/Players/Pitcher[@role="Starter"]') = 1

Value Method
There are times when you do not want to interpret a whole query's result just to get a scalar value: this is where the value method is helpful. The value method is used to query the XML and return an atomic value. The syntax for the value method is:

value(XQuery, datatype)

Use the value method when you want to get a single scalar value from the XML. You must specify the XQuery statement and the datatype you want it to return and you can return any datatype except the XML datatype. For example, if you want to get the name of the first pitcher on every team, you can write the query like this:

-- Do a Query to get an individual value SELECT TeamDoc.value( '(/Team/Players/Pitcher/@name)[1]', 'nvarchar(max)') AS FirstPitcher FROM Team

This query results in the scalar value of the first pitcher for each team returned in the result:

FirstPitcher ------------------------------ John Smoltz (1 row(s) affected)

The difference between the query and value methods is that the query method returns an XML datatype that contains the results of the query, and the value method returns a non-XML datatype with the results of the query. The value method can return only a single (or scalar) value. You will get an error if you try to create an XQuery expression that returns more than one value using the value method.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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