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 6

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.

Using XQuery
Now that XML is in the database, you will want a smart way to query it. Earlier, you saw that you could use the query method of the XML datatype to search through instances of XML. In those examples, you used an XML language called XPath to search the document. XPath is a good starting point for asking simple questions of XML documents, but has limitations when asking robust questions. To this end, the World Wide Web Consortium has a working group that created a true query language for XML called XML Query (or XQuery for short).

XQuery is a language for querying XML documents. The topic of XQuery is big enough for its own book, but I'll give you some basic information to get you started.

Every XQuery is an expression language. What this means is that every XQuery expression must evaluate to a result. Therefore, it is valid to write any expression that evaluates back to a result. That makes these valid XQuery expressions even though they do not do anything to search XML:

   SELECT @doc.query('"Hello"')
   SELECT @doc.query('15 * 10')
XQuery has adopted most of the XPath expression language to describe paths within XML documents. So the examples above were valid XQuery expressions, even though they looked like XPath. For example, to get the list of players, you used:

   SELECT @doc.query('/Team/Players')
But to really see the power of XQuery, you want to use it to answer questions. For example, you may want to create a set of XML nodes called <Player/> that contain starting pitchers for the team:

   SELECT @doc.query('
     for $b in 
     /Team/Players/Pitcher[@role = "Starter"]
     return (
       <Player name="{$b/@name}"></Player>)' 
This XQuery expression uses several different pieces of XQuery to create the expected results. It uses the for...in block syntax to say, go through all the nodes that the path expression (/Team/Players/Pitcher[@role = "Starter"]) returns (which is stored in a variable called $b), and create a <Player/> tag for each of the nodes. XQuery inserts a name attribute into the Player tag and calculates the name attribute's value by getting the name attribute from the node ($b). This query results in an XML fragment that looks like this:

   <Player name="Russ Ortiz" />
   <Player name="John Thomson" />
   <Player name="Mike Hampton" />
   <Player name="Horacio Ramirez" />
Although this query is not rocket science, it does hint at some of the power of XQuery. Now that you've got a quick introduction, let's look at some of the components of an XQuery expression.

The syntax of the XQuery language is made up of several parts:

  • Prolog
  • Iteration
  • Path Expressions
  • Conditional Expressions
  • Quantified Expressions
XQuery Prolog
The prolog is a place to specify any namespaces to declare for the XQuery expression. The entire prolog prefaces the body of the query. For example, you can specify the default namespace of the expression, like so:

   SELECT @doc.query('
      declare namespace T="http://yukon/teams";
      return /T:Team/T:Players
   ') as Result
The purpose of declaring the namespace is to allow you to alias it across the query. You can define multiple namespaces within the prolog. In addition, the prolog can also contain a definition for the default namespace. For example:

   SELECT @doc.query('
      declare default element namespace = 
      return /Team/Players
   ') as Result 
Other than namespaces, you can also include XML schema imports in the prolog. If you are working with typed XML, their schemas are automatically imported by the engine. For additional schemas, you can use the import schema syntax:

   SELECT @doc.query('
      import schema 
      return  /Team/Players
   ') as Result

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