How to extract XML data for relational use?

Problem Statement:

You need to extract individual elements values from an XML document.

Solution:

Oracle provides the XMLTABLE function to manipulate XML documents using XQuery and column mapping to Oracle datatypes. Using XMLTABLE, we can identify and use data elements in an XML document in a relational way.

Let us assume, below XML document is stored in a table tmp_xml_gen from which we wanted to extract the elements. We have customer details along with order related information.

Example

<p><?xml version="1.0"?>
<customers>
  <customer>
    <customer_id>134</customer_id>
    <email>taylor.cauchon@internalmail</email>
    <name>Taylor Cauchon</name>
    <orders>
      <order>
        <order_id>921</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>485</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>1528</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>1672</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>1319</order_id>
        <status>COMPLETE</status>
      </order>
    </orders>
  </customer>
</customers></p>

Code

<p>WITH cust AS
-- Customer details
      (SELECT xt.customer_id,
              xt.email,
              xt.name,
              x.result
         FROM tmp_xml_gen x,
         XMLTABLE('/customers/customer' PASSING x.RESULT
                   COLUMNS
                   customer_id VARCHAR2(10)  PATH 'customer_id',
                         email VARCHAR2(255) PATH 'email',
                          name VARCHAR2(255) PATH 'name') xt),
-- Order details
     ord AS
     (SELECT customer_id,
             email,
             name,
             xt2.*
        FROM cust t,
        XMLTABLE('//orders/order' PASSING t.result
                 COLUMNS order_id VARCHAR2(4)  PATH 'order_id',
                           status VARCHAR2(10) PATH 'status' ) xt2)
SELECT * FROM ord;</p>

Output: Few rows from above SQL

<p>134 taylor.cauchon@internalmail Taylor Cauchon  921     COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  485     COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  1528    COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  1672    COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  1319    COMPLETE
135 marc.domanski@internalmail  Marc Domanski   50      COMPLETE
135 marc.domanski@internalmail  Marc Domanski   99      COMPLETE
135 marc.domanski@internalmail  Marc Domanski   142     COMPLETE
135 marc.domanski@internalmail  Marc Domanski   195     COMPLETE</p>

The XMLTABLE function takes one or more XQuery expressions to apply to a column or expression of XMLTYPE data. The general form of XMLTABLE is:

<p>xmltable(
  <XQuery to apply>,
  <Source XMLTYPE column or expression>,
  <Column definition optionally mapped with XQuery path expression>).</p>