In this series of articles I’m hoping to provide a primer for newish Oracle developers who are curious about XQuery and are looking to dip their toes into its world. If that’s you, hi, nice to meetcha.
Or rather, nice to meetcha again. This is the second article in the series; go back and read the first if you haven’t yet.
In this article we’re going to talk about Oracle’s implementation of the SQL/XML functions that include XQuery – or, in other words, how to use XQuery in your Oracle database. (SQL/XML, in case you were wondering, is that part of the SQL standard that instructs the various relational databases on how to deal with XML and ensures that the skills you learn in this article are mostly transferable between RDBMS platforms. Oh, you weren’t wondering? Never mind then.)
To really get our teeth into this, we need to create a table and load it with some sample data. Our table will need to have a column of the XMLTYPE type, which is Oracle’s native XML datatype.
CREATE TABLE sitcoms ( id NUMBER, sitcom VARCHAR2(30), data XMLTYPE );
Now we’ve got our table, let’s stuff in some data. We’ll use our XML from the first article and one other.
INSERT INTO sitcoms (id, sitcom, data) VALUES (1, 'Friends', xmltype ('<friends> <characters> <character status="Lead" actorid="001"> <firstname>Ross</firstname> <lastname>Geller</lastname> <gender>Male</gender> <birthdate>1967-10-18</birthdate> <occupation>Palaeontologist</occupation> </character> </friends>') ); INSERT INTO sitcoms (id, sitcom, data) VALUES (2, 'Episodes', xmltype ('<episodes> <characters> <character status="Lead" actorid="003"> <firstname>Matt</firstname> <lastname>LeBlanc</lastname> <gender>Male</gender> <birthdate>1967-07-25</birthdate> <occupation>Actor</occupation> </character> </episodes>') );
I know I’m beginning to get a little boring with this SQL/XML thing, but it’s probable useful at this point to bring it up again. There’s a huge amount that you can do with XML in the Oracle database. The corporation has pretty much fully implemented everything in the SQL/XML standard – and thrown in a few native functions too.
However, this article is about XQuery, and so we’ll be restricting ourselves to the 3 main SQL/XML functions that use XQuery: XMLQuery, XMLTable and XMLExists.
Here’s a quick rule of thumb: you use XMLQuery in the SELECT clause; you use XMLTable in your FROM clause; and you use XMLExists in your WHERE clause.
XMLQuery, as I’ve just said, lives in the SELECT clause and is a function for querying XML data. (You may have heard of other SELECT-centric functions like XMLAgg and XMLElement. They’re not XQuery functions and – here’s another rule of thumb for you – they’re mostly used for generating XML; XQuery is used for querying XML.) XMLQuery queries XML data and returns XML.
Here’s the syntax for XMLQuery
XMLQuery ( '<xquery string>' PASSING <xmltype column> RETURNING CONTENT NULL ON EMPTY )
Basically, you feed it a full XQuery expression as a string and, using the PASSING clause, you set a context – i.e. tell it where to get the xml you wish to query. Finally, the RETURNING CONTENT is mandatory. (NULL ON EMPTY is an optional appendage and, as you’ve probably guessed, it tells Oracle what to do if your XQuery expression resolves to nothing.)
Here’s an example. Say we wanted to find out the first names of all the lead characters of our sitcoms, we might run something along these lines:
SELECT sitcom, XMLQUERY('//characters/character[@status="Lead"]/firstname' PASSING data RETURNING CONTENT ) character_name FROM sitcoms;
The XMLTable function is usually found hanging around in the FROM clause of queries. It is used to transform the results of an XQuery expression into a virtual table, complete with rows and columns.
Its usefulness is probably immediately obvious to you; once it turns the XML into a virtual table it makes it a doddle for you to run your everyday SQL statements against it.
Here’s the syntax:
XMLTable ( '<xquery string>' PASSING <xml col> COLUMNS <col name> <col type> PATH <XQuery path> )
Basically, you feed it a full XQuery expression as a string along with (in the PASSING clause) the xmltype column that gives it its context. You then (optionally) define the columns of the virtual table you’d like it to create. (If you don’t define your columns, XMLTable will create one xmltype column named COLUMN_VALUE. You probably don’t want that, so you really should define your columns.) Finally, the PATH tells XMLTable what portion of the XQuery’s result should make up the column.
It’s pretty straightforward, but just in case I haven’t explained it very well, here’s an example for you.
Let’s return to the example we used when we were discussing XMLQuery; say we wanted to find out the details of the lead characters in our sitcoms.
SELECT sitcoms.sitcom, t.* FROM sitcoms, XMLTABLE('//characters/character[@status="Lead"]' PASSING sitcoms.data COLUMNS firstname VARCHAR2(30) PATH 'firstname', lastname VARCHAR2(30) PATH 'lastname', gender VARCHAR2(10) PATH 'gender' ) t;
Notice how the resultset is neat and orderly, like a regiment of soldiers standing to attention, rather than a jumble of xml.
I’d also like to you to notice that, in the FROM clause, I called the sitcoms table right before I made the call to XMLTable. Without this, it would’ve failed. You need to make a call to the table with your XML right before you call XMLTable.
Notice also that the PATH subclause references the node within the XQuery expression that you want the column to be built from.
The XMLExists function returns a boolean that reports if an XQuery expression returns a non-empty sequence, and can be found in the WHERE clause of queries. Well, can mostly be found in WHERE clauses; the one exception is that you can use XMLExists in case statements in your SELECT clause. But let’s put that to one side for now; we can talk about it later.
Here’s the syntax.
XMLExists ( '<xquery string>' PASSING '<xml col>' )
Basically, you feed it a full XQuery expression as a string along with (in the PASSING clause) the xmltype column that gives it its context, and if that XQuery expression leads it to an actual snatch of xml it’ll return TRUE, otherwise it’ll return FALSE.
Let’s think of an example. We know Matt Leblanc is an actor, but what if we’re looking for that sitcom where he plays himself rather than a character (in other words, a sitcom where Matt Leblanc is a character, not just a cast member).
SELECT sitcom FROM sitcoms WHERE XMLEXISTS('//characters/character[firstname = "Matt" and lastname= "LeBlanc"]' PASSING data );
Ah, that’s it Episodes, the sitcom in which Matt LeBlanc plays himself, and not Friends where he plays the character Joey Tribiani.
You know I said that XMLExists can be used in case statements; I should’ve also mentioned that it can also be used in function-based indexes. It’s pretty straightforward, but I won’t go into it as it’s a little beyond the scope of this article.
And that’s it for this second article in our series on XQuery and Oracle. There is a certain elegance and symmetry to the SQL/XML functions that we’ve been talking about that I personally find quite attractive. I hope that’s come across in this article.
We don’t often look for beauty in computer code; but if it’s prettiness you’re after, wait for the next article in this series – it’s about FLWOR.Tags: oracle, XML, xmlexists, xmlquery, xmltable, xquery