There’s something slightly oxymoronic about the phrase XQuery Update Facility. Make your mind up, dude – are you querying or updating? You cannae do both!
But as, with each passing year, XQuery got more powerful and more popular the lack of an ability to modify XML documents began to look more and more like a gaping hole. And so, in 2009, the XQuery Update Facility – a small extension to XQuery – became a W3C Candidate Recommendation, and, by 12c it had found its way into Oracle (we need to talk in detail about Oracle’s implementation of XQuery Update Facility, but we’ll do that a little later).
XQuery Update Facility
First things first. “XQuery Update Facility” is far too much typing, and so it is often abbreviated to XQUF, which looks less like a name and more like a toddler closing his eyes and banging wildly at a keyboard. But oh well.
And before we dive head first into XQUF and its syntax, we should mention a few of its quirks. Yes, XQUF modifies XML data; however, updates are not applied immediately, instead they are stacked in a “Pending Update List” and executed at the end.
I’ve never been one of those geeks who cares too deeply about execution plans as long as my queries run reasonably quickly. So if you’re like me, you might wonder: “so what?” But this quirk does have two major impacts on XQUF. Firstly, it means that your XQUF operations can be written in pretty much any order you like, which is nice. Fancy writing your delete before your insert? Go ahead, bruv, knock yourself out.
The second consequence of this XQUF quirk is that you cannot, therefore, query XQUF changes in the same command in which you are making them. And that’s because those changes are not executed until the end of that command.
But we’re getting a little ahead of ourselves here. Let me introduce you to the 5 basic XQUF operations. We’ll start with four of them, and then, for two important reasons, we’ll tackle the fifth separately.
The four operations we’re going to talk about first are called updating expressions because, well, because they’re expressions and they update XML. They are:
- insert – which inserts one or more nodes inside, after or before a specified node.
- delete – which deletes one or more nodes.
- replace – which can either replace a node and its descendants, if it has any, or can replace the value of a node.
- rename – which can change the name of a node, leaving its contents and any attributes untouched.
To employ these operations we would, typically, use XPath or FLWOR to select the node(s) we wish to update and then we would update them.
Let’s take a closer look at each one.
We use insert to add a node to an XML document. Here’s the syntax:
If, for instance, we wanted to include a count of all our characters to our XML, we might write the following:
for $i at $cnt in //friends/character return insert node <count>$cnt</count> as first into $i
This, unsurprisingly, deletes a node. The syntax is simple too: follow the keywords delete node with an expression that identifies the node(s) you wish to delete.
If, for instance, we wished to delete the node we added in our previous example, we might write:
for $i in //friends/character/count return delete node $i
The insert and delete operations worked strictly on nodes; replace, however, has an extra trick up its sleeves. Using replace you can either replace a node (and any descendants it may have), or you may replace the value of a node.
To replace a node you’d phrase your command thus:
However, if you wish to replace the value of a node with a string, you need the value of keywords.
An example might help here. Say we wanted to update one of the characters’ name from Monica Geller to Monica Geller-Bing, we might write:
for $i in //friends/friend/character where $i/firstname = "Monica" and $i/lastname = "Geller" return replace value of node $i/lastname with "Geller-Bing"
This operation allows you update the name of a node without affecting it’s atomic value, its attributes or descendants. The syntax is as follows:
The fifth XQUF expression is transform. The reason I have spliced it from its brethren is that, unlike them, it is a non-updating expression. What we do here is make a copy of the XML document, modify that copy, and then return the result. And that’s what makes it a non-updating expression – it doesn’t change the original XML data.
There’s a second reason why I’ve hived it off apart from the other XQUF expressions, and that is because this is the only XQuery Update Facility type that we use in Oracle. That’s right, if you want to use XQUF in your Oracle code, you must use a transform expression.
So pay attention.
An example would probably help at this point. Why don’t we repurpose our last example as a transform operation?
copy $monica := //friends/friend/character[@actorid="005"] modify replace value of node $monica/lastname with "Geller-Bing" return $monica
What we’ve basically done is make a copy of the node identified by our XPath expression, apply some XQUF to it, and then returned it.
Oracle and XQUF
As I’ve said, to use XQUF in Oracle, you must wrap it in a transform operation. Which is fine; they’re easy enough to write. Another thing to remember – and this is something that seems counterintuitive – is that irrespective of what you want to do to your XML data – delete, insert, whatever – you must use an update SQL statement. It makes sense if you think about it – you are updating your XML data by deleting that node – but the instinct to start writing an SQL delete statement might be strong.
Let us go back to our last example and dress it in an Oracle suit.
UPDATE sitcom SET data = XMLQuery ('copy $monica = . modify replace value of node $monica/friends/characters/character[@actorid="005"]/lastname with "Geller-Bing" return $monica' PASSING data RETURNING CONTENT ) WHERE id = 1;
We’re 5 articles into this series so I’m guessing I don’t need to spoonfeed you any more; you can probably read the above statement as well as I can. However, there is something that I’d like to draw your attention to, and it’s this line:
copy $var = .
As you possibly remember the “.” represents the current node and will, therefore, copy the whole column into the variable. You’ll probably want this, because you should remember that it is only the contents of our variable that we’re passing back in our return clause. If we populate it with a subset of our data – say, //friends/characters/character[@actorid=”005″] – we’ll find that when we run our update statement that’s all that’ll be left in our column.
Deprecated Oracle Functions
If you’ve updated XML in Oracle in the past, you may have used some functions that have now been deprecated. If you haven’t, you don’t need to read this bit – go and make yourself a cup of tea or, if you hate your lungs, have a cigarette.
The deprecated functions are appendChildXML, insertChildXML, deleteXML, updateXML and a few others. As with all deprecated functionality, my guess is that they’ll hang around, unsupported, for another decade or more, so there’s no need to panic, but you might want to migrate to XQUF. This Oracle document goes into fine detail on what you should do.
And that’s it, XQuery – from XPath via FLWOR all the way to XQUF. Obviously there’s a lot more to learn, but I hope that this short series has equipped you to take it on and learn as you go. If you’ve got any questions, ask them in the comments below and I’m sure that either me or someone from the community will be able to answer it for you.Tags: XML, xquery, xquery update facility, xquf