Using Oracle XML DB Repository as a Filesystem

DBA: XML

Using Oracle XML DB Repository as a Filesystem
by Yuli Vasiliev

Learn how to manage XML content stored in Oracle XML DB, whether you use SQL or not.

Downloads for this article: · Oracle Database 10g · Sample Code

Oracle XML DB repository, also referred to as XML repository, is a significant feature in Oracle XML DB architecture. With XML DB repository you can use industry-standard internet protocols such as FTP, HTTP, and WebDAV to move XML content in and out of the Oracle Database. At the same time, you can employ Oracle XML DB resource APIs to manage XML data stored in the repository programmatically. Finally, it is important to note that Oracle XML DB can be still queried from SQL—XML content stored in the database can be accessed and updated with standard SQL commands such as SELECT, UPDATE, and INSERT.

Oracle XML DB Repository Architecture

To the internet protocols mentioned above, Oracle XML DB repository is essentially a hierarchical filesystem on the server side. Like a conventional filesystem, Oracle XML DB repository consists of files and folders that, in this case, are referred to as resources. To be reachable, each resource in XML repository has one or more associated pathnames that are similar to pathnames in the UNIX filesystem. Specifically, a pathname in XML repository begins with the root element / and consists of the path elements separated by a /. Here is an example of the repository resource pathname:

/sys/acls/bootstrap_acl.xml

The foldering feature in Oracle XML DB allows applications to interact with the database as if the database contents were stored in a filesystem.

Turning back to SQL-based mechanisms, it is interesting to note that the metadata and data corresponding to a repository resource are stored in a single row in the XDB$RESOURCE table owned by the XDB database user. You never use the XDB$RESOURCE table to access or update metadata and documents stored in the repository. Instead, you should use two public views: RESOURCE_VIEW and PATH_VIEW. Using a RES virtual column, available in both views, you can access and manipulate resources stored in XML repository with SQL statements grounded on a path notation. In the following example, the query lists the contents of the /sys/acls repository folder:

SELECT path
FROM PATH_VIEW
WHERE under_path(RES,'/sys/acls') = 1;

PATH
----------------------------------
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml

Oracle XML DB repository can be thought of as a filesystem whose metadata and data are stored in the database. In essence, this means that Oracle XML DB uses the full power of Oracle Database when managing both metadata and data stored in the repository.

Privileges Required for Working with XML Repository Resources

You might be asking yourself what roles a database user must be granted to work with Oracle XML DB repository. The answer depends on the tasks to be performed. For instance, FULL ACCESS on all resources within the repository is granted to the XDBADMIN and DBA roles. The READ privilege on all repository resources is granted by default to all database users. Specifically, to connect to XML DB repository, a database user must be granted at least the CONNECT role. In this case, the user can connect to the repository by means of, say, FTP protocol and then navigate within the repository hierarchy and even download stored documents from (but not upload them to) the repository to the local filesystem if necessary. This is the default behavior, which can be changed by use of an ACL-based security mechanism, which is explained later in this article.

For security purposes, each resource in XML repository has an associated access control list (ACL) that determines who can access the resource. The privileges defined in the ACL are checked just before a user is allowed to access the resource. The interesting thing about all of this is that ACLs themselves are resources stored in the repository as XML schema-based documents and can be found in the /sys/acls folder.

These are the ACLs supplied with Oracle XML DB:

• bootstrap_acl.xml—grants READ privilege to all users and FULL ACCESS to the XDBADMIN and DBA roles
• all_all_acl.xml—grants all privileges to all users
• all_owner_acl.xml—grants all privileges to the resource owner
• ro_all_acl.xml—grants READ privilege to all users

When you create a new resource in the repository, a particular ACL is associated with that resource. By default, a resource is associated with the ACL on its parent folder.

At first glance, you may find the ACL-based security mechanism discussed here too complex to understand. However, it becomes clear if you remember that all repository resources are stored in the XDB$RESOURCE table owned by the XDB database user. Similarly, each ACL is stored as a row in the XDB$ACL table owned by the same user. The relationship between these two tables is based on an object-relational mechanism with REFs: Every row (resource) in the XDB$RESOURCE table contains a REF to the appropriate row (ACL) in the XDB$ACL table. In practice, this means that you can query resources along with the corresponding ACLs from SQL. For instance, you can always learn the path of the ACL that protects a given resource by issuing a RESOURCE_VIEW

