Oracle SQL and XML Selecting things as an XMLType

Question

I have this table

 CREATE TABLE Test_T (
  COL_1 VARCHAR(30),
  COL_2 XMLTYPE
);

With this inside

INSERT INTO Test_T VALUES ('one', XMLType('<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
 <food>
  <name>Belgian Waffles</name>
  <price>$5.95</price>
  <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
  <calories>650</calories>
 </food>
 <food>
  <name>Strawberry Belgian Waffles</name>
  <price>$7.95</price>
  <description>Light Belgian waffles covered with strawberries and whipped cream</description>
  <calories>900</calories>
 </food>
 <food>
  <name>Berry-Berry Belgian Waffles</name>
  <price>$8.95</price>
  <description>Light Belgian waffles covered with an assortment of fresh berries and whipped cream</description>
  <calories>900</calories>
 </food>
  <food>
  <name>French Toast</name>
  <price>$4.50</price>
  <description>Thick slices made from our homemade sourdough bread</description>
  <calories>600</calories>
 </food>
 <food>
  <name>Homestyle Breakfast</name>
  <price>$6.95</price>
   <description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description>
  <calories>950</calories>
 </food>
</breakfast_menu>
'));

I want to select the Name And description from this table where the name contains Belgian

I started with this select statement but it doesn't seem to be working

SELECT XMLTYPE FROM Test_T WHERE ('/breakfast_menu/food[contains(name, 'Belgian')]');

Could anyone point me in the right direction?

Answer 1

You're looking for XMLTABLE.

Here's how a SQL statement would look like against your XML -

SELECT name, description
  FROM test_t,
       XMLTABLE (
          '/breakfast_menu/food[contains(name,"Belgian")]'
          PASSING test_t.col_2
          COLUMNS name VARCHAR2 (100) PATH 'name',
                  description VARCHAR2 (100) PATH 'description');

Another way to write the same thing would be -

SELECT name, description
  FROM test_t,
       XMLTABLE (
          '/breakfast_menu/food'
          PASSING test_t.col_2
          COLUMNS name VARCHAR2 (100) PATH 'name',
                  description VARCHAR2 (100) PATH 'description')
 WHERE name LIKE '%Belgian%';

Depending on various factors, one of these could be more efficient. You can run diagnostics and figure out which kind of filtering is best for your data.