Browse DevX
Sign up for e-mail newsletters from DevX


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

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

XQuery Path Expressions
XQuery adopts most of the XPath expression language to accomplish specifying paths. Therefore most of the XPath expression language is perfectly appropriate for use in XQuery. In the simple case, you can use XPath raw as the query like this:

SELECT @doc.query('/Team/Players')

You can use axis specifiers as you would in XPath as well:

SELECT @doc.query('/Team/child::Players')

Or you can use it to retrieve values from a document:

SELECT @doc.value('/Team[@name][1]', 'varchar(50)')

Lastly, most of XPath's function library is part of XQuery. Therefore you can use calculations that you are already familiar with from XPath. For example, to get the count of players on the team, you can do this:

SELECT @doc.query('count(/Team/Players/*)')

Conditional Expressions
XQuery includes the if...then...else construct to allow for conditional expressions. This construct allows you to make tests and branch based on specific results. For example, if you want to return a result of whether the roster is full or not, you can test the team by using a conditional expression:

SELECT @doc.query(' if (count(/Team/Players/*) < 25) then "Need Players" else "Roster Full" ')

You can nest conditional expressions to do more elaborate testing. For example, you can add a test to see if any players exist, and then you can report that you have No Players, instead of just saying Need Players:

SELECT @doc.query(' if (count(/Team/Players/*) < 25) then if (count(/Team/Players/*) = 0) then "No Players" else "Need Players" else "Roster Full" ')

Conditional expressions also allow you to compound several tests together using and, or, and parentheses. This next example shows testing for both 25- and 40-man rosters:

SELECT @doc.query(' if ((count(/Team/Players/*) = 25) or (count(/Team/Players/*) = 45)) then "Roster Full" else "Need Players"')

Quantified Expressions
There are times where you need to test whether all or some results match some specific criteria. XQuery allows for this with Quantified Expressions, which use the following syntax:

( some | every ) <variable> in <Expression> satisfies <Expression>

With this syntax you can test a set of nodes based on a criteria. In this example, you are testing to see if all of the players are starters or not:

SELECT @doc.query(' if (every $player in /Team/Player/* satisfies $player/@role="Starter") then "We have all Starters" else "We have Starters and others" ')

You can use the some clause to specify that at least one needs to pass the test instead of requiring all to pass the test (as seen with the use of every above). You can see this work in the example below:

SELECT @doc.query(' if (some $player in /Team/Player/* satisfies $player/@role="Starter") then "We have some Starters" else "We no starters" ')

One of the most common uses for XQuery is to iterate through all the results of a node test and perform some work. To do this, XQuery supports a set of clauses that they shorten to FLWOR (pronounced flower). FLWOR stands for the different pieces of the iteration syntax FOR, LET, WHERE, ORDER BY, RETURN.

As you saw in the earlier example, you can use the most common parts of this syntax (the for and return clauses) to create results that concatenate results. For example, here you want to create a node for each player on the team:

SELECT @doc.query(' for $b in /Team/Players/Pitcher[@role = "Starter"] return (<Player name="{$b/@name}"></Player>) ')

This creates a variable called $b for each pitcher that is a starter. Then it returns a Player element with the name of the player embedded in it.

You can further enhance the query by using the where clause to specify a condition that each node must pass to become part of the node set. For example, you can make sure that every player has a name by doing this:

SELECT @doc.query(' for $b in /Team/Players/Pitcher[@role = "Starter"] where (count($b/@name) > 0) return (<Player name="{$b/@name}"></Player>) ')

Lastly, you can further improve this query by sorting the pitchers by name using the order by clause:

SELECT @doc.query(' for $b in /Team/Players/Pitcher[@role = "Starter"] where count($b/@name) > 0 order by ($b/@name) return (<Player name="{$b/@name}"></Player>) ')

You may have noticed that I skipped the let clause. I skipped it because SQL Server 2005 doesn't support the let clause.

XQuery Extension Functions
To enable better integration with the database engine, Microsoft includes two functions that extend the functionality of XQuery by giving access to columns and local variables in the SELECT clause. These functions are called sql:column and sql:variable. For example, to include the TeamID in the <Player/> elements you're creating, you can call sql:column with the name of the column in the SELECT statement:

SELECT TeamID, TeamDoc.query(' for $b in /Team/Players/Pitcher where count($b/@name) > 0 order by ($b/@name) return (<Player team="{sql:column("TeamID")}" name="{$b/@name}"></Player>)') FROM Team

In addition, you can use the sql:variable function to have access to local variables. For example, if you create a local variable with the date of the roster, you can insert it into the output XML by using the sql:variable clause:

DECLARE @today datetime SET @today = '12/31/2004' SELECT TeamID, TeamDoc.query(' for $b in /Team/Players/Pitcher where count($b/@name) > 0 order by ($b/@name) return (<Player dt="{sql:variable("@today")}" name="{$b/@name}"></Player>)') FROM Team

For more information on XQuery see the XQuery page on the w3c.org Web site, and the XQuery specification.

Storing XML has become a mainstay of many software architectures these days. SQL Server 2005 follows this trend by including a real XML data type, but XML is not just structured storage in this case. It represents a way to extend the SQL type system with a well known and open type system: XML Schema. By treating XML as a mature type, the database can deal with XML in an efficient way.

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. SQL Server 2005 lets you do searches, additions, changes and deletions of parts of a document in-place. This represents a great leap in ease and performance when using XML in the database.

Shawn Wildermuth is a Microsoft C# MVP and founder of Wildermuth Consulting Services, LLC. He's a speaker on the INETA Speakers Bureau and at national conferences. Shawn authored the books, Pragmatic ADO.NET (2001) and the upcoming Prescriptive Data Architectures, both for Addison-Wesley. He has written for a variety of magazines and Web sites, including MSDN, MSDN Online, DevSource, and others. Shawn enjoys building data-driven software for more than twenty years.<.i>
Thanks for your registration, follow us on our social networks to keep up-to-date