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:<\/p>\n
SELECT sitcom, \r\n XMLQUERY('\/\/characters\/character[@status=\"Lead\"]\/firstname' \r\n PASSING data \r\n RETURNING CONTENT\r\n ) character_name\r\nFROM sitcoms;\r\n<\/pre>\n![\"XML](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/03\/xmlquery-results.jpg\")
<\/p>\n
XMLTable<\/h5>\n
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.<\/p>\n
Its usefulness is probably immediately obvious to you; once it turns the XML\u00a0into a virtual table it makes it a doddle for you to run your everyday SQL\u00a0statements against it.<\/p>\n
Here’s the syntax:<\/p>\n
![\"XMLTable](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/03\/xmltable.jpg\")
<\/p>\n
Fig 2 – XMLTable syntax<\/p>\n
XMLTable (\r\n '<xquery string>'\r\n PASSING <xml col>\r\n COLUMNS <col name> <col type> PATH <XQuery path> \r\n )\r\n<\/pre>\nBasically, 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 \u00a0that, so you really should define your columns.) Finally, the PATH tells XMLTable what portion of the XQuery’s result should make up the column.<\/p>\n
It’s pretty straightforward, but just in case I haven’t explained it very well, here’s an example for you.<\/p>\n
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.<\/p>\n
SELECT sitcoms.sitcom, t.*\r\nFROM sitcoms, XMLTABLE('\/\/characters\/character[@status=\"Lead\"]' \r\n PASSING sitcoms.data \r\n COLUMNS firstname VARCHAR2(30) PATH 'firstname', \r\n lastname VARCHAR2(30) PATH 'lastname',\r\n gender VARCHAR2(10) PATH 'gender'\r\n ) t;\r\n<\/pre>\n![\"XMLTable](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/03\/xmltable-results.jpg\")
<\/p>\n
Notice how the resultset is neat and orderly, like a regiment of soldiers standing to attention, rather than a jumble of xml.<\/p>\n
I’d also like to you to notice that, in the FROM clause, I called the\u00a0sitcoms<\/em> 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\u00a0right before you call XMLTable.<\/p>\nNotice also that the PATH subclause references the node within the XQuery expression that you want the column to be built from.<\/p>\n
XMLExists<\/h5>\n
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\u00a0mostly<\/em> 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.<\/p>\nHere’s the syntax.<\/p>\n
![\"XMLExists](\"http:\/\/www.readfree.ly\/wp-content\/uploads\/2016\/03\/xmlexists.jpg\")
<\/p>\n
Fig 3 – XMLExists syntax<\/p>\n
XMLExists (\r\n '<xquery string>'\r\n PASSING '<xml col>'\r\n ) \r\n<\/pre>\nBasically, 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.<\/p>\n
Let’s\u00a0think 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).<\/p>\n
SELECT sitcom\r\n FROM sitcoms\r\n WHERE XMLEXISTS('\/\/characters\/character[firstname = \"Matt\" and lastname= \"LeBlanc\"]'\r\n PASSING data\r\n );\r\n<\/pre>\n![\"XMLExists](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/03\/xmlexists-result.jpg\")
<\/p>\n
Ah, that’s it Episodes<\/em>, the sitcom in which Matt LeBlanc plays himself, and not Friends<\/em> where he plays the character Joey Tribiani.<\/p>\nYou 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.<\/p>\n
Conclusion<\/h5>\n
And that’s it for this second article in our series on XQuery and Oracle. \u00a0There is a certain elegance and symmetry to the SQL\/XML functions that we’ve been talking about that I personally find quite attractive. \u00a0I hope that’s come across in this article.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[4459,4217,48540,48541,48542,4734],"coauthors":[],"class_list":["post-73129","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-oracle","tag-xml","tag-xmlexists","tag-xmlquery","tag-xmltable","tag-xquery"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73129"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73129\/revisions"}],"predecessor-version":[{"id":91626,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73129\/revisions\/91626"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73129"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"XML](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/03\/xmlquery.jpg\")
<\/p>\n