Extract single repeating XML node through whole XML result

Question

Given the following XML sample below, how can I extract all <table_no> values from top to bottom using Oracle XML extraction.

Based on below, I would expect to see the following individual rows from my select:

1
4
2
11

Table: tickets
Column holding XML: ticket_col 

XML code:

<xml>
  <ticket_order>
    <table_no>1<table_no/>
    <waiter>Jack<waiter/>
    <total_people>12<total_people/>
  </ticket_order>
  <ticket_order>
    <table_no>4<table_no/>
    <waiter>Jackie<waiter/>
    <total_people>3<total_people/>
  </ticket_order>
  <ticket_order>
    <table_no>2<table_no/>
    <waiter>Sally<waiter/>
    <total_people>2<total_people/>
  </ticket_order>
  <ticket_order>
    <table_no>11<table_no/>
    <waiter>Mike<waiter/>
    <total_people>6<total_people/>
  </ticket_order>
</xml>

Answer 1

You can use XMLTable(); with (fixed) sample XML as a string in-line:

select x.*
from xmltable(
 '/xml/ticket_order'
  passing xmltype('<xml>
  <ticket_order>
    <table_no>1</table_no>
    <waiter>Jack</waiter>
    <total_people>12</total_people>
  </ticket_order>
  <ticket_order>
    <table_no>4</table_no>
    <waiter>Jackie</waiter>
    <total_people>3</total_people>
  </ticket_order>
  <ticket_order>
    <table_no>2</table_no>
    <waiter>Sally</waiter>
    <total_people>2</total_people>
  </ticket_order>
  <ticket_order>
    <table_no>11</table_no>
    <waiter>Mike</waiter>
    <total_people>6</total_people>
  </ticket_order>
</xml>')
  columns table_no number path 'table_no'
) x;

  TABLE_NO
----------
         1
         4
         2
        11

If the XML is a string (VARCHAR2 or CLOB) in a table you would pass it in via a cross join:

select x.*
from your_table t
cross join xmltable(
  '/xml/ticket_order'
  passing xmltype(t.xml_string)
  columns table_no number path 'table_no'
) x;

If it's already am XMLType in the table you woudl skip that conversion:

select x.*
from your_table t
cross join xmltable(
  '/xml/ticket_order'
  passing t.xml
  columns table_no number path 'table_no'
) x;

You can get multiple columns at once; and the generated relatinal column name doesn't have to be the same as the node name:

select x.*
from your_table t
cross join xmltable(
  '/xml/ticket_order'
  passing t.xml
  columns table_number number path 'table_no',
    server varchar2(10) path 'waiter',
    covers number path 'total_people'
) x;

TABLE_NUMBER SERVER         COVERS
------------ ---------- ----------
           1 Jack               12
           4 Jackie              3
           2 Sally               2
          11 Mike                6

Read more.