12 Full-Text Search Over XML Data

Comparison of Full-Text Search and Other Search Types

Full-text search differs from structured search or substring search in the following ways:

• A full-text search looks for whole words rather than substrings. A substring search for comments that contain the string "law" can return a comment that contains "my lawn is going wild". A full-text search for the word "law" cannot.

• A full-text search supports some language-based and word-based searches that substring searches do not. You can use a language-based search, for example, to find all the comments that contain a word with the same linguistic stem as "mouse", and Oracle Text finds "mouse" and "mice". You can use a word-based search, for example, to find all the comments that contain the word "lawn" within 5 words of "wild".

• A full-text search generally involves some notion of relevance. When you do a full-text search for all the comments that contain the word "lawn", for example, some results are more relevant than others. Relevance is often related to the number of times the search word (or similar words) occur in the document.

Searching XML Data

XML search is different from unstructured document search. In unstructured document search you generally search across a set of documents to return the documents that satisfy your text predicate. In XML search you often want to use the structure of the XML document to restrict the search. And you often want to return just the part of the document that satisfies the search.

Searching Documents using Full-Text Search and XML Structure

There are two ways to do a search that includes full-text search and XML structure:

• Include the structure inside the full-text predicate, using Oracle SQL function contains:

  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0
  

  Function contains is an extension to SQL, and can be used in any query. It requires a CONTEXT full-text index.

• Include the full-text predicate inside the structure, using XPath function ora:contains:

  '/purchaseOrder/items/item/comment[ora:contains(text(), "electric")>0]'
  

  XPath function ora:contains is an extension to XPath, and can be used in a call to SQL/XML function XMLQuery, XMLTable, or XMLExists.

Roles and Privileges

To run the examples, you need database roles CTXAPP, CONNECT, and RESOURCE. You must also have EXECUTE privilege on the CTXSYS package CTX_DDL.

Schema and Data for Full-Text Search Examples

Examples in this chapter are based on "The Purchase Order Schema", W3C XML Schema Part 0: Primer.

The data in the examples is from the document "Purchase-Order XML Document, po001.xml".

The tables used in the examples of this chapter are defined in section "CREATE TABLE Statements""CREATE TABLE Statements". Some of the performance examples are, however, based on a larger table (purchase_orders_xmltype_big), which is included in the downloadable version only. See http://www.w3.org/TR/xmlschema-0/#po.xml.

Some of the examples here use data type VARCHAR2. Others use type XMLType. All examples that use VARCHAR2 also work with XMLType.

Overview of SQL Function CONTAINS

Oracle SQL function contains returns a positive number for rows where [schema.]column matches text_query. Otherwise, it returns zero. It requires an index of type CONTEXT. If there is no CONTEXT index on the column being searched, then contains raises an error.

CONTAINS Syntax

contains([schema.]column, text_query VARCHAR2 [,label NUMBER])
RETURN NUMBER

Example 12-1 shows a typical query that uses Oracle SQL function contains. It returns the id for each row in table purchase_orders where the doc column contains the word "lawn" and id is less than 25.

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn') > 0 AND id < 25;

Suppose doc is a column that contains a set of XML documents. You can do full-text search over doc, using its XML structure to restrict the query. The query in Example 12-2 returns id values for table purchaseorders where column doc contains the word "lawn" in the text() node of XML element comment.

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn WITHIN comment') > 0;

More complex XML structure restrictions can be applied using the INPATH operator and an XPath expression. The query in Example 12-3 finds purchase orders that contain the word "electric" in the text() node of a comment element that is targeted by XPath expression/purchaseOrder/items/item/comment.

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

Overview of XPath Function ora:contains

XPath function ora:contains can be used in an XPath expression inside an XQuery expression or in a call to SQL/XML function XMLQuery, XMLTable, or XMLExists. It is used to restrict a structural search with a full-text predicate. It extends XPath through a standard mechanism: it is a user-defined function in the Oracle XML DB namespace, ora. It requires no index, but you can use an index with it to improve performance.

ora:contains Syntax

ora:contains(input_text NODE*, text_query STRING 
             [,policy_name STRING]
             [,policy_owner STRING])

Function ora:contains returns a positive integer when the input_text matches text_query (the higher the number, the more relevant the match), and zero otherwise. When used in an XQuery expression, the XQuery return type is xs:integer(). When used in an XPath expression outside of an XQuery expression, the XPath return type is number.

