Using XQuery Full Text to Search XML Content

Overview

    Purpose

    In this tutorial, you create a simple XMLType table named PURCHASEORDER using the default settings, and then load 10,000 XML documents into the PURCHASEORDER table. You also use XQuery Full Text to search XML documents. In order to use XQuery Full Text, you must create an XML-aware full-text index on the documents that you want to search.

    Time to Complete

    Approximately 60 minutes.

    Introduction

    This tutorial shows you how to create an XML-aware full-text index in order to search XML content by using XQuery full-text search. It covers the following topics:

    • Create a simple XMLType table and populate it with 10,000 XML documents, and then view the table in SQL Developer.
    • Use XQuery and SQL/XML to query XML documents stored in the PURCHASEORDER table in the XDBOE schema.
    • Create an XML-aware full-text index.
    • Perform XQuery full-text searches on the PURCHASEORDER table.

    Scenario

    XQuery Full Text is an extension to W3C XQuery standard that makes it possible to perform complex full-text style search operations on the content of XML documents. Support for XQuery Full Text is available in Oracle Database 12.1.0.1.0 and later. The original XQuery language included an operator called CONTAINS that allow pattern-matching-based searches on XML content. The CONTAINS operator provides a case-sensitive substring-style search capability; that is, it returns TRUE if the source string contains exactly the set of characters contained in the target string. The XQuery Full Text specification adds the ability to perform word-based searches of XML content, with all of the common features of a text-retrieval system. XQuery Full Text includes support for word match, windowing (word must appear within n words of word), and stemming (automatically recognize related words). In order to provide an efficient implementation of XQuery Full Text, Oracle Database 12c makes use of Oracle’s Text indexing technology. In order to make use of XQuery Full Text it is necessary to create an XML-aware full-text index on the documents that are to be searched.

    Hardware and Software Requirements

    The following is a list of hardware and software requirements:

    • Oracle Database 12c Release 1 (12.1) Enterprise Edition for linux.
    • Oracle SQL Developer version 3.2 or higher (optional, you can use SQL*Plus in a terminal window).
    • Mozilla Firefox Web browser.
    • A linux text editor such as gedit.

    Prerequisites

    Before starting this tutorial, you should:

    • Install Oracle Database 12c Release 1 (12.1) Enterprise Edition for linux.
    • Have access to or have installed the sample schemas.
    • Install Oracle SQL Developer (optional, you can use SQL*Plus in a terminal window).
    • Download the DatabaseTrack.zip file which contains the script files used in this tutorial to your local drive. In this tutorial, we copied the downloaded and extracted the required DatabaseTrack.zip file to the /home/oracle/DatabaseTrack folder on a linux machine.
    • Download the xdboe_privileges.sql script which creates the new XDBOE user and grants this user some additional privileges.
    • The code examples in this tutorial use a new XDBOE schema that will be created with the setup scripts.

