Oracle 9i XML

Peter Goldthorp August 2002

XML is a markup language for structured data. If you are new to XML the following url provides a high level overview: Oracle 9i provides extensive support for XML development and introduces three new features:

  1. XMLType Columns and Tables
  2. URIType Columns
  3. XBD Repository
XMLType Columns and Tables

An XMLType is a datatype that 'understands' XML. It can be used to create a column in a table or as a table in its own right. XMLType resources can be interrogated via XPath. This allows individual elements or XML fragments to be read from an XML document and used in a PL/SQL (or other language) program. xml_datatype.html shows an example of an XMLType column being queried via XPath.

XMLType resources can be transformed using XSL Stylesheets. This allows an XML document to be the source for other documents. For example, The dbms_metadata package shipped with Oracle 9i includes XSL stylsheets to convert an XML document into DDL (data definition language) statements that can be run to create objects in a database. The same XML could be converted into an HTML document by using a separate stylesheet.

XSL Transformations can be performed inside the database. Oracle 9i includes an XSL processor.

URIType Columns

URIType columns hold pointers to external (http) or internal (oracle database) resources. They can be used to select a resource or its contents. uri_datatype.html shows URIType examples.

XBD Repository

The XDB Repository is an XML 'file system' inside the database. It allows folder structures to be build to hold XML files or other database content. The metadata for these files is queryable. It allows complex queries to be performed against it. For example, the following query will get the path for all resources in the /public folder and its sub folders upto 3 levels (of sub-folder) deep:

select path(1)
from resource_view
where under_path(res, 3, '/public', 1)=1;

XML content in an XDB Repository is accessible from inside the database using PL/SQL or Java and outside the database via HTTP, FTP or WebDAV. WebDAV allows a user to drag and drop files into the database for storage or processing. Microsoft Windows Explorer includes a WebDAV client. The Cadaver client provides command line WebDAV functionality in Unix environments. It can be downloaded from

The XDB Repository provides a powerful platform for XML processing. Resources can be created, updated and deleted from a database session or an external process. For example, XML documents can be created from a PL/SQL stored procedure for access by an external process. This is a common requirement when two distinct applications are being integrated.