How to extract Key XML Elements from an XML Document in Oracle?

Problem Statement:

You need to extract a portion/subset of elements values from an XML document.

Solution:

We can leverage Oracle’s EXTRACT function which supports XMLTYPE datatype. EXTARCT function provides the ability to preserve XML element name and attribute constructs by returning the results as an XMLTYPE value.

Assume, we have below XML in a table tmp_xml_gen from which we wanted to extract the customer names.

Example

<?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>

SQL: To extract customer name values from a XML document

SELECT extract (result, '/customers/customer/customer_id').getclobval() FROM tmp_xml_gen ;

Output: rows from the SQL statements

<customer_id>134</customer_id>
<customer_id>135</customer_id>
<customer_id>136</customer_id>
<customer_id>137</customer_id>
<customer_id>138</customer_id>
<customer_id>139</customer_id>
<customer_id>140</customer_id>
<customer_id>141</customer_id>
<customer_id>142</customer_id>
<customer_id>143</customer_id>
<customer_id>144</customer_id>
<customer_id>145</customer_id>
<customer_id>146</customer_id>
<customer_id>147</customer_id>

NOTE: The EXTRACT function operation on XML differs from its behavior when used against other datatypes. When used on dates or times, EXTRACT will return only the portion of the date or time requested, such as the month. If month is requested, EXTRACT won’t return the date or time, or any of the “smaller” measures, such as day, hour, and so on. When operating against XMLTYPE data, EXTRACT returns everything at or below the nesting level of the matching element.

We can alos use XMLTABLE to extract the required information.

Code

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;

Output: Few rows from above SQL

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