XPath / XQuery / SQL

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