This is the fourth part of a series introducing Oracle developers to XQuery. In the first three parts we’ve talked about XPath, Oracle’s implementation of XQuery, and FLWOR. This article won’t be taking on anything so meaty; I just want to clear up some odds and ends that I think are important, but I wasn’t able to shoehorn into any of the earlier articles.

You know that feeling. You take your flatpack furniture home from Ikea, and even though the instruction manual says Assembly time: 1 hour, you’re still sweating over it two and a half hours later. And when you’re finally done, and just as you’re about to congratulate yourself, you spot that one leftover screw lying on the floor. And you panic: where should it go? Does this mean your new bed will collapse in the middle of the night?

Well, the topics in this article are those lonely screws; they didn’t necessarily fit neatly into any of the earlier boxes (or, if I’m being honest, I forgot to include them when I was writing those articles).

Loops

In our discussion of FLWOR we touched on the subject of loops when we talked about for. However, you may have come away from that conversation with the mistaken impression that XQuery loops are constrained to only iterate through an XML document’s nodes. No, you can also write loops that iterate through ranges that are of your own definition. And you can define those ranges either by using the “to” function or simply by enumerating its constituents.

And, if that’s what floats your boat, you can combine both methods:

Let’s throw in a little more functionality. You can use the “at” function to add a simple counter to your loop. Here’s an example:

Conditional statements

XQuery supports if-then-else statements, as you would probably expect. There are no real surprises here; the syntax is, again, as you might expect:

If, for instance, we wanted to output a cast list of the sitcom in our example XML with the names of main characters in upper case, we might write something like this:

One thing that’s probably worth noting is that, unlike in PL/SQL, the else clause in XQuery conditional statements is not optional. There are ways around this “shortcoming”; you could, for instance, simply output an empty string.

Quantified Expressions

The topic of quantified expressions follows on nicely from our talk about conditional statements. You mightn’t be familiar with the term so we’d best start with a definition. Quantified expressions are means by which we can compare two sets of sequences and return a boolean that tells us whether they match or not. There are two types of quantified expressions: the existential quantifier and the universal quantifier. An existential quantifier compares two sets and returns “true” if any item in the first sequence has a matching value in the second. A universal quantifier, on the other hand, will only return “true” if every item in the first set has a matching value in the second.

Here’s the syntax:

As you might expect, you use the some keyword when you want an existential quantifier, and every when you want a universal one.

One way to look at the quantified expression syntax is to think that it mirrors that of a FLWOR statement, with some|every standing in for the for, and satisfies taking the place of where, order and return.

An example would probably help at this point: let’s say we wanted to confirm that every cast member in our XML has a status attribute in the character tag. We could wrap a universal quantifier in a conditional statement.

Custom Functions

For all the reasons that you already know – plus one that you may not have thought of – you can create your own functions in XQuery. Being Oracle developers we, of course, have the advantage of being able to output our XML document using XMLTable and manipulating it using simple SQL. However, if you want to – or need to – keep all the logic within the walls of your XQuery, you may want to create your own function(s) to break down complex code and make it more readable. Another advantage – the one I said you may not have thought of – is that user-defined XQuery functions can also be called recursively, which, depending on the structure of your XML, can be a huge boon.

The syntax for XQuery custom functions is as follows:

Let’s step through the syntax word by word: declare function well, declares the function. No explanation needed there. The prefix is the namespace. A namespace named local is reserved by XQuery and is used, unsurprisingly, for locally-declared functions. It’s what we’ll be using in our examples.

Jumping forward a little brings us to our input parameter. We can, as you’d expect, have zero, one, or many input parameters. If you have more than one, they must be comma-separated. The datatypes in an XQuery function can either be nodes or atomic values. Here are some of the datatypes that you may find yourself needing:

This isn’t an exhaustive list (see here), but it’s enough to get us started.

Continuing to step through the syntax for XQuery custom functions brings us to the function body. The body must be bookended by curly brackets and, importantly, must end with a semi-colon. The code that makes up that body can be an expression, or it can be full-fledged FLWOR.

Let’s write a simple function of our own. We might, for instance, want to write a function that, given an actor id, would return the actor’s name.

If we wanted to find out the name of the actor who plays Ross in our sitcom we could write the following query:

I said earlier that one strength of XQuery custom functions is that they’re recursive. Yeah, big deal, you might have thought dismissively, but think about it: XML, by its very nature, lends itself to recursive data, so having functions that are recursive – can call themselves directly or indirectly – is a big deal.

Lets reimagine our standard Emp table – listing a group of employees, their departments and, more importantly for our current purposes, their managers – as XML. Lets say Allen is managed by Blake and Blake is managed by King.

If we wanted to write a function that would give us the organisation chart starting with a parameterised staffer, we might write something like this.

Conclusion

I don’t know who you are, but I imagine you’re a bit like me. We’re Oracle developers and we’ve got our biases. Our natural inclination is to do as much in SQL and PL/SQL as possible; and so I have to admit that I’m a little more likely to take XML data and output it using the XMLTable command, and then manipulate it with SQL than I am to use XQuery custom functions or even, sometimes, conditional statements. But it’s good that we’ve talked about them.

And that’s it. I promised you that this would be the final article in this series – and it is.

Kind of. There is one more subject that I really want to talk about – it goes by the ugly name, XQUF – and so we need one further article. That’ll come next, and then that’ll be it. I promise.