XQuery and the Oracle XML DB Repository

In my previous blog entry Specifying a Document in XQLPlus, I discussed the importance of specifying the file:// protocol in front of a file system path when trying to access an external file using Oracle XML DB's implementation of the XQuery standard function fn:doc. The reason that you need to specify the file:// protocol to explicitly access an external file in the file system is that Oracle database provides an Oracle XML DB repository with file-like path addresses and the file:// designation instructs the XQuery engine to look at an external file rather than looking in the repository.

The Oracle documentation Using XQuery with Oracle XML DB (part of the Oracle XML DB Developer's Guide) and the Oracle Technology Network (OTN) article Querying, Constructing, and Transforming XML with Oracle XQuery, and OTN article XML: Getting to XML each discuss this repository and how it can be used and accessed.

Perhaps the most definitive source of information on the Oracle XML DB repository is in Chapter 20 ("Accessing Oracle XML DB Repository Data") of the Oracle XML DB Developer's Guide (11g PDF). This section explains the need to create new "folders" in the repository before trying to insert documents into them and warns against placing anything under the /sys portion of the repository.

An easy way to see the difference between accessing XML in the Oracle XML DB repository using fn:doc and accessing an external XML file using fn:doc is to do both using the XQLPlus tool. To demonstrate accessing both external files and repository XML with fn:doc, I first need to place XML in both locations.

For the XML file in the file system, I will be using a subset of an XML file used in a previous blog entry. The file will be called C:\xquery\xmlSource\planets-nomoons.xml. That file is shown next:

<?xml version = '1.0'?>
<!-- Note that all data here is not meant to be factual, but is instead intended
     to illustrate XQuery principles. -->
<Planets>
  <Planet name="Mercury"
          minDistanceFromSunMK="46"
          maxDistanceFromSunMK="70"
          class="planet" />
  <Planet name="Venus"
          minDistanceFromSunMK="108"
          maxDistanceFromSunMK="109"
          class="planet" />
  <Planet name="Earth"
          minDistanceFromSunMK="146"
          maxDistanceFromSunMK="152"
          class="planet" />
  <Planet name="Mars"
          minDistanceFromSunMK="205"
          maxDistanceFromSunMK="249"
          class="planet" />
  <Planet name="Jupiter"
          minDistanceFromSunMK="741"
          maxDistanceFromSunMK="817"
          class="planet" />
  <Planet name="Saturn"
          minDistanceFromSunMK="1350"
          maxDistanceFromSunMK="1500"
          class="planet" />
  <Planet name="Uranus"
          minDistanceFromSunMK="2700"
          maxDistanceFromSunMK="3000"
          class="planet" />
  <Planet name="Neptune"
          minDistanceFromSunMK="4460"
          maxDistanceFromSunMK="4540"
          class="planet" />
  <Planet name="Pluto"
          minDistanceFromSunMK="7376"
          maxDistanceFromSunMK="4437"
          class="dwarf" />
</Planets>

With the above XML saved to the file, I'll now move onto placing this XML into the repository using the built-in PL/SQL stored function DBMS_XDB.createResource. Alternate ways of accessing the Oracle XML DB Repository include via Java, SQL, FTP, and HTTP/servlets. The code below, when run in SQL*Plus using PL/SQL, places the same XML into the XML DB repository.

DECLARE
  populateResponse BOOLEAN;
  planetsXmlString VARCHAR2(10000) :=
    '<?xml version = "1.0"?>
<Planets>
  <Planet name="Mercury"
          minDistanceFromSunMK="46"
          maxDistanceFromSunMK="70"
          class="planet" />
  <Planet name="Venus"
          minDistanceFromSunMK="108"
          maxDistanceFromSunMK="109"
          class="planet" />
  <Planet name="Earth"
          minDistanceFromSunMK="146"
          maxDistanceFromSunMK="152"
          class="planet" />
  <Planet name="Mars"
          minDistanceFromSunMK="205"
          maxDistanceFromSunMK="249"
          class="planet" />
  <Planet name="Jupiter"
          minDistanceFromSunMK="741"
          maxDistanceFromSunMK="817"
          class="planet" />
  <Planet name="Saturn"
          minDistanceFromSunMK="1350"
          maxDistanceFromSunMK="1500"
          class="planet" />
  <Planet name="Uranus"
          minDistanceFromSunMK="2700"
          maxDistanceFromSunMK="3000"
         class="planet" />
  <Planet name="Neptune"
          minDistanceFromSunMK="4460"
          maxDistanceFromSunMK="4540"
          class="planet" />
  <Planet name="Pluto"
          minDistanceFromSunMK="7376"
          maxDistanceFromSunMK="4437"
          class="dwarf" />
</Planets>';
BEGIN
  populateResponse := DBMS_XDB.createResource(
    '/public/planetswithnomoons.xml',
    planetsXmlString );
COMMIT;
END;
/

Running the above in SQL*Plus places the planets XML source into the XML DB repository. I can confirm that this was successfully placed into the repository with the following SQL*Plus query using the XMLQuery SQL/XML function (click on image to see larger version):



With the Planets XML in both a file and in the XML DB repository, I can now run XQuery expressions over both sources via XQLPlus using the fn:doc command.

The next image (click on it to see larger version) shows the output from running a simple XQuery expression against the XML stored in the repository. The example also demonstrates that a SQL connection is required and that set server on must be executed within XQLPlus before running the command to access the repository. The SQL connection and the server setting will not be required when we access an external file.



The next example demonstrates the identical XQuery expression with the one change being the specification of the document over which to bind. In the previous example, it was a repository item and no protocol was required. In the next example, the file:/// protocol is required to access an external file on the file system. No SQL connection is necessary for this external file example and I don't need to set server on either.



When running XMLPlus as shown in the last example, I really didn't need to include the Oracle JDBC JAR on the classpath (even though I did to be consistent with the previous example). In other words, XQLPlus needs the JDBC driver class for accessing the XML in the XML DB repository, but does not need it for accessing XML in an external file. As pointed out earlier, the XML DB repository access also required a sqlconn SQL/JDBC connection to be specified and required the setting of set server on. XQLPlus access of the external file did not require the JDBC driver, the SQL/JDBC connection, or the turning on of the "server" option.

Finally, note the output differences between running these two nearly identical examples in XQLPlus. These differences are most likely due to the XML representation stored in the respective sources (repository versus external text/XML file).