Argument input_text must evaluate to a single text node or an attribute. The syntax and semantics of text_query in ora:contains are the same as text_query in contains, with the following restrictions:

• Argument text_query cannot include any structure operators (WITHIN, INPATH, or HASPATH).

• If the weight score-weighting operator is used, the weights are ignored.

Example 12-4 shows a call to ora:contains in the XPath parameter to XMLExists. Notice the namespace declaration that declares prefix ora as representing the Oracle XML DB namespace.

SELECT id
  FROM purchase_orders_xmltype
  WHERE
    XMLExists(
      'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
       $d/purchaseOrder/comment
         [ora:contains(text(), "($lawns AND wild) OR flamingo") > 0]'
      PASSING doc AS "d");

Comparison of CONTAINS and ora:contains

Both Oracle SQL function contains and Oracle XPath function ora:contains let you combine searching on XML structure with full-text searching. These are the main differences between them:

Oracle SQL function contains:

• Needs a CONTEXT index to run. If there is no index, then an error is raised.

• Does an indexed search and is generally very fast.

• Returns a score (through Oracle SQL function score).

• Restricts a search based on documents (rows in a table) rather than nodes.

• Cannot be used for XML structure-based projection (extracting parts of an XML document).

Oracle XPath function ora:contains:

• Does not need an index to run, but you can use an index to improve performance.

• Might do an unindexed search, so it might be resource-intensive.

• Separates application logic from storing and indexing considerations.

• Does not return a score.

• Can be used for XML structure-based projection (extracting parts of an XML document).

Use Oracle SQL function contains when you want a fast, index-based, full-text search over XML documents, possibly with simple XML structure constraints. Use Oracle XPath function ora:contains when you need the flexibility of full-text search combined with XPath navigation (possibly without an index) or when you need to do projection, and you do not need a score.

Full-Text Search using SQL Function CONTAINS

The second argument to Oracle SQL function contains, text_query, is a string that specifies the full-text search. text_query has its own language, based on the SQL/MM Full-Text standard.

The examples in the rest of this section show some of the power of full-text search. They use only a few of the available operators. The example queries search over a VARCHAR2 column (PURCHASE_ORDERS.doc) with a text index (index type CTXSYS.CONTEXT).

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn AND wild') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, '((lawn OR garden) AND (wild OR flooded)) NOT(flamingo)')
        > 0;

SELECT id FROM purchase_orders WHERE contains(doc, '$(lawns)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, '($lawns AND wild) OR flamingo') > 0;

SCORE SQL Function

Oracle SQL function score is related to Oracle SQL function contains. Function score can be used anywhere in a query. It is a measure of relevance, and it is especially useful when doing full-text searches across large document sets. Function score is typically returned as part of the query result, used in the ORDER BY clause, or both.

SCORE Syntax

score(label NUMBER) RETURN NUMBER

In Example 12-9, score(10) returns the score for each row in the result set. Oracle SQL function score returns the relevance of a row in the result set with respect to a particular call to function contains. A call to score is linked to a call to contains by a LABEL (in this case the number 10).

SELECT score(10), id FROM purchase_orders
  WHERE contains(doc, 'lawn', 10) > 0 AND score(10) > 2
  ORDER BY score(10) DESC;

Function score always returns 0 if, for the corresponding contains expression, argument text_query does not match input_text, according to the matching rules dictated by the text index. If the contains text_query matches the input_text, then score returns a number greater than 0 and less than or equal to 100. This number indicates the relevance of the text_query to the input_text. A higher number means a better match.

If the contains text_query consists of only the HASPATH operator and a Text Path, the score is either 0 or 100, because HASPATH tests for an exact match.

Restricting the Scope of a CONTAINS Search

Oracle SQL function contains does a full-text search across the whole document, by default. In the example heres, a search for "lawn" with no structure restriction finds all purchase orders with the word "lawn" anywhere in them.

There are three ways to restrict contains queries using XML structure:

• WITHIN

• INPATH

• HASPATH

SELECT id FROM purchase_orders WHERE contains(DOC, 'lawn WITHIN comment') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, '(lawn WITHIN comment) WITHIN item') > 0;
  

SELECT id FROM purchase_orders
  WHERE contains(doc, '10 WITHIN purchaseOrder@orderDate') > 0;
  

