Tag Archives: SQL - Page 2

xQuery and SQL Server – a Basic Look

The database table which I am using in this post: xmlDB.zip

xQuery comes in quite handy when it comes to querying Xml fields in SQL Server 2005 or 2008. SQL Server supports many xQuery language features, and its Xml data type provides a variety of functions to enable the use of xQuery.

To demonstrate, I wanted to keep things very simple and created a relation with the following schema:

schema
The ItemId is just an artificial identifier which performs the role of primary key for each tuple. The SalesRep field (which is of the Xml type) contains information about the sales reps. The Xml fragment stored in that field is similar to the ones which I have been querying in previous posts.

Starting off with a simple example, I have used the query function of the Xml type to return the Name element from the SalesRep field for each tuple:

SELECT SalesRep.query('//Name') as [RepName]
FROM Employees

which returns:
repName
To return the actual text nodes of particular Name elements, we can use:

SELECT SalesRep.query('//Name/child::text()') as [RepName]
FROM Employees
WHERE Office = 'Brisbane'

If we want to use an xQuery query in a WHERE clause, we need to use the exist function of SQL Server’s Xml type. For example, we may want to return every REP element with an ID of 8:

SELECT SalesRep.query('/REP') as [Rep]
FROM Employees
WHERE SalesRep.exist('/REP/ID/child::text()[. = "8"]') = 1

The way we do that, is to use a predicate in the WHERE clause. The query has asked for every element that is returned when the predicate is true i.e. when the exist function equals 1 (1 being true).

We may want to find those sales reps whose Sales were less than $10,000:

SELECT SalesRep.query('/REP') as Rep
FROM Employees
WHERE SalesRep.exist('/REP/Sales/child::text()[xs:integer(.) < 10000]') = 1

This is just a taste of what can be done in terms of utilizing xQuery to query XML fields in SQL Server databases. Such queries are much faster, and more efficient, than ones which cast the Xml field to NVARCHAR and then perform a LIKE %xyz% in the WHERE clause. Cool, eh?!