SELECT a.any_path FROM resource_view a
  WHERE sys_op_r2o(extractValue(a.res, '/Resource/XMLRef')) =
   (SELECT extractValue(r.res, '/Resource/ACLOID') FROM resource_view r
    WHERE r.any_path='/sys/schemas');

ANY_PATH
----------------------------
/sys/acls/bootstrap_acl.xml

As mentioned earlier, FULL ACCESS on all repository resources is granted to the XDBADMIN and DBA roles, and READ privileges are granted to all database users by default. In practice, this means that you can use either XDBADMIN or DBA role in order to get all privileges on all resources stored in the repository if you don't want to delve into security issues. However, keep in mind that that is not always a good idea in terms of security. You might want to grant all privileges only for a given resource or certain resources to a particular user or group. One way to do that is to create a new ACL that grants all privileges on a given resource to a particular user or group and then associate that ACL with the resource. The next section gives an example of how to do this.

Accessing XML Repository Programmatically

As mentioned earlier, you can use Oracle XML DB Resource APIs to access and manipulate repository resources programmatically. This section illustrates how you can use the DBMS_XDB PL/SQL package to access XML repository. To run the sample code presented in this section, I used SQL*Plus.

To start with, let's walk through an example that illustrates how you can create an ACL by using the DBMS_XDB.createResource() function. Before beginning, you need to create a new database user to work with: Create a database user, say USR, and grant the RESOURCE and CONNECT roles to that user.

Now you create an ACL that grants all privileges on the /home/usr/employee folder to the newly created user USR. This can be done using the following PL/SQL code, which is executable by any user with the XDBADMIN or DBA role:

DECLARE
  r BOOLEAN;
