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.
ComparisonsAll 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,
select *
from ITEMS
where DESCRIPTION = 'Motorcycle'
is written in XQuery as follows,
for $item in collection("ITEMS")/ITEMS
where $item/DESCRIPTION = "Motorcycle"
return $itemLooks 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,
for $item in collection("ITEMS")/ITEMS
where $item/DESCRIPTION eq "Motorcycle"
return $item/ITEMNOArithmetic operatorsA 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 clauseNote the similarity, to some extend, between the IN condition as known in SQL with XQuery's equality general comparison.
select ITEMNO
from ITEMS
where DESCRIPTION in ('Motorcycle', 'Bicycle')
Can be written in XQuery using a general comparison,
for $item in collection("ITEMS")/ITEMS
where $item/DESCRIPTION = ("Motorcycle", "Bicycle")
return $item/ITEMNOXQuery 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 conditionTake 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.
where DESCRIPTION LIKE 'Motor%' is written in XQuery as where matches($item/DESCRIPTION, "^Motor")where DESCRIPTION LIKE '%Motor%' is written in XQuery as where matches($item/DESCRIPTION, "Motor")where DESCRIPTION LIKE 'Motor_' is written in XQuery as where matches($item/DESCRIPTION, "^Motor.$")where DESCRIPTION LIKE 'Motor%cycle' is written in XQuery as where matches($item/DESCRIPTION, "^Motor.*cycle$")
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",
select *
from ITEMS
where DESCRIPTION LIKE '%cycle'
An equivalent XQuery query is,
for $item in collection("ITEMS")/ITEMS
where ends-with($item/DESCRIPTION = "cycle")
return $itemBoolean operatorsSimilar 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,
select *
from ITEMS
where not(DESCRIPTION = 'Bicycle')
And get all items not ending on "cycle",
select *
from ITEMS
where DESCRIPTION NOT LIKE '%cycle'
In XQuery, we have again only one approach, using the not() function. The first example above can be expressed as follows,
for $item in collection("ITEMS")/ITEMS
where not($item/DESCRIPTION = "Bicycle")
return $itemAnd get all items not ending on "cycle",
for $item in collection("ITEMS")/ITEMS
where not(ends-with($item/DESCRIPTION = "cycle"))
return $itemDISTINCT valuesDISTINCT 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,
select DISTINCT USERID
from BIDS
Using the distinct-values() function this can be expressed as follows in XQuery,
distinct-values(collection("BIDS")/BIDS/USERID)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,
for $userId in distinct-values(collection("BIDS")/BIDS/USERID)
order by $userId
return $userIdAggregate functionsNot surprising, the 5 aggregate functions in SQL and XQuery are equivalent,
Calculating the total value of all items in our auction,
select sum(RESERVE_PRICE) from xvs001.ITEMS
Is expressed in XQuery as follows,
sum(collection("ITEMS")/ITEMS/RESERVE_PRICE)Scalar functionsJust 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.
select DESCRIPTION
from ITEMS
where len(DESCRIPTION) > 10
With the following equivalent XQuery,
for $item in collection("ITEMS")/ITEMS
where string-length($item/DESCRIPTION) > 10
return $item/DESCRIPTIONSuppose 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,
select INITCAP(DESCRIPTION)
from ITEMS
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.
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))
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.
Labels: relational, SQL, XQuery
