As Published In
Oracle Magazine
May/June 2005


Getting to XML

By Sean Dillon

Oracle XML DB Repository provides new ways into XML.

Oracle users have grown accustomed to using the SQL interface to interact with a database. To put data in, you use the INSERT statement. To change existing data, you use an UPDATE , and to remove data, you use a DELETE . Security is accomplished with GRANT and REVOKE statements.

In the early days of XML data storage, however, the SQL language was as foreign to XML users as XML was to Oracle users. In those early days, I remember explaining to the manager of an XML project how—after creating a data model, defining the project's declarative data integrity, building some custom SQL scripts to load the data, writing DTD or XML Schema validation code, and using a parser to access the XML-based data—they'd be ready to build a SQL-based application. Needless to say, this wasn't what that particular manager (and many other XML-related project personnel out there, for that matter) was looking for.

Even though these programmers were unsure about a SQL interface to their XML data, they recognized the need for features such as fault tolerance, high availability, scalability, security, manageability, and the continued data management offered by Oracle Database. Oracle9i Database Release 2 introduced Oracle XML DB Repository, which delivered improved XML storage and added support for interfaces familiar to XML users, such as FTP, WebDAV, and HTTP.

In this column, I'll explain what Oracle XML DB Repository is, outline the XML Services offered through it, and describe the various protocols and methods available to connect to and access the data stored there.

A File-System Metaphor

Oracle XML DB Repository provides an interface that is standards-compliant and also supports the non-SQL-based application developers and data managers of the world. Essentially, Oracle XML DB Repository is a file-system-like interface that provides a foldering/container mechanism so that "resources" can be added to the repository in a hierarchical fashion.

Each object you create in Oracle XML DB Repository is a resource. It's worth noting that XML documents are not the only type of files that can be added to the repository. Unstructured XML documents, structured XML documents that map to a registered XML Schema, HTML documents, word processing documents, binary files (such as images), and more can all be added to Oracle XML DB Repository. The repository understands how to store the various documents you will add, based on the type of file you are adding.

Even though the repository looks like a file system, the underlying implementation is still tables, rows, and columns. When an XML document is loaded into Oracle XML DB Repository, a row is created in the underlying table belonging to a schema called XDB. The XDB account is locked by default and is not meant to be used by a connected user.

Access Methods

The Oracle9i Release 2 Net Services stack added new protocol support to accommodate non-SQL-based users. The Oracle Listener was augmented to listen for FTP and HTTP connections to the database. Provided that the listener is running and the database is registered with the listener, connection requests on these protocols are routed to the Oracle XML DB Repository on successful authentication.

You can see whether your database services are registered with the listener by using the LSNRCTL utility. Use the STATUS command to see the status of the listener and services registered. Listing 1 contains a condensed version of the command results; see listener_status.txt , for the full version.

Code Listing 1: Condensed LSNRCTL STATUS report 


Alias                  LISTENER
Version                TNSLSNR for 32-bit Windows: Version - Production
Start Date             31-JAN-2005 13:33:34
Uptime                 0 days 2 hr. 53 min. 53 sec
Trace Level            off
Security               ON: Local OS Authentication
SNMP                   OFF

Listening Endpoints Summary...

The result of the LSNRCTL STATUS command shows that this listener is ready for HTTP connections on port 8080 and FTP connections on port 2100.

Beyond access via HTTP and FTP, you can access Oracle XML DB Repository as a Web-based Distributed Authoring and Versioning (WebDAV) repository. WebDAV is an extension to the HTTP protocol. The purpose of WebDAV repositories is to allow groups of users to collaborate and share documents through a standards-based repository. You can use any WebDAV-compliant authoring tool—including Microsoft Word and Altova's XMLSpy—to browse the Oracle XML DB Repository.

Using Microsoft Internet Explorer on the Windows platform, you can open a "Web Folder" through the WebDAV implementation that lets you browse Oracle XML DB Repository as if it were a folder on your computer. Use the following steps to open a Web folder to your Oracle XML DB Repository: 

  1. Open Microsoft Internet Explorer.

  2. Click on File -> Open.

  3. Enter the URL of your database listener and HTTP port (for example,

  4. Click on the Open as a Web Folder checkbox.

  5. Click on the OK button.

  6. Enter your username and password (I used SCOTT/TIGER ).

  7. Click on the OK button.

This gives you a WebDAV-based interface to your Oracle XML DB Repository, as shown in Figure 1.


figure 1
Figure 1: Oracle XML DB Repository accessed as a Web folder

Repository Security

Connecting to Oracle XML DB Repository is the same as connecting to the database. You must provide a valid username and password in order to connect to the repository via FTP, HTTP, or WebDAV.

After you connect to Oracle XML DB Repository, access control lists (ACLs) manage access to data stored in the repository. By default, all resources added to the repository are assigned the "bootstrap ACL" (located at /sys/acls/bootstrap_acl.xml), which means that all users can see the resource but only the owner of the resource and the XDB ADMIN user have UPDATE and DELETE privileges. Alternate or additional ACLs can be assigned to resources, depending on the security required for that particular resource.


You can create version-controlled resources in Oracle XML DB Repository. Every time a version-controlled resource is updated, Oracle XML DB stores the preupdate contents as a separate version of that resource. This is especially useful in collaborative environments such as those using WebDAV-enabled tools. Versioning is not limited to WebDAV-created or -updated resources; this is just one example of where it can be used.

Versioning is managed through the PL/SQL package DBMS_XDB_VERSION . By default, resources are added to the repository as nonversioned resources, meaning that updates to those resources overwrite the original and older versions are lost. In order to create a version-controlled resource, you can call the PL/SQL function DBMS_XDB_VERSION.MakeVersioned() on an existing resource, which tells Oracle XML DB that future updates to that particular resource should be stored as new versions. Oracle XML DB's versioning supports check-in, uncheck-in, and checkout operations, once again through the DBMS_XDB_VERSION package.

At this time, versioning is not available to folders (directories or collections), ACLs, or structured storage documents where the underlying tables have associated triggers or constraints.

Access Models

As discussed above, you can access Oracle XML DB Repository resources through FTP, HTTP, and WebDAV. Access to resources stored in the Repository is also available through SQL and PL/SQL.

SQL views provide comprehensive access to not only resource contents but also the metadata of the resource. Oracle XML DB's RESOURCE_VIEW and PATH_VIEW provide access to data in the Repository from two different perspectives.

RESOURCE_VIEW is a SQL view that gives a user access to Oracle XML DB Repository resources and associated Repository data. There is one row in RESOURCE_VIEW for every unique resource in the Repository. When you query a resource using RESOURCE_VIEW , the resulting data will include the following: 

  • RES —the resource, including the name of the resource, the contents, the ACL(s), and any associated properties

  • ANY_PATH —the absolute path

  • RESID —the resource ID

PATH_VIEW is another SQL view that gives you access to Oracle XML DB Repository resources and associated Repository data, but this view shows all paths that lead to resources stored in the Repository. Since there can be multiple paths to the same resource, there is one row in PATH_VIEW for every unique path to all resources stored in the Repository.


Oracle XML DB Repository provides different ways to access XML data. The file system metaphor for storing and retrieving files, combined with new access mechanisms such as FTP, HTTP, and WebDAV, gives XML users a familiar data management environment. RESOURCE_VIEW and PATH_VIEW provide access to the Oracle XML DB Repository for SQL and PL/SQL users.

Sean Dillon ( is a principal technologist at Oracle, where he has worked since 1996. The lead author of Beginning Oracle Programming (APress), Dillon specializes in core database, XML, and Web services technologies.

Send us your comments