In brief, I will compare the equivalence of SQL, XQuery and XPATH. The following XML feed is used for this purpose. Oracle 11g is used.
<?xml version="1.0" encoding="UTF-8"?> <cars> <car> <brand active="y">BMW</brand> <model>4.20d</model> <year>2015</year> <country>Germany</country> <price>15000</price> </car> <car> <brand active="n">BMW</brand> <model>5.30d</model> <year>2016</year> <country>Germany</country> <price>25000</price> </car> <car> <brand active="y">Mercedes</brand> <model>slk200</model> <year>2005</year> <country>Germany</country> <price>10000</price> </car> <car> <brand active="y">FORD</brand> <model>Focus</model> <year>2013</year> <country>USA</country> <price>8000</price> </car> <car> <brand active="y">Porche</brand> <model>Carrera</model> <year>2016</year> <country>Germany</country> <price>12000</price> </car> </cars>
Now, I want to get all rows that years between 2005 and 2015 and price more than 10,000. Also, I want rows sorted by model. So, the following XQUERY can be used in XMLTABLE function.
XML is case-sensitive. So if you write //Car instead of //car you will get an error
WITH XML_TAB as ( select XMLTYPE.createXML('<?xml version="1.0" encoding="UTF-8"?> <cars> <car> <brand active="y">BMW</brand> <model>4.20d</model> <year>2015</year> <country>Germany</country> <price>15000</price> </car> <car> <brand active="n">BMW</brand> <model>5.30d</model> <year>2016</year> <country>Germany</country> <price>25000</price> </car> <car> <brand active="y">Mercedes</brand> <model>slk200</model> <year>2005</year> <country>Germany</country> <price>10000</price> </car> <car> <brand active="y">FORD</brand> <model>Focus</model> <year>2013</year> <country>USA</country> <price>8000</price> </car> <car> <brand active="y">Porche</brand> <model>Carrera</model> <year>2016</year> <country>Germany</country> <price>12000</price> </car> </cars>') XML_COL from dual ) SELECT * FROM XML_TAB, XMLTABLE(' for $i in //car let $x:=$i/brand, $start_year:=2005, $end_year:=2015, $price:=10000 where $i/year >= $start_year and $i/year <= $end_year and $i/price >= $price and $i/brand[@active = "y"] order by $x return $i ' passing XML_TAB.XML_COL columns "brand" path 'brand' ,"model" path 'model' ,"year" path 'year' ,"country" path 'country' ,"price" path 'price' ) ORDER BY "brand";
The equilavent XQUERY of the above code is following.
SELECT brand, model, year, country ,price FROM cars WHERE year between 2005 and 2015 and price >= 10000 and active = 'y' ORDER BY brand;
And finally, for XPATH syntax, you can use the following.
//car[year>=2005 and year<=2015 and price>=10000 and brand[@active=”y”]]
WITH XML_TAB as (...) SELECT * FROM XML_TAB, XMLTABLE('//car[year>=2005 and year<=2015 and price>=10000 and brand[@active="y"]]' passing XML_TAB.XML_COL columns "brand" path 'brand' ,"model" path 'model' ,"year" path 'year' ,"country" path 'country' ,"price" path 'price' ) ORDER BY "brand";
So, as it can be seen from the above examples, XPATH looks like file path.
/ | Start from the root node |
// | Get the selected node regardless of location |
. | Selects the current node |
.. | Get the parent of the current node |
@ | Gets an attribute |