Using the Text-on-XML Method

With Oracle Text, you can create a CONTEXT index on a column that contains XML data. The column type can be XMLType or any supported type, provided that you use the correct index preference for XML data.

With the Text-on-XML method, use the standard CONTAINS query and add a structured constraint to limit the scope of a search to a particular section, field, tag, or attribute. That is, specify the structure inside text operators, such as WITHIN, HASPATH, and INPATH.

For example, set up your CONTEXT index to create sections with XML documents. Consider the following XML document that defines a purchase order:

<?xml version="1.0"?>
<PURCHASEORDER pono="1">
   <PNAME>Po_1</PNAME>
   <CUSTNAME>John</CUSTNAME>
   <SHIPADDR>
      <STREET>1033 Main Street</STREET>
      <CITY>Sunnyvalue</CITY>
      <STATE>CA</STATE>
   </SHIPADDR>
   <ITEMS>
     <ITEM>
       <ITEM_NAME> Dell Computer </ITEM_NAME>
       <DESC> Pentium 2.0 Ghz 500MB RAM  </DESC>
     </ITEM>
     <ITEM>
       <ITEM_NAME> Norelco R100 </ITEM_NAME>
       <DESC>Electric Razor </DESC>
     </ITEM>
   </ITEMS>
</PURCHASEORDER>

To query all purchase orders that contain Pentium within the item description section, use the WITHIN operator:

SELECT id from po_tab where CONTAINS( doc, 'Pentium WITHIN desc') > 0;

Use the INPATH operator to specify more complex criteria with XPATH expressions:

SELECT id from po_tab where  CONTAINS(doc, 'Pentium INPATH (/purchaseOrder/items/item/desc') > 0;