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 |