SELECT id FROM purchase_orders
  WHERE contains(doc, '(lawn WITHIN comment) AND (electric WITHIN comment)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, '(lawn AND electric) WITHIN comment') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'lawn AND electric WITHIN comment') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'lawn AND (electric WITHIN comment)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (//items/item[@partNum="872-AA"]/comment)')  
        > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'lawnmower INPATH (/*/*/item[.//comment])') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 
                 '(electric INPATH (//comment)) INPATH (/purchaseOrder/items)')
        > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items//comment)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(DOC, 'HASPATH (/purchaseOrder//item/USPrice)') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'HASPATH (/purchaseOrder//item/USPrice="148.95")') > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc,
                 'electric
                  AND HASPATH (/purchaseOrder//item/USPrice="148.95")
                  AND 10 INPATH (/purchaseOrder/@orderDate)')
        > 0;

Projecting the CONTAINS Result

The result of a SQL query with a contains expression in the WHERE clause is always a set of rows (and possibly score information), or a projection over the rows that match the query.

If you want to return only a part of each XML document that satisfies a contains expression, then use SQL/XML function XMLQuery. The examples in this section use the XMLType table purchase_orders_xmltype.

Example 12-26 finds purchaseOrders that contain the word "electric" inside a comment element that is a descendant of the top-level element purchaseOrder. Instead of returning the ID of the row for each result, XMLQuery is used to return only the comment element.

SELECT XMLQuery('declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
                 $d/purchaseOrder//comment'
                PASSING doc AS "d" RETURNING CONTENT) "Item Comment"
  FROM purchase_orders_xmltype
  WHERE CONTAINS(doc, 'electric INPATH (/purchaseOrder//comment)') > 0;

The result of Example 12-26 is two instances of element comment. Function contains indicates which rows contain the word "electric" inside a comment element (the row with ID = 1), and function XMLQuery extracts all of the instances of element comment in the document at that row. There are two instances of element comment inside the purchaseOrder element, and the query returns both of them.

This might not be what you want. If you want the query to return only the instances of element comment that satisfy the contains expression, then you must repeat that predicate in the XQuery expression passed to XMLQuery. You do that using XPath function ora:contains. Example 12-27 illustrates this.

SELECT XMLQuery('declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
                 $d/purchaseOrder/items/item/comment
                   [ora:contains(text(), "electric") > 0]'
                PASSING doc AS "d" RETURNING CONTENT) "Item Comment"
  FROM purchase_orders_xmltype
  WHERE CONTAINS(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

Indexing with a CONTEXT Index

This section contains these topics:

• Introduction to CONTEXT Indexes

• Effect of a CONTEXT Index on CONTAINS

• CONTEXT Index Preferences

• Introduction to Section Groups

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT;

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT 
  PARAMETERS ('section group CTXSYS.PATH_SECTION_GROUP');

CREATE INDEX po_index_xmltype ON purchase_orders_xmltype(doc)
  INDEXTYPE IS CTXSYS.CONTEXT;

CREATE INDEX po_index_xmltype_table 
  ON purchase_orders_xmltype_table (OBJECT_VALUE)
  INDEXTYPE IS CTXSYS.CONTEXT;

SELECT XMLCast(XMLQuery(
                 'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
                  $p/purchaseOrder/@orderDate'
                 PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
               AS DATE) "Order Date"
  FROM purchase_orders_xmltype_table po
  WHERE contains(po.OBJECT_VALUE, 'electric INPATH (/purchaseOrder//comment)')
        > 0;

SELECT id FROM purchase_orders
  WHERE contains(doc, 'HURRY INPATH (/purchaseOrder/comment)') > 0;

BEGIN
  CTX_DDL.create_preference(PREFERENCE_NAME  =>  'my_lexer',
                            OBJECT_NAME      =>  'BASIC_LEXER');
    
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer', 
                        ATTRIBUTE_NAME   =>  'mixed_case', 
                        ATTRIBUTE_VALUE  =>  'TRUE');
    
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer', 
                        ATTRIBUTE_NAME   =>  'printjoins', 
                        ATTRIBUTE_VALUE  =>  '-,!');
END;
/

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('lexer my_lexer section group CTXSYS.PATH_SECTION_GROUP');

SELECT id FROM purchase_orders
  WHERE contains(doc, 'Hurry INPATH (/purchaseOrder/comment)') > 0;

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT 
  PARAMETERS ('section group CTXSYS.PATH_SECTION_GROUP');

Full-Text Search using XQuery Function ora:contains

The ora:contains argument text_query is a string that specifies the full-text search. The ora:contains text_query is the same as the contains text_query, with the following restrictions:

• ora:contains text_query must not include any of the structure operators WITHIN, INPATH, or HASPATH

• ora:contains text_query can include the score weighting operator weight(*), but weights are ignored

If you include any of the following in the ora:contains text_query, the query cannot use a CONTEXT index:

• Score-based operator MINUS (-) or threshold (>)

• Selective, corpus-based expansion operator FUZZY (?) or soundex (!)

Example 12-4 shows a full-text search using an arbitrary combination of Boolean operators and $ (stemming).

Matching rules are defined by the policy, policy_owner.policy_name. If policy_owner is absent, then the policy owner defaults to the current user. If both policy_name and policy_owner are absent, then the policy defaults to CTXSYS.DEFAULT_POLICY_ORACONTAINS.

Restricting the Scope of an ora:contains Query

When you use ora:contains in an XPath expression, the scope is defined by argument input_text. This argument is evaluated in the current XPath context. If the result is a single text node or an attribute, then that node is the target of the ora:contains search. If input_text does not evaluate to a single text node or an attribute, an error is raised.

The policy determines the matching rules for ora:contains. The section group associated with the default policy for ora:contains is of type NULL_SECTION_GROUP.

ora:contains can be used anywhere in an XPath expression, and its input_text argument can be any XPath expression that evaluates to a single text node or an attribute.

Projecting the ora:contains Result

If you want to return only a part of each XML document, then use function XMLQuery to project a node sequence, possibly applying XMLCast to the result to project the scalar value of a node.

Example 12-38 returns the orderDate for each purchase order that has a comment that contains the word "lawn".

SELECT XMLCast(XMLQuery(
                 'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
                  $d/purchaseOrder/@orderDate'
                 PASSING doc AS "d" RETURNING CONTENT)
               AS DATE) "Order date"
  FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment
             [ora:contains(text(), "($lawns AND wild) OR flamingo") > 0]'
          PASSING doc AS "d");