Initialize the Hands-On Lab

    Note: In this OBE, we are using HTTP on a standard port (80) instead of an Oracle XML DB Default port. You can use the Oracle XML DB configuration file, /xdbconfig.xml, to configure HTTP(S) to listen on any port. By default, HTTP(S) listens on a non-standard, unprotected port. To use HTTP or HTTPS on a standard port (80 for HTTP, 443 for HTTPS), see the Using HTTP(S) on a Standard Port Instead of an Oracle XML DB Default Port section (chapter 28) in the Oracle XML DB Developer's Guide 12c Release 1 (12.1) reference guide.

    Setting the HTTP Port and Configuring the Listener

      Start-up SQL*Plus. In your linux environment, start a terminal window. Double-click the Terminal icon on your desktop.

      A terminal window is displayed.

      Start SQL*Plus. Enter system as the username and your valid system password.

      You are connected to SQL*Plus.

      Set the HTTP port to the standard port 80. Use the PL/SQL procedure DBMS_XDB.sethtpport to set the HTTP(S) port number to port_number in the Oracle XML DB configuration file /xdbconfig.xml, where port_number is 80 for HTTP or 443 for HTTPS:

      Confirm that the HTTP has been set. Use the PL/SQL procedure DBMS_XDB.getHTTPPort to get the HTTP(S) port number.

      Note: For information on how to set the port number to the standard port 80 if you have a conflict in ports, see the Using HTTP(S) on a Standard Port Instead of an Oracle XML DB Default Port section (chapter 28) in the Oracle XML DB Developer's Guide 12c Release 1 (12.1) documentation reference.

      Force the database to re-register with the listener, using the following SQL statement:

      Your configuration has been set.

      Check that the listener is correctly configured. Enter the lsnrctl STATUS command at the operating system prompt(%).

      If your listener is not started, enter the following command:

      lsnrctl START

    Run the Reset Script

      Start a terminal window. Double-click the Terminal icon on your desktop.

      A terminal window is displayed.

      To run the setup (or reset it), enter the following commands on at the command prompt in a terminal window:

      $ cp /home/oracle/Desktop/DatabaseTrack/XMLDB/files/reset_xmldb $HOME

      $ chmod +x $HOME/reset_xmldb

      $ export XDB_HOL="/home/oracle/Desktop/DatabaseTrack/XMLDB"

      To start the refresh, run this command at the command prompt:

      $ ./reset_xmldb

      The following screen captures show the result of running reset_xmldb.

      In this step, you create the new XDBOE user and grant the user some additional privileges. In a terminal window, start SQL*Plus, enter system as the username and your valid system password. You are connected to SQL*Plus. Run the xdboe_privileges.sql script.

    Use SQL Developer to Establish a Connection to the XDBOE Schema

      Click the SQL Developer icon on the desktop to start the application.

      Create a database connection as user XDBOE. In the Connections tab, right-click Connections, and then select New Connection.

      The New / Select Database Connection window is displayed.

      Enter the following details in the New / Select Database Connection window:

      Connection Name: XDBOE
      UserName: XDBOE
      Password: oracle
      Hostname: localhost
      Port: 1521
      SID: orcl

      Click Test to make sure that the connection has been set correctly. Select the Save Password check box to ensure that the password is saved. Click Connect after the test status shows Success.

    Set the Autotrace Parameters

      Select Tools > Preferences. The Preferences dialog box is displayed.

      Expand the Database node, and then select Autotrace/Explain Plan parameter.

      Make sure to select the following check boxes if not already selected, and then click OK:

      • Object_Name
      • Cost
      • Cardinality
      • Predicates

