XQuery in soapUI Part 2: Where and Order By Clauses

In the last post we took a look at the basic syntax of XQuery expressions in soapUI, including for and return clauses.  In this post we'll look at the optional where and order by clauses, which can add more complexity to expressions.

The XQuery where clause is very similar to the SQL where clause; it allows you to provide an additional filter on the base data set selected in your for clause.  In the last post we created an XQuery example using the Euro 2012 web service that retrieved data for each player returned in the AllPlayersNames test request.  Let's say we wanted to narrow down the list of players to include only members of the German national team.  We could adjust the query by adding a where clause just after the for clause:

In this case the where clause (where $x/m:sCountryName = 'Germany') whittles down the full set of tPlayerNames elements returned by the for clause, keeping only those that satisfy the condition of the where clause (in this case, those where the sCountryName child element equals "Germany").

Many of the same functions available in XPath expressions can also be used in XQuery expressions; the following is a valid where clause, for example:

where starts-with($x/m:sName/text(),'M')

The starts-with function takes two arguments and returns true if the first argument (in this case, the value of the sName element) begins with the second argument ("M").

In many cases, the choice to use a where clause may come down to preference; it's possible to achieve the same end result by using predicates with your for clause XPath expression.  This XPath expression in the for clause returns only German players (equivalent to the first where clause example):

for $x in //m:tPlayerNames[m:sCountryName='Germany']

Likewise, the following is equivalent to the second example, returning only those players with names starting with "M":

for $x in //m:tPlayerNames[starts-with(m:sName/text(),'M')]

For readability, however, you may find using where clauses preferable to the extended for clauses.

If a web service returns data in an arbitrary order (i.e., changing with each request), it presents a problem for XQuery assertions, which check for an exact match-- order absolutely matters.  For situations like these, an XQuery order by clause is necessary.  As you might expect, the order by clause is used to sort XQuery results by some node's value, ensuring results are always in the same order for evaluation by the assertion.  The order by clause is added before the return clause; here's an example of a complete XQuery expression including a for, where, order by, and return clause:

This example selects tPlayerNames elements from the response (the for clause), filters out only those elements with sName child element values that start with "M" (the where clause), orders the results by the value of the sName child element (the order by clause), and finally returns the values of the sName and sCountryName child elements for each tPlayerNames element, formatted within identifying tags (the return clause).

It's also possible to specify multiple values for the order by clause, establishing primary, secondary, etc. sort criteria.  For example, you can change the order by clause in the example above to the following to use sCountryName as the primary sort value and sName as the secondary sort value:

order by $x/m:sCountryName/text(), $x/m:sName/text()

The beginning of the results from this modified XQuery expression look like this:

As expected, results are sorted by country first, with players from the same country further sorted by name.

As usual when discussing XML-related topics, I highly recommend checking out W3Schools.com (available from the Useful Links section on the right) to find more information about using XQuery expressions.