Function XMLExists restricts the result to rows (documents) where the purchaseOrder element includes some comment that contains the word "lawn". Function XMLQuery then returns the value of attribute orderDate from those purchaseOrder elements. Function XMLCast casts this result as a SQL DATE value.

If //comment had been extracted, then both comments from the sample document would have been returned, not just the comment that matches the WHERE clause.

Policies for ora:contains Queries

The CONTEXT index on a column determines the semantics of contains queries on that column. Because ora:contains does not rely on a supporting index, some other means must be found to provide many of the same choices when doing ora:contains queries. A policy is a collection of preferences that can be associated with an ora:contains query to give the same sort of semantic control as the indexing choices give to the contains user.

BEGIN
  CTX_DDL.create_policy(POLICY_NAME  =>  'my_nostopwords_policy',
                        STOPLIST     =>  'CTXSYS.EMPTY_STOPLIST');
END;
/

SELECT id FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment[ora:contains(text(), "is") > 0]'
          PASSING doc AS "d");

SELECT id FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment
             [ora:contains(text(), "is", "MY_NOSTOPWORDS_POLICY") > 0]'
          PASSING doc AS "d");

SELECT id FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment[ora:contains(text(), "HURRY") > 0]'
          PASSING doc AS "d");

BEGIN
  CTX_DDL.create_preference(PREFERENCE_NAME  =>  'my_lexer',
                            OBJECT_NAME      =>  'BASIC_LEXER');
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'MY_LEXER', 
                        ATTRIBUTE_NAME   =>  'MIXED_CASE', 
                        ATTRIBUTE_VALUE  =>  'TRUE');
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer', 
                        ATTRIBUTE_NAME   =>  'printjoins', 
                        ATTRIBUTE_VALUE  =>  '-,!');
END;
/

BEGIN
  CTX_DDL.create_policy(POLICY_NAME  => 'my_policy',
                        LEXER        => 'my_lexer');
END;
/

SELECT id FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment
             [ora:contains(text(), "HURRY", "my_policy") > 0]'
          PASSING doc AS "d");

SELECT id FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment[ora:contains(text(), "Hurry") > 0]'
          PASSING doc AS "d");

Performance of ora:contains

The ora:contains XPath function does not depend on a supporting index. ora:contains is very flexible. But if you use it to search across large amounts of data without an index, then it can also be resource-intensive. This section shows how to get the best performance from queries that include XPath expressions with XPath function ora:contains.

The examples in this section use table purchase_orders_xmltype_big. This has the same table structure and XML schema as purchase_orders_xmltype, but it has around 1,000 rows. Each row has a unique ID (in column id), and some different text in /purchaseOrder/items/item/comment. Where an execution plan is shown, it was produced using the SQL*Plus command AUTOTRACE. Execution plans can also be produced using SQL commands TRACE and TKPROF. A description of commands AUTOTRACE, trace and tkprof is outside the scope of this chapter.

This section contains these topics:

• Use a Primary Filter in the Query

• XPath Rewrite and CONTEXT Indexes

SELECT id FROM purchase_orders_xmltype_big
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment[ora:contains(text(), "constitution") > 0]'
          PASSING doc AS "d");

Execution Plan
------------------------------------------------------------------------------------------------
| Id | Operation                       | Name                      |Rows|Bytes|Cost(%CPU)| Time|
------------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT                |                           |  32|64480|686(38)|00:00:09|
|*  1| FILTER                          |                           |    |     |       |        |
|   2|TABLE ACCESS FULL                |PURCHASE_ORDERS_XMLTYPE_BIG|1161|2284K| 140(3)|00:00:02| 
|*  3|COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE    |    |     |       |        |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$" WHERE
              SYS_XMLCONTAINS(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(SYS_XQCON2SEQ(VALUE(KOKBF$)),
              '/comment/text()'),1,50),50,1,0),'constitution')>0))
   3 - filter(SYS_XMLCONTAINS(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(SYS_XQCON2SEQ(VALUE(KOKBF$)),
              '/comment/text()'),1,50),50,1,0),'constitution')>0)
 
Note
-----
   - dynamic sampling used for this statement

CREATE INDEX id_index ON purchase_orders_xmltype_big(id);

SELECT id FROM purchase_orders_xmltype_big
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/comment[ora:contains(text(), "constitution") > 0]'
          PASSING doc AS "d")
    AND id > 5;

Execution Plan
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      |Rows| Bytes |Cost(%CPU)| Time|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |  1 |  2015 |8  (13)|00:00:01|
|*  1 |  TABLE ACCESS BY INDEX ROWID|PURCHASE_ORDERS_XMLTYPE_BIG|  1 |  2015 |8  (13)|00:00:01|
|*  2 |   INDEX RANGE SCAN          |ID_INDEX                   | 10 |       |2   (0)|00:00:01|
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(EXISTSNODE(SYS_MAKEXML("PURCHASE_ORDERS_XMLTYPE_BIG"."SYS_NC00003$
       "),'/purchaseOrder/items/item/comment[ora:contains(text(), "constitution") >
              0]','xmlns:ora="http://xmlns.oracle.com/xdb"')=1)
   2 - access("ID">5)
 
Note
-----
   - dynamic sampling used for this statement

SELECT id FROM purchase_orders_xmltype
  WHERE XMLExists(
          'declare namespace ora = "http://xmlns.oracle.com/xdb"; (: :)
           $d/purchaseOrder/items/item/comment
             [ora:contains(text(), "electric") > 0]'
          PASSING doc AS "d");

CREATE INDEX po_otext_ix ON my_path_table (VALUE) INDEXTYPE IS CTXSYS.CONTEXT;
 
EXPLAIN PLAN FOR
  SELECT DISTINCT XMLCast(XMLQuery('$p/PurchaseOrder/ShippingInstructions/address'
                                   PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
                          AS VARCHAR2(256)) "Address"
    FROM po_binxml po
    WHERE XMLExists(
            '$p/PurchaseOrder/ShippingInstructions/address
               [(# ora:use_text_index #) {ora:contains(., "$(Fortieth)")} > 0]'
            PASSING po.OBJECT_VALUE AS "p");

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                         | Rows | Bytes|Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                              |    1 | 3046 |   12  (17)|00:00:01|
|   1 |  SORT GROUP BY                       |                              |    1 | 3524 |           |        |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MY_PATH_TABLE                |    2 | 7048 |    3   (0)|00:00:01|
|*  3 |    INDEX RANGE SCAN                  | SYS89559_PO_XMLINDE_PIKEY_IX |    1 |      |    2   (0)|00:00:01|
|   4 |  HASH UNIQUE                         |                              |    1 | 3046 |   12  (17)|00:00:01|
|   5 |   NESTED LOOPS                       |                              |    1 | 3046 |    8  (13)|00:00:01|
|   6 |    SORT UNIQUE                       |                              |    1 | 3034 |    6   (0)|00:00:01|
|*  7 |     TABLE ACCESS BY INDEX ROWID      | MY_PATH_TABLE                |    1 | 3034 |    6   (0)|00:00:01|
|*  8 |      DOMAIN INDEX                    | PO_OTEXT_IX                  |      |      |    4   (0)|00:00:01|
|   9 |    TABLE ACCESS BY USER ROWID        | PO_BINXML                    |    1 |   12 |    1   (0)|00:00:01|
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
   3 - access("SYS_P3"."RID"=:B1 AND "SYS_P3"."PATHID"=HEXTORAW('6F7F'))
   7 - filter("SYS_P1"."PATHID"=HEXTORAW('6F7F') AND SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1)
   8 - access("CTXSYS"."CONTAINS"("SYS_P1"."VALUE",'$(Fortieth)')>0)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
28 rows selected.

Purchase-Order XML Document, po001.xml

<?xml version="1.0" encoding="UTF-8"?>
<purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xsi:noNamespaceSchemaLocation="xmlschema/po.xsd" 
               orderDate="1999-10-20">
  <shipTo country="US">
    <name>Alice Smith</name>
    <street>123 Maple Street</street>
    <city>Mill Valley</city>
    <state>CA</state>
    <zip>90952</zip>
  </shipTo>
  <billTo country="US">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <comment>Hurry, my lawn is going wild!</comment>
  <items>
    <item partNum="872-AA">
      <productName>Lawnmower</productName>
      <quantity>1</quantity>
      <USPrice>148.95</USPrice>
      <comment>Confirm this is electric</comment>
    </item>
    <item partNum="926-AA">
      <productName>Baby Monitor</productName>
      <quantity>1</quantity>
      <USPrice>39.98</USPrice>
      <shipDate>1999-05-21</shipDate>
    </item>
  </items>
</purchaseOrder>

CREATE TABLE Statements

CREATE TABLE purchase_orders (id NUMBER, doc VARCHAR2(4000));

INSERT INTO purchase_orders (id, doc)
  VALUES (1,
          '<?xml version="1.0" encoding="UTF-8"?>
           <purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                          xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                          orderDate="1999-10-20">
             <shipTo country="US">
               <name>Alice Smith</name>
               <street>123 Maple Street</street>
               <city>Mill Valley</city>
               <state>CA</state>
               <zip>90952</zip>
             </shipTo>
             <billTo country="US">
               <name>Robert Smith</name>
               <street>8 Oak Avenue</street>
               <city>Old Town</city>
               <state>PA</state>
               <zip>95819</zip>
             </billTo>
             <comment>Hurry, my lawn is going wild!</comment>
             <items>
               <item partNum="872-AA">
                 <productName>Lawnmower</productName>
                 <quantity>1</quantity>
                 <USPrice>148.95</USPrice>
                 <comment>Confirm this is electric</comment>
               </item>
               <item partNum="926-AA">
                 <productName>Baby Monitor</productName>
                 <quantity>1</quantity>
                 <USPrice>39.98</USPrice>
                 <shipDate>1999-05-21</shipDate>
               </item>
             </items>
           </purchaseOrder>');
COMMIT;

CREATE TABLE purchase_orders_xmltype (id  NUMBER, doc XMLType);

INSERT INTO purchase_orders_xmltype (id, doc)
  VALUES (1,
          XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
                     <purchaseOrder
                       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:noNamespaceSchemaLocation="po.xsd"
                       orderDate="1999-10-20">
                       <shipTo country="US">
                         <name>Alice Smith</name>
                         <street>123 Maple Street</street>
                         <city>Mill Valley</city>
                         <state>CA</state>
                         <zip>90952</zip>
                       </shipTo>
                       <billTo country="US">
                         <name>Robert Smith</name>
                         <street>8 Oak Avenue</street>
                         <city>Old Town</city>
                         <state>PA</state>
                         <zip>95819</zip>
                       </billTo>
                       <comment>Hurry, my lawn is going wild!</comment>
                       <items>
                         <item partNum="872-AA">
                           <productName>Lawnmower</productName>
                           <quantity>1</quantity>
                           <USPrice>148.95</USPrice>
                           <comment>Confirm this is electric</comment>
                         </item>
                         <item partNum="926-AA">
                           <productName>Baby Monitor</productName>
                           <quantity>1</quantity>
                           <USPrice>39.98</USPrice>
                           <shipDate>1999-05-21</shipDate>
                         </item>
                       </items>
                   </purchaseOrder>'));
COMMIT;

CREATE TABLE purchase_orders_xmltype_table OF XMLType;

INSERT INTO purchase_orders_xmltype_table
  VALUES (
    XMLType ('<?xml version="1.0" encoding="UTF-8"?>              <purchaseOrder 
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                orderDate="1999-10-20">
                <shipTo country="US">
                  <name>Alice Smith</name>
                  <street>123 Maple Street</street>
                  <city>Mill Valley</city>
                  <state>CA</state>
                  <zip>90952</zip>
                </shipTo>
                <billTo country="US">
                  <name>Robert Smith</name>
                  <street>8 Oak Avenue</street>
                  <city>Old Town</city>
                  <state>PA</state>
                  <zip>95819</zip>
                </billTo>
                <comment>Hurry, my lawn is going wild!</comment>
                <items>
                  <item partNum="872-AA">
                    <productName>Lawnmower</productName>
                    <quantity>1</quantity>
                    <USPrice>148.95</USPrice>
                    <comment>Confirm this is electric</comment>
                  </item>
                  <item partNum="926-AA">
                    <productName>Baby Monitor</productName>
                    <quantity>1</quantity>
                    <USPrice>39.98</USPrice>
                    <shipDate>1999-05-21</shipDate>
                  </item>
                </items>
              </purchaseOrder>'));
COMMIT;

Purchase-Order XML Schema for Full-Text Search Examples

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:annotation>
    <xsd:documentation xml:lang="en">
      Purchase order schema for Example.com.
      Copyright 2000 Example.com. All rights reserved.
    </xsd:documentation>
  </xsd:annotation>
  <xsd:element name="purchaseOrder" type="PurchaseOrderType"/>
  <xsd:element name="comment" type="xsd:string"/>
  <xsd:complexType name="PurchaseOrderType">
    <xsd:sequence>
      <xsd:element name="shipTo" type="USAddress"/>
      <xsd:element name="billTo" type="USAddress"/>
      <xsd:element ref="comment" minOccurs="0"/>
      <xsd:element name="items" type="Items"/>
    </xsd:sequence>
    <xsd:attribute name="orderDate" type="xsd:date"/>
  </xsd:complexType>
  <xsd:complexType name="USAddress">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string"/>
      <xsd:element name="street" type="xsd:string"/>
      <xsd:element name="city" type="xsd:string"/>
      <xsd:element name="state" type="xsd:string"/>
      <xsd:element name="zip" type="xsd:decimal"/>
    </xsd:sequence>
    <xsd:attribute name="country" type="xsd:NMTOKEN" fixed="US"/>
  </xsd:complexType>
  <xsd:complexType name="Items">
    <xsd:sequence>
      <xsd:element name="item" minOccurs="0" maxOccurs="unbounded">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="productName" type="xsd:string"/>
            <xsd:element name="quantity">
              <xsd:simpleType>
                <xsd:restriction base="xsd:positiveInteger">
                  <xsd:maxExclusive value="100"/>
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="USPrice" type="xsd:decimal"/>
            <xsd:element ref="comment" minOccurs="0"/>
            <xsd:element name="shipDate" type="xsd:date" minOccurs="0"/>
          </xsd:sequence>
          <xsd:attribute name="partNum" type="SKU" use="required"/>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <!-- Stock Keeping Unit, a code for identifying products -->
  <xsd:simpleType name="SKU">
    <xsd:restriction base="xsd:string">
      <xsd:pattern value="\d{3}-[A-Z]{2}"/>
    </xsd:restriction>
  </xsd:simpleType>
</xsd:schema>