Using XQuery in Oracle Database 12c Release 1 (12.1)

    In this section, you create a simple XMLType table named PURCHASEORDER using the default settings in the XDBOE schema. You then load 10,000 XML documents into the new PURCHASEORDER table. Finally, you use XQuery and SQL/XML to query XML documents stored in the PURCHASEORDER table.

    Create a Simple XMLType Table

      Using the Files tab, navigate to the location where you extracted the contents of the downloaded DatabaseTrack.zip file. Right-click the 1.1 createTable.sql script, and then select Open to display it in the SQL Worksheet area.

      Note: In this tutorial, the extracted contents of the DatabaseTrack.zip file are saved in the /home/oracle/DatabaseTrack folder on a linux machine. The .sql scripts used in this OBE are stored in the /home/oracle/DatabaseTrack/XMLDB/sql folder.

      Select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Script icon (or press the F5 key) to execute the script.

      This script creates a simple XMLType table, called PURCHASEORDER, using the default settings. In Oracle Database Release 12.1.0.1.0, this will be XMLType stored as Binary XML in a Secure File LOB. Binary XML stores the XML in a "post-parsed" native XML format that allows for efficient storage, indexing, and processing of XML. Since the format is "post-parsed", the database does not need to perform any additional parsing operations when performing XQuery operations on the XML content.

      The script then loads 10,000 XML documents into the PURCHASEORDER table. The output is displayed in the Script Output tab.

    Review the PURCHASEORDER Table in SQL Developer

      Select the Connections tab, and then click the + icon to the left of the XDBOE connection to expand it.

      Expand Tables, and then select PURCHASEORDER. On the right window pane, select the Details tab to examine the values of the NUM_ROWS and the TABLE_TYPE parameters.

      Notice the highlighted TABLE_TYPE parameter with the value XMLTYPE.

    Accessing XML Content Using XQuery

      XQuery is to XML content what SQL is to relational data. The XQuery standard was developed by the W3C and as such, it is the natural language for querying, manipulating, and updating XML content. The following section will show how to use XQuery to work with XML content stored in an Oracle database.

      Using the Files tab, right-click the 1.1 simpleQueries.sql script, and then select Open to display it in the SQL Worksheet area.

      The complete code for the 1.1 simpleQueries.sql script is as follows:

      set long 4096
      --
      -- 1. Use XQuery and fn:collection to count the documents in the PURCHASEORDER
      -- table.
      --
      select *
      from XMLTABLE(
      'count(fn:collection("oradb:/XDBOE/PURCHASEORDER"))'
      )
      /
      --
      -- 2. Use XQuery to select a single document from the PURCHASEORDER table
      --
      select *
      from XMLTABLE(
      'for $i in fn:collection("oradb:/XDBOE/PURCHASEORDER")/PurchaseOrder[Reference/text()=$REFERENCE]
      return $i'
      passing 'AFRIPP-2012060818343243PDT' as "REFERENCE"
      )
      /
      --
      -- 3. XQuery with multiple predicates, returning a single node (Reference)
      --
      select *
      from XMLTABLE(
      'for $i in fn:collection("oradb:/XDBOE/PURCHASEORDER")/PurchaseOrder[CostCenter=$CC and Requestor=$REQUESTOR and count(LineItems/LineItem) > $QUANTITY]/Reference
      return $i'
      passing 'A60' as "CC", 'Diana Lorentz' as "REQUESTOR", 5 as "QUANTITY"
      )
      /
      --
      -- 4. XQuery constructing a new summary document from the documents that match the specified predicates
      -- Also demonstrates the use of nested FOR loops, one for the set of PurchaseOrder documents, and one for
      -- the LineItem elements
      --
      select *
      from XMLTable(
      '<Summary UPC="{$UPC}">
      {
      for $p in fn:collection("oradb:/XDBOE/PURCHASEORDER")/PurchaseOrder
      for $l in $p/LineItems/LineItem[Quantity > $Quantity and Part/text() =$UPC]
      order by $p/Reference
      return
      <PurchaseOrder reference="{$p/Reference/text()}" lineItem="{fn:data($l/@ItemNumber)}" Quantity="{$l/Quantity}"/>
      }
      </Summary>'
      passing '707729113751' as UPC, 3 as "Quantity"
      )
      /
      --
      -- 5. Use XMLSerialize to format the XMLType and serialize it as a CLOB.
      -- Allows result to be viewed in products that do not support XMLType.
      -- XMLSerialize allows control over the layout of the serialized
      -- XML.
      --
      select XMLSERIALIZE(CONTENT COLUMN_VALUE AS CLOB INDENT SIZE=2)
      from XMLTable(
      '<Summary UPC="{$UPC}">
      {
      for $p in fn:collection("oradb:/XDBOE/PURCHASEORDER")/PurchaseOrder
      for $l in $p/LineItems/LineItem[Quantity > $Quantity and Part/text() =$UPC]
      order by $p/Reference
      return
      <PurchaseOrder reference="{$p/Reference/text()}" lineItem="{fn:data($l/@ItemNumber)}" Quantity="{$l/Quantity}"/>
      }
      </Summary>'
      passing '707729113751' as UPC, 3 as "Quantity"
      )
      /
      --
      -- 6. Using XMLTable to create an in-line relational view from the documents that match the XQuery expression.
      --
      select *
      from xmlTable(
      'for $p in fn:collection("oradb:/XDBOE/PURCHASEORDER")/PurchaseOrder
      for $l in $p/LineItems/LineItem[Quantity > 3 and Part/text() = "707729113751"]
      return
      <Result ItemNumber="{fn:data($l/@ItemNumber)}">
      {
      $p/Reference,
      $p/Requestor,
      $p/User,
      $p/CostCenter,
      $l/Quantity
      }
      <Description>{fn:data($l/Part/@Description)}</Description>
      <UnitPrice>{fn:data($l/Part/@UnitPrice)}</UnitPrice>
      <PartNumber>{$l/Part/text()}</PartNumber>
      </Result>'
      columns
      SEQUENCE for ordinality,
      ITEM_NUMBER NUMBER(3) path '@ItemNumber',
      REFERENCE VARCHAR2( 30) path 'Reference',
      REQUESTOR VARCHAR2(128) path 'Requestor',
      USERID VARCHAR2( 10) path 'User',
      COSTCENTER VARCHAR2( 4) path 'CostCenter',
      DESCRIPTION VARCHAR2(256) path 'Description',
      PARTNO VARCHAR2( 14) path 'PartNumber',
      QUANTITY NUMBER(12,4) path 'Quantity',
      UNITPRICE NUMBER(14,2) path 'UnitPrice'
      )
      /
      --
      -- 7. Joining relational and XML tables using XQuery.
      --
      select REQUESTOR, DEPARTMENT_NAME
      from HR.EMPLOYEES e, HR.DEPARTMENTS d,
      XMLTABLE(
      'for $p in fn:collection("oradb:/XDBOE/PURCHASEORDER")/PurchaseOrder
      where $p/User=$EMAIL and $p/Reference=$REFERENCE
      return $p'
      passing 'AFRIPP-2012060818343243PDT' as "REFERENCE", e.EMAIL as "EMAIL"
      COLUMNS
      REQUESTOR path 'Requestor/text()',
      USERNAME path 'User'
      )
      where e.DEPARTMENT_ID = d.DEPARTMENT_ID
      /

      Select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Script icon (or press the F5 key) to execute the script.

      This script demonstrates how to use the XMLTable() operator to perform XQuery operations on XML content stored in the Oracle database. The result of an XQuery operation is a sequence of zero or more nodes. The XMLTable() operator takes each node returned by the XQuery operation and converts it into a row consisting of a single column of XMLType, allowing the result of the XQuery to be understood by tools that expect a SQL result set.

      The output is displayed in the Script Output tab.

      The first query uses XQuery and the XMLTable to count the number of documents in the PURCHASEORDER table using the standard XQuery function fn:collection() to access the contents of the table. Remember that fn:collection() expects a path that identifies the set of XML documents to be processed. In this case, the path is prefixed with the protocol oradb, indicating that the components of the path should be interpreted as DATABASE_SCHEMA and TABLE.

      The second query uses XQuery to select a single document from the PURCHASEORDER table. The query uses predicates to restrict the documents that are returned. In this case the predicate on the Reference element uniquely identifies a single document. The document is returned as an XMLType object. In order to see the XML in the SQL Developer script output tab, the XML must be serialized, or converted into a textual representation, using the XMLSerialize() function. XMLSerialize() generates a textual representation of the XML and returns it as a CLOB, BLOB or VARCHAR2 data type.

      The third query uses XQuery with multiple predicates, to return a single Reference node. The query uses A60 as the predicate on CostCenter, Diana Lorentz as the Requestor, and 5 as the Quantity.

      The fourth query uses XQuery to create a new document from the documents that match the supplied predicates. Note that the generated XMLType is not "pretty printed". Pretty Printing is a function of serialization.

      The fifth query uses XMLSerialize() to convert the document into a serialized form that is stored in a CLOB data type. This allows the result to be viewed in SQL Developer versions that do not support rendering XMLType.

      The sixth query demonstrates how you can use XMLTable to create an inline relational view from the documents that match the XQuery expression. This query shows uses the columns clause of the XMLTable operator to create an inline relational view from the documents that match the supplied predicates. In this case, the XQuery expression generates a result document from each of the PurchaseOrder documents that match the supplied predicates, and then the columns clause maps elements and attributes in the result document into the columns of the inline view. This allows a conventional relational result to be created by executing an XQuery operation on XML content.

      The last query joins relational and XML tables using XQuery.

