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?

ruudvan · Accepted Answer · 2016-04-05 21:37:19Z

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.

answered Apr 5, 2016 at 21:37

ruudvan

1,3816 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

impact_Sv Over a year ago

@ruudvan- Actually when i use the first one it's just giving me an empty table.

Collectives™ on Stack Overflow

Oracle SQL and XML Selecting things as an XMLType

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related