BEGIN
  r := DBMS_XDB.createResource('/sys/acls/all_usr_acl.xml',
  '<acl description="empl_acl"
  xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
  http://xmlns.oracle.com/xdb/acl.xsd">
  <ace>
    <principal>USR</principal>
    <grant>true</grant>
    <privilege>
      <all/>
    </privilege>
    </ace>
  </acl>');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

If you have been granted neither the XDBADMIN nor the DBA role when trying to execute the above code, you'll get the following error message:

ERROR at line 1:
ORA-31050: Access denied
ORA-06512: at "XDB.DBMS_XDB", line 73
ORA-06512: at line 4

Once an ACL is created, you can use it to protect a resource or resources stored in the repository. In the following example, you first create the /home/usr folder and then associate that folder with the ACL stored in the all_usr_acl.xml file. Again, to run this code, the user must be granted the XDBADMIN or the DBA role.

DECLARE
  r BOOLEAN;
BEGIN
  r:=DBMS_XDB.createFolder('/home/usr');
  DBMS_XDB.setAcl('/home/usr', '/sys/acls/all_usr_acl.xml');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

As a result, you have the /home/usr repository folder protected by the ACL stored in the all_usr_acl.xml file. Only USR and users granted the DBA role have all privileges on the /home/usr folder. It is interesting to note that even users who are granted the XDBADMIN role have no privileges on that resource, because you have defined such behavior in all_usr_acl.xml.

Now you can create subfolders in /home/usr. As stated earlier, each new resource is associated by default with the ACL on its parent folder. In our case, this means that all resources inserted in the /home/usr folder are associated by default with the ACL defined in all_usr_acl.xml. In the following PL/SQL code, you create several folders in the repository, using the DBMS_XDB.createFolder() procedure. Remember that you must be connected as USR to execute this code (as well as all other code presented in the rest of this article).

DECLARE
  r BOOLEAN;
BEGIN
  r:=DBMS_XDB.createFolder('/home/usr/xsd');
  r:=DBMS_XDB.createFolder('/home/usr/xsl');
  r:=DBMS_XDB.createFolder('/home/usr/employee');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

This sounds easy, and it is. Alternatively, you might use FTP or WebDAV interfaces to create resources in the repository. For instance, to create a new folder in the repository by using FTP, you would use the FTP mkdir command. Later, this article discusses in detail an FTP-based solution for managing repository resources.

Building an XML Schema to Validate XML Documents

In this section, you develop an annotated XML schema that is designed to validate employee XML documents. Then you put that XML schema as a file into the home/usr/xsd folder in the repository. Finally, you have to register the newly created XML schema.

As you learned from the previous section, one way to create a file in the repository is to use the DBMS_XDB.createResource function. Another method is to upload the necessary file from a local filesystem, using one of the internet protocols supported by Oracle XML DB. Here is how to upload a file into the repository, using FTP protocol. First create an XSD file containing an XML Schema and save it onto your local filesystem. Specifically, you might want to create the following employee.xsd file:

<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           version="1.0" >

  <xs:element name="EMPLOYEE" type="EmplType" 
              xdb:defaultTable="EMPLOYEE"/>

  <xs:complexType name="EmplType" xdb:SQLType="EMPL_T">
    <xs:sequence>
      <xs:element name="EMPLNO" type="EmplNoType" minOccurs="1" 
                  xdb:SQLName="EMPLNO"/>
      <xs:element name="ENAME" type="EnameType" xdb:SQLName="ENAME"/>
      <xs:element name="TITLE" type="TitleType" 
                  xdb:SQLName="TITLE"/>
    </xs:sequence>
  </xs:complexType>

  <xs:simpleType name="EmplNoType">
    <xs:restriction base="xs:integer"/>
  </xs:simpleType>

  <xs:simpleType name="EnameType">
    <xs:restriction base="xs:string">
      <xs:minLength value="2"/>
      <xs:maxLength value="30"/>
    </xs:restriction>
  </xs:simpleType>

  <xs:simpleType name="TitleType">
    <xs:restriction base="xs:string">
      <xs:minLength value="2"/>
      <xs:maxLength value="30"/>
    </xs:restriction>
  </xs:simpleType>

</xs:schema>

The above XML schema uses an annotation mechanism that allows you to control the mapping between the XML schema and the SQL object model. In particular, you map the XML schema data type EmplType to the SQL type empl_t and specify the employee table of XMLType to be used to store the contents of employee XML documents. When an employee XML document is validated against the above XML schema, Oracle XML DB decomposes the document contents and stores them as an instance of empl_t in the employee table. This is called "structured storage." Using structured storage allows you to lower storage requirements, create a conventional B-tree index, and use in-place updates.

Using FTP to Access XML Repository

After you have created the employee.xsd file, copy it to the user home directory on your computer. If you are a Windows user and no home directory is specified, the %SystemDrive% is assumed to be the home directory.

The following example illustrates how you can upload the XML schema stored in the employee.xsd file into the repository by using a standard command-line FTP tool. Note that you use the database user USR to connect to the FTP server.

ftp> open localhost 2100
Connected to localhost.
220 localhost FTP Server (Oracle XML DB/Oracle Database 10g Enterprise Edition
Release 10.1.0.2.0) ready.
User (localhost:(none)): usr
331 pass required for USR
Password:
230 USR logged in

ftp> cd /home/usr/xsd
250 CWD Command successful

ftp> put employee.xsd
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 1136 bytes sent in 0.00 Seconds 1136000.00Kbytes/sec

ftp> quit
221 QUIT Goodbye.

This looks like you simply copy a file from one folder in your local filesystem to another. However, to make Oracle aware of the XML schema, you must register it, using the following PL/SQL code:

BEGIN
DBMS_XMLSCHEMA.REGISTERSCHEMA(
  'http://localhost:8080/home/usr/xsd/employee.xsd',
  xdbURIType('/home/usr/xsd/employee.xsd').getClob(), TRUE, TRUE, FALSE, TRUE);
END;
/

PL/SQL procedure successfully completed.

When registering the schema, Oracle automatically creates the empl_t object type and the employee table of XMLType under the USR database schema. As mentioned earlier, you use the XMLType employee table to store the contents of employee XML documents. By now the employee table is empty. To make sure everything goes as planned, you can issue the following query that tells you the number of rows stored in the employee table:

SELECT COUNT(*) FROM employee;

  COUNT(*)
----------
         0

Alternatively, you might issue the following query:

SELECT COUNT(*) FROM resource_view r
WHERE under_path(r.res, '/home/usr/employee') = 1;

  COUNT(*)
----------
         0

As you can see, you use the under_Path() operator in the above SQL statement to include a path-based predicate in the WHERE clause.

Applying Database Integrity Constraints to a Table of XMLType

Before proceeding, you might want to apply database integrity constraints to the XMLType employee table. Specifically, you define the PRIMARY KEY constraint on the EMPLNO element, to ensure that the value of the EMPLNO element is NOT NULL and unique across a set of XML documents stored in the employee table. You can do this with the following SQL command:

ALTER TABLE EMPLOYEE
ADD constraint EMPLNO_IS_PRIMARYKEY
PRIMARY KEY (xmldata."EMPLNO");

Table altered.

XMLType Storage and Repository

Up to now, this article has dealt mostly with techniques that can be used when working with Oracle XML DB repository. Specifically, you have learned how to create a resource in the repository from PL/SQL code and how repository resources are protected. Also, you have learned how to interact with XML repository through FTP protocol.

Now let's take a look at another significant feature of Oracle XML DB architecture: XMLType storage. Like XML repository, XMLType storage enables you to manage XML content stored in Oracle XML DB. With XMLType storage, however, you explicitly query XMLType tables and views by using standard SQL commands, whereas with XML repository, you manage only repository resources and Oracle implicitly queries database objects associated with those resources as necessary.

Here is an illustration of how you can populate the employee table with data by using the XMLType storage feature. The following example employs a conventional SQL mechanism based on using the INSERT statement to insert a new row into the employee table:

INSERT INTO Employee VALUES(XMLTYPE('
   <EMPLOYEE>
         <EMPLNO>1</EMPLNO>
         <ENAME>Frank Locke</ENAME>
         <TITLE>Manager</TITLE>
   </EMPLOYEE>
').createSchemaBasedXML('http://localhost:8080/home/usr/xsd/employee.xsd'));

1 row created.

Note that you use the createSchemaBasedXML() XMLType member function to create a schema-based XMLType instance from a non-schema-based XML document. To be sure that a new row has been inserted into the employee table, count the number of rows in the table again:

SELECT COUNT(*) FROM employee;

  COUNT(*)
----------
         1

As this output shows, one row has been inserted into the employee table. This means that you can insert new rows into a table of XMLType by using a standard SQL approach. In this case, however, a row stored in a table of XMLType has no corresponding resource in the repository.

Now, switching our focus to Oracle XML DB repository, let's insert a new row into the employee table by simply copying an employee XML document from a local filesystem to the repository. To do this, you first need to create an appropriate employee XML document and save it as a file in your local filesystem. For instance, you might want to create the following document:

<?xml version="1.0"?>
<EMPLOYEE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/home/usr/xsd/employee.xsd">
  <EMPLNO>2</EMPLNO> 
  <ENAME>Jane Smith</ENAME> 
  <TITLE>Programmer</TITLE> 
</EMPLOYEE>

Next, save the above XML document as a file, say employee2.xml, in the user home directory in your local filesystem. Once the file has been created, you can upload it into the repository. To do this, you can use FTP protocol, as discussed previously in the "Using FTP to Access XML repository" section. It is interesting to note that it is not necessary to upload employee XML documents into a specific repository folder such as /home/usr/employee. In fact, you can choose any available folder within the repository for this. Because an employee XML document includes the registered schema URL, Oracle XML DB "knows" how to insert the content of the uploaded document into the employee table. In other words, when you upload an employee XML document into the repository, Oracle XML DB implicitly inserts the appropriate row into the employee table. To make sure it has done so, you can issue the following query after employee2.xml has been uploaded:

SELECT COUNT(*) FROM employee;

  COUNT(*)
----------
         2

As the output shows, another row has been inserted into the employee table. This means that whether you use XML repository or XMLType storage to move a schema-based XML document in the database, Oracle XML DB stores the document contents as a row in the default table defined by the specified XML schema. In this case, the contents of employee XML documents are stored in the employee table. When you upload an employee XML document into the repository, the document contents are shredded and stored as an instance of empl_t in the employee table but the metadata about the document and the pointer to its contents (an instance of empl_t in the employee table) are stored in a single row in the XDB$RESOURCE table. This explains why integrity constraints defined on an XMLType table are enforced, whether XML is uploaded in the repository or inserted directly into the table by use of SQL.

For instance, using a standard command-line FTP tool, you will see the following error message when trying to upload in the repository two employee XML documents whose EMPLNO elements have the same value:

550 - Error Response
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraints (USR.EMPLNO_IS_PRIMARYKEY) violated
550 End Error Response

Transactions

Whether you use XMLType storage or XML repository to manipulate data stored in Oracle XML DB, all SQL and PL/SQL operations you perform are transactional. This means that all changes made through either SQL or PL/SQL become permanent and visible to other database users when the transaction has been committed. This allows you to combine SQL statements within a logical unit of work and explicitly commit the transaction or roll back if necessary.

Conversely, when you use protocols such as FTP or WebDAV to manipulate data stored in Oracle XML DB repository, you cannot explicitly control transactions. In such a case, you might find it useful to think of XML repository as a filesystem. Like a conventional filesystem, Oracle XML DB repository makes permanent the changes made through FTP or WebDAV once a single operation on the resource has been completed.

Generating a Set of XML Documents from SQL Data

This section discusses how to generate XML documents from the data returned by a SQL query.

Assume that we have a traditional relational table empls defined as follows:

DESC EMPLS

Name                               Null?    Type
---------------------------------- -------- ------------
EMPLNO                             NOT NULL NUMBER
ENAME                                       VARCHAR2(50)
TITLE                                       VARCHAR2(50)

Also assume that the empls table has several rows:

SELECT * FROM empls;

    EMPLNO ENAME             TITLE
---------- ----------------- ------------------
       117 Maya Silver       Programmer
       189 John Polonski     Manager
       245 Ben Jacobson      Manager
...

Now, suppose that we have to generate XML documents from the above relational data. Obviously, the above table may contain many more rows in reality. Therefore, if you create a large XML document to store all the generated XML content, working with that document in the future will not be efficient. You may find it more efficient to generate a set of smaller XML documents instead of a large one. For instance, the following example sets the maximum number of rows to be retrieved for each fetch to 1:

DECLARE
  ctxHndl DBMS_XMLGEN.CTXHANDLE;
  XMLdoc XMLType;
  counter INTEGER;
BEGIN
  ctxHndl:= DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM empls ORDER BY emplno');
  DBMS_XMLGEN.SETMAXROWS(CtxHndl, 1);
  DBMS_XMLGEN.setRowSetTag(CtxHndl, NULL);
  DBMS_XMLGEN.setRowTag(CtxHndl,'EMPLOYEE');
  counter:=0;
  LOOP
    counter:=counter+1;
    XMLdoc:= DBMS_XMLGEN.GETXMLTYPE(ctxHndl);
    EXIT WHEN DBMS_XMLGEN.GETNUMROWSPROCESSED(ctxHndl)=0;
    INSERT INTO Employee VALUES(XMLdoc.createSchemaBasedXML('http://localhost:8080/home/usr/xsd/employee.xsd'));
   END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

As this code shows, you use the INSERT statement to insert the generated XML content into the employee table and you invoke the XMLType createSchemaBasedXML() method to explicitly identify the XML schema against which to validate the generated XML content.

Alternatively, you might use XML repository to insert the generated XML content into the employee table. In this case, you store the generated XML documents as resources (files) in the repository, by using the DBMS_XDB.createResource() function. Before you run the code shown below, remember to delete the rows inserted into the employee table in the previous example to avoid integrity constraint violations.

DECLARE
  ctxHndl DBMS_XMLGEN.CTXHANDLE;
  XMLdoc XMLType;
  CLBcnt CLOB;
  counter INTEGER;
BEGIN
  ctxHndl:= DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM empls ORDER BY emplno');
  DBMS_XMLGEN.SETMAXROWS(CtxHndl, 1);
  DBMS_XMLGEN.setRowSetTag(CtxHndl, NULL);
  DBMS_XMLGEN.setRowTag(CtxHndl,'EMPLOYEE');
  counter:=0;
  LOOP
    counter:=counter+1;
    CLBcnt:= DBMS_XMLGEN.GETXML(ctxHndl);
    EXIT WHEN DBMS_XMLGEN.GETNUMROWSPROCESSED(ctxHndl)=0;
    XMLdoc:=XMLType(CLBcnt).createSchemaBasedXML('http://localhost:8080/home/usr/xsd/employee.xsd');
    IF(DBMS_XDB.CREATERESOURCE('/home/usr/employee/emp'||counter||'.xml', XMLdoc)) THEN
      DBMS_OUTPUT.PUT_LINE('Resource is created');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Cannot create resource');
    END IF;
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

As a result, when you create resources that contain the generated XML content, Oracle XML DB implicitly inserts the appropriate rows into the employee table. To be sure it has done so, you can issue the following query:

SELECT COUNT(*) FROM employee;

  COUNT(*)
----------
         5

Storing XLS Style Sheets in XML Repository

Sometimes you might want to produce formatted results from XML content. To address this issue, Oracle XML DB allows you to employ XSL (Extensible Stylesheet Language). This section discusses how to create an XSL style sheet that is designed to apply an XSL transformation to the employee XML documents generated by the DBUri Servlet from the relational data in the empls table. This technique lets you access the contents of the traditional relational table empls from a browser.

With the DBUri Servlet, you normally use the following URL to return an XML document containing the contents of a given table or view:

http://<hostname>:8080/oradb/<SCHEMA>/<TABLE>

For instance, I used the following URL to see an employee XML document containing the contents of the empls table in the USR database schema:

http://localhost:8080/oradb/USR/EMPLS

You may be asking yourself why you need to view XML from a browser. The real power of the above approach lies in the ability to apply XSL transformation to the XML content generated by the DBUri Servlet. In essence, you can transform an XML document into another XML or HTML document or a variety of other documents. The following example creates a style sheet that transforms an employee XML document into an HTML document.

DECLARE
    b BOOLEAN;
BEGIN
  b := DBMS_XDB.createResource('/home/usr/xsl/employee.xsl', 
  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:template match="/">
    <html>
     <head>
       <title>Employees</title>
     </head>
      <body>
          <table border="1" cellspacing="0">
       <tr>
          <th><b>EMPLNO</b></th>
          <th><b>ENAME</b></th>
          <th><b>TITLE</b></th>  
       </tr>
         <xsl:for-each select="EMPLS">
          <xsl:for-each select="ROW">
              <tr>
                <td><xsl:value-of select="EMPLNO"/></td>
                <td><xsl:value-of select="ENAME"/></td>
                <td><xsl:value-of select="TITLE"/></td>
              </tr>
            </xsl:for-each>
           </xsl:for-each>
          </table>
      </body>
    </html>
   </xsl:template>
  </xsl:stylesheet>');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

You use DBMS_XDB.createResource() to save the style sheet as a resource in the repository.

Alternatively, you can first save the style sheet as a file in your filesystem and then copy that file to the /home/usr/xsd folder in the repository by using either the FTP or WebDAV interface.

Transforming an XML Document into an HTML Page

Once you have saved the style sheet in the repository, you can use it; no extra steps, such as registration, are required. In our case, you can use employee.xsl to transform an XML document generated from the contents of the empls table to a user-friendlier HTML page. To do this, you simply type the following address in your browser's address box:

http://localhost:8080/oradb/USR/EMPLS?contenttype=text/html&transform=/home/usr/xsl/employee.xsl

This URL includes a transform parameter that specifies the path of the repository resource where the style sheet is located. The DBUri Servlet uses the XMLTransform() function to apply the specified style sheet to the generated XML content.

Once you have entered this URL in your browser, you'll be asked to enter the user name and password. You are supposed to enter the username and password of the user who has access to the USR database schema. In our case, the database user USR meets this requirement.

As a result, the browser should return the following:

EMPLNO	ENAME	TITLE
117	Maya Silver	Programmer
189	John Polonski	Manager
245	Ben Jacobson	Manager

Conclusion

This article has demonstrated that the most interesting thing about Oracle XML DB is that it allows you to manage stored XML content in different ways.

First, you learned how to move XML content in and out of Oracle Database, using Oracle XML DB repository, and why you might find it handy to think of the repository as a filesystem whose metadata and data are stored in the database. Next, we took a look at the other significant feature of Oracle XML DB architecture: XMLType storage. You learned that XMLType storage lets you directly access and update XML content stored in the database by querying appropriate XMLType tables and views, using standard SQL commands. Finally, you have seen how XSL style sheets can be used to produce formatted results from XML.