Searching XML Content Using XQuery Full Text

    XQuery Full Text is an extension to W3C XQuery standard that makes it perform complex full-text style search operations on the content of XML documents. Support for XQuery Full Text is available in Oracle Database 12.1.0.1.0 and later. The original XQuery language included an operator called CONTAINS that allow pattern-matching-based searches on XML content. The CONTAINS operator provides a case-sensitive substring-style search capability; that is, it returns TRUE if the source string contains exactly the set of characters contained in the target string. The XQuery Full Text specification adds the ability to perform word-based searches of XML content, with all of the common features of a text-retrieval system. XQuery Full Text includes support for word match, windowing (word must appear within n words of word), and stemming (automatically recognize related words). In order to provide an efficient implementation of XQuery Full Text, Oracle Database 12c makes use of Oracle’s Text indexing technology. In order to make use of XQuery Full Text it is necessary to create an XML-aware full-text index on the documents that are to be searched.

    This section demonstrates how to create an XML Aware full-text index.

    Using the Files tab, right-click the 2.1 XQuery-FTIndex.sql script, and then select Open to display it in the SQL Worksheet area.

    Select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Script icon (or press the F5 key) to execute the script. The output is displayed in the Script Output tab.

    The first step is to define the section group and storage preferences that will be used by the index. These items are managed using methods provided by the package CTX_DLL. In order to use this package the user must have been granted the role CTXAPP.

    The second step is to create a CTXSYS.CONTEXT index based on the section group and storage preferences.

    Note: The results of running the 2.1 XQuery-FTIndex.sql script might be different than the preceding screen capture.This depends on whether or not you already created the structures.

    Once the index has been created it can be used to optimize XQuery Full-Text operations. Currently XQuery Full-Text searches must always be performed via the XMLExists operator, to ensure that the optimizer uses the Full Text index to locate which documents match the specified search conditions. As per the XQuery standard, by default all XQuery Full-Text operations in Oracle Database 12c are case-insensitive.

    The next section shows how to perform XQuery Full-Text searches on XML Content stored in Oracle XML DB. Using the Files tab, right-click the 2.2 XQuery-FTQueries.sql script, and then select Open to display it in the SQL Worksheet area.

    Highlight Query 1, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    This query performs a search for an exact match on a phrase. The index cannot be used because the comparison is case-sensitive. No results are returned because the source is in mixed case and the target is in uppercase.

    The complete code for query # 1 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address[city=$PHRASE]'
    passing OBJECT_VALUE as "P",
    'OXFORD' as "PHRASE"
    )
    /

    Highlight Query 2, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    This query searches for an exact match on a phrase. The index cannot be used because the comparison is also case-sensitive; However, results are returned because the source and target are an exact match.

    The complete code for query # 2 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address[city=$PHRASE]'
    passing OBJECT_VALUE as "P",
    'Oxford' as "PHRASE"
    )
    /

    Highlight Query 3, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    The query uses the XQuery contains() operator to search for an exact match on a phrase. The index is not used because the comparison is case-sensitive, and contains performs a substring-type match, searching for the target string anywhere in the specified source. For word searches, this leads to false positives when a word in the source contains the target string. Case sensitivity also leads to false negatives where the target appears in the specified source but in a different case from the case used to define the target.

    There are a number of limitations to this kind of search. The contains() operator performs a substring-style of match, searching for the target string anywhere in the specified source. This kind of operation cannot easily be optimized by using any index. When thinking in text-retrieval terms, this kind of search often leads to false positives when a word in the source contains the target string. The case-sensitive nature of the contains() operator also leads to false negatives unless the programmer takes care to ensure that comparison of source and target is performed in a non-case-sensitive manner. As can be seen in the results, a search for the word sport has returned documents that contain the term transportation and omitted documents that contain the word Sporting.

    The complete code for query # 3 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address/street[contains(.,$PHRASE)]'
    passing OBJECT_VALUE as "P",
    'sport' as "PHRASE"
    )
    /

    Highlight Query 4, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    This query uses the XQuery Full Text contains text operation instead of the contains() operator. The contains text operation searches for the target as a word in the source. Because the comparison is now based on words, rather than strings, a text index can be used to optimize the search. In this case, there are no results because neither transportation nor Sporting are an exact match for the phrase sport.

    The complete example for query # 4 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address/street[. contains text {$PHRASE}]'
    passing OBJECT_VALUE as "P",
    'sport' as "PHRASE"
    )
    /

    Highlight Query 5, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    This query uses the XQuery Full Text using stemming operation. The using stemming operation searches for any word related to the target as a word in the source. Because the comparison is now based on related words rather than exact words, the query returns documents that contain the word Sporting because sport is treated as a stem of Sporting.

    An XQuery Full Text contains text searches on a phrase with stemming. In this example, the index is used because contains text comparisons are non-case-sensitive. Results are returned because stemming identified that sport is a stem for sporting.

    The complete code for query # 5 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address/street[. contains text {$PHRASE} using stemming]'
    passing OBJECT_VALUE as "P",
    'sport' as "PHRASE"
    )
    /

    Highlight Query 6, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    The sixth query show that XQuery Full Text can be used to search fragments, as well as leaf-level nodes. In this query the complex element Address is being searched for the word Oxford. The query returns documents where any child of address contains the target word. In this example the elements street and city contain the word Oxford.

    An XQuery Full Text contains text searches on a phrase with stemming. In this example, the index is used because contains text comparisons are non-case-sensitive. Results are returned because stemming identified that sport is a stem for sporting.

    The index is used in this example because contains text comparisons are non-case-sensitive.

    The complete code for query # 6 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address[. contains text {$PHRASE}]'
    passing OBJECT_VALUE as "P",
    'Oxford' as "PHRASE"
    )
    /

    Highlight Query 7, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    The seventh query shows the use of the ftand operator to search for the presence of two words. The words do not need to be adjacent and do not need to be in any particular order.

    The index is used because contains text comparisons are non-case-sensitive. Results are returned because stemming identified centre as a stem for center.

    The complete code for query # 7 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address[. contains text {$PHRASE1} ftand {$PHRASE2} using stemming]'
    passing OBJECT_VALUE as "P",
    'Oxford' as "PHRASE1",
    'Center' as "PHRASE2"
    )
    /

    Highlight Query 8, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    The eighth query shows how to add a window to the ftand operator. A window makes it possible to control how many words are allowed to exist between the two terms that are being searched on. In this case the window is restricted to 2 words and consequently no documents are returned.

    The complete code for query # 8 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address[. contains text {$PHRASE1} ftand {$PHRASE2} using stemming window 2 words]'
    passing OBJECT_VALUE as "P",
    'Science' as "PHRASE1",
    'Magdalen' as "PHRASE2"
    )
    /

    Highlight Query 9, select the XDBOE database connection from the Choose Db Connection drop-down list, and then click the Run Statement icon to execute the query. The output is displayed in the Query Result tab.

    This query demonstrates how expanding the size of the window allows the search to return documents that were eliminated from the result set by the narrow window used in the previous query.

    The index is used because the contains text comparison is non-case-sensitive. The window clause specifies that the words must appear within 6 words of each other. Results are returned since the window is large enough.

    The complete code for query # 9 is as follows:

    select distinct STREET, CITY
    from PURCHASEORDER,
    XMLTABLE(
    '$P/PurchaseOrder/ShippingInstructions/Address'
    passing OBJECT_VALUE as "P"
    COLUMNS
    STREET VARCHAR2(64) PATH 'street',
    CITY VARCHAR2(32) PATH 'city'
    )
    where XMLExists(
    '$P/PurchaseOrder/ShippingInstructions/Address[. contains text {$PHRASE1} ftand {$PHRASE2} using stemming window 6 words]'
    passing OBJECT_VALUE as "P",
    'Science' as "PHRASE1",
    'Magdalen' as "PHRASE2"
    )
    /

Summary

    In this tutorial, you have learned how to:

    • Create a simple XMLType table and populate it with 10,000 XML documents, and then view the table in SQL Developer.
    • Use XQuery and SQL/XML to query XML documents stored in the PURCHASEORDER table in the XDBOE schema.
    • Create an XML-aware full-text index.
    • Perform XQuery full-text searches on the PURCHASEORDER table.

    Resources

    • For additional information on Oracle XML DB:
      Attend the Oracle Database 12c: Use XML DB 5-day course.
      See the Oracle XML DB Developer's Guide 12c Release 1 (12.1) documentation reference.
    • For additional information on using HTTP on a standard port (80) instead of an Oracle XML DB Default port, see the Using HTTP(S) on a Standard Port Instead of an Oracle XML DB Default Port section (chapter 28) in the Oracle XML DB Developer's Guide 12c Release 1 (12.1) documentation reference.
    • For useful XML DB white papers, technical presentations, demos, and hands-on practices, access the Oracle XML DB Home Page.
    • To learn more about XML DB , refer to the additional OBEs in the Oracle Learning Library OLL Web site OLL .

    Credits

    • Lead Curriculum Developer: Lauran K. Serhal
    • Main Contributor: Mark Drake

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.