This is our fourth post in the XQuery for the SQL programmer series. Today we'll have a closer look at the SQL functions, operators, and conditions used in where clauses. And of course we'll explain how to accomplish the same in XQuery.
Comparisons
All of us are using comparison operators frequently in SQL, especially equality (=). Not surprising, we've been using it already a few times in examples of previous posts of this series.
Select all motorcycles,
[cc lang="sql"]select * from ITEMS where DESCRIPTION = 'Motorcycle'[/cc]
is written in XQuery as follows,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION = "Motorcycle" return $item[/cc]
Looks like = in SQL is the same as = in XQuery, but actually that is not entirely true. In SQL the comparison operators are used to compare two values. In XQuery, the comparisons operate on sequences of values and as such have existential behavior. A few examples of = in XQuery,
- 1 = 2 evaluates to false
- 1 = (1,2) evaluates to true
- 1 != (1,2) evaluates to true
Note the last two comparisons, they illustrate that = and != are not the inverse of each other. Another consequence of the existential behavior, equality is not transitive in XQuery.
- (1,2) = (2,3) evaluates to true
- (2,3) = (3,4) evaluates to true
- But (1,2) = (3,4) evaluates to false!
In XQuery we call these operators (=, !=, <, and >=) general comparisons. In addition XQuery also has value comparisons (eq, ne, lt, le, gt, ge), which are used to compare single values. Although they have a different syntax, these value comparisons are equivalent to the SQL comparison operators. The example above could thus also be written as follows,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION eq "Motorcycle" return $item/ITEMNO[/cc]
Arithmetic operators
A lot of analogy between the arithmetic operators in SQL and XQuery. +, - and * are equivalent. Where SQL uses the / operator for divisions, this is not possible in XQuery. Remember that / is used in path expressions. So XQuery has a div operator.
IN clause
Note the similarity, to some extend, between the IN condition as known in SQL with XQuery's equality general comparison.
[cc lang="sql"]select ITEMNO from ITEMS where DESCRIPTION in ('Motorcycle', 'Bicycle')[/cc]
Can be written in XQuery using a general comparison,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION = ("Motorcycle", "Bicycle") return $item/ITEMNO[/cc]
XQuery doesn't have a dedicated construct like the SQL IN condition. As we will see throughout this post, in XQuery we take advantage of the rich set of built-in functions and operators to accomplish the same as with some of the specific SQL constructs.
LIKE condition
Take for example, the LIKE condition, frequently used in where clauses. XQuery doesn't have the concept of a LIKE condition, but the rich set of functions includes equivalents.
The matches() function has two arguments, the input string and a regular expression against which the input string is matched. Refer to the regular expression syntax specification for all the details. But in the context of the SQL LIKE condition, here is a quick introduction. In a regular expression a dot (.) represents any character, similar to the underscore (_) in SQL. Any number of any character matches percent (%) in SQL, this is represented as a dot with asterisk (.*)in XQuery. Further, in a regular expression ^ and $ indicate the start and end of the input string.
- [cc lang="sql"]where DESCRIPTION LIKE 'Motor%'[/cc]
is written in XQuery as
[cc lang="xquery"]where matches($item/DESCRIPTION, "^Motor")[/cc]
- [cc lang="xquery"]where DESCRIPTION LIKE '%Motor%'[/cc] is written in XQuery as [cc lang="xquery"]where matches($item/DESCRIPTION, "Motor")[/cc]
- [cc lang="xquery"]where DESCRIPTION LIKE 'Motor_'[/cc] is written in XQuery as [cc lang="xquery"]where matches($item/DESCRIPTION, "^Motor.$")[/cc]
- [cc lang="xquery"]where DESCRIPTION LIKE 'Motor%cycle'[/cc] is written in XQuery as [cc lang="xquery"]where matches($item/DESCRIPTION, "^Motor.*cycle$")[/cc]
XQuery also offers a starts-with() and ends-with() function, which are equivalent to LIKE "string%" and LIKE "%string". In simple cases, starts-with() and ends-with() are more convenient than the powerful matches() function.
Get all items ending on "cycle",
[cc lang="xquery"]select * from ITEMS where DESCRIPTION LIKE '%cycle'[/cc]
An equivalent XQuery query is,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where ends-with($item/DESCRIPTION = "cycle") return $item[/cc]
Boolean operators
Similar as in SQL, you can also in XQuery combine Boolean expressions with "and" and "or". Negating a condition in SQL is expressed in two different ways.
Get all items which are not Bicycles,
[cc lang="xquery"]select * from ITEMS where not(DESCRIPTION = 'Bicycle')[/cc]
And get all items not ending on "cycle",
[cc lang="xquery"]select * from ITEMS where DESCRIPTION NOT LIKE '%cycle'[/cc]
In XQuery, we have again only one approach, using the not() function. The first example above can be expressed as follows,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where not($item/DESCRIPTION = "Bicycle") return $item[/cc]
And get all items not ending on "cycle",
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where not(ends-with($item/DESCRIPTION = "cycle")) return $item[/cc]
DISTINCT values
DISTINCT as it exists in SQL, is not available in XQuery . Again, in XQuery this is accomplished through a function, distinct-values(). For example, retrieve all ids for users having placed a bid,
[cc lang="xquery"]select DISTINCT USERID from BIDS[/cc]
Using the distinct-values() function this can be expressed as follows in XQuery,
[cc lang="xquery"]distinct-values(collection("BIDS")/BIDS/USERID)[/cc]
Note that we wrote this without a FLWOR expression. In case we would like to return the user ids ordered, we would need to use a FLWOR,
[cc lang="xquery"]for $userId in distinct-values(collection("BIDS")/BIDS/USERID) order by $userId return $userId[/cc]
Aggregate functions
Not surprising, the 5 aggregate functions in SQL and XQuery are equivalent,
- avg
- count
- max
- min
- sum
Calculating the total value of all items in our auction,
[cc lang="xquery"]select sum(RESERVE_PRICE) from xvs001.ITEMS[/cc]
Is expressed in XQuery as follows,
[cc lang="xquery"]sum(collection("ITEMS")/ITEMS/RESERVE_PRICE)[/cc]
Scalar functions
Just as every database having a long list of scalar functions, XQuery also includes a wide range of built-in functions. Going through the complete list is out of scope for this post. In general usage is the same, only a matter of finding the equivalent XQuery function for your SQL function.
Retrieve all items with a description of more than 10 characters. In SQL Server we have the len() function.
[cc lang="xquery"]select DESCRIPTION from ITEMS where len(DESCRIPTION) > 10[/cc]
With the following equivalent XQuery,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where string-length($item/DESCRIPTION) > 10 return $item/DESCRIPTION[/cc]
Suppose your favorite SQL function is not supported in the standard XQuery function library. Are you stuck? Not necessarily if you're using DataDirect XQuery, which allows you to call any of the functions available in your database.
Consider the ORACLE function INITCAP, it returns the specified string with each word's first letter in uppercase,
[cc lang="xquery"]select INITCAP(DESCRIPTION) from ITEMS[/cc]
Querying my ORACLE data through DataDirect XQuery you can achieve the same as follows. All you need is to declare in your query the INITCAP function as being SQL specific, and under the covers DataDirect XQuery will take advantage of Oracles INITCAP implementation.
[cc lang="xquery"]declare function ddtek-sql:INITCAP($s as xs:string) as xs:string external; for $item in collection('ITEMS')/ITEMS return ddtek-sql:INITCAP(lower-case($item/DESCRIPTION))[/cc]
I hope you have now a better feel on the similarities and key differences between SQL and XQuery, when it comes to functions and operators. Looking forward at our next post, we'll discuss grouping.