Managing XML using Oracle's XMLDB

This article introduces Oracle XML DB features to the DBAs and Developers who are not actively working with XML. It offers a quick start to those who finds quite a lot of Oracle XML literature around, and who is not sure where to begin.

Setting Up

The examples in this article use the General Purpose Database (with Sample Schemas) created by the Database Configuration Assistant (DBCA) Wizard in Oracle 10g. It has XML DB support pre-installed. The XDB and SCOTT accounts, which are locked by default, needs to be unlocked and the password reset.

The database service and the listener service should be running in order to test the following examples. The listener service should be started without a listener.ora file. You may use the lsnrctl start command to start the listener.

Why put your XML in a database?

The XML format is commonly used to exchange and store documents. As far as data storage is concerned, relational storage offers better flexibility, performance and scalability than XML storage. Joins, Sort, Group By, Order By, etc, are appreciably mature features with the relational engine, and are scalable to terabytes of data. XML storage in a database is only relevant untill the DOCUMENTS become DATA. XML storage may also be relevant when we need to generate a couple of DOCUMENTS that are waiting to be dispatched from the native database application to another heterogeneous application.

Where can core XML be learned from?

Learning XML means learning how to use the tags. This article does not cover tags. A good place to begin is w3schools.com. The rest of this document will be dedicated to the following features of Oracle XML DB:

• Accept XML and store it temporarily as XML documents
• Process XML and extract relational data and store it in a relational schema
• Process XML and store it in XML Type columns
• Create XML views from relational schemas
• Access the XML docs (created from the XML views) from web

XML Documents and XMLType Columns

XML Documents are XML Files contained in a virtual directory structure in the database. They can be viewed by the default browsing interface (http://database-server:8080). If the sample schemas are installed, many XML Documents can be found in the /home/OE folder.

XML Type Columns are quite different from XML Documents. They are a column datatype - that is, they signify columns which contain XML Documents. However, the organization of those documents is relational -- that is, they are part of a table rather than of a directory. The script given below shows how to create the table PROJECTS, which we will use for our examples.

/**** User XDB ****/
Create table project1 
(PROJECT_ID number, 
 Employees XMLType);

Input XML Document (FTP or WebDAV access)

The following steps show how to create a Web Folder with the WebDAV interface in Windows. This is the most essential element to get started quickly in Oracle XMLDB.

• Open Windows Explorer, right Click on My Network Places
• Select Map Network Drive
• Click the link Signup for online storage or connect to a network server
• Add Network Place Wizard opens. Click next and wait for the searching to stop
• Select Choose another network location, and click Next
• In the box titled Internet or network address type http://database-server:8080/. Click Next
• When prompted for userid and password use xdb and the xdb password
• Select a suitable name for the web folder and click Finish

The created web folder gives the root level access to the virtual XMLDB directory tree. The folders that at level 1 are home, public and sys. For our examples we need to create a folder named demo under home, which can be created by navigating to /home and right clicking.

Save the file given in Appendix (A) as demo1.xml in the local directory and copy it to the web directory under /home/demo.

The view RESOURCE_VIEW gives the SQL interface to the XML directory interface.

Input XML as Document and XMLTYPE (PLSQL)

In addition to the WebDAV interface, an XML document can be entered into the database if it is present in any directory in the database host. We see how the XML present in demo2.xml file has been loaded into table PROJECT1.

/****** Needs to be executed as SYSTEM ******/
CREATE DIRECTORY xmldir AS 'The path to the folder containing the XML File';
grant all on directory xmldir to xdb;

/************ User XDB ********/
INSERT INTO project1 VALUES
(10, xmltype(bfilename('XMLDIR', 'demo2.xml'),nls_charset_id('AL32UTF8')));

The PLSQL Interface can also load the document into the XML directory tree from the OS directories, as shown in the example below.

/************ Run as XDB ************/
declare
res boolean;
begin
res := dbms_xdb.createResource('/home/demo/demo2.xml',
bfilename('XMLDIR','demo2.xml'),
nls_charset_id('AL32UTF8'));
end;
/

To view any loaded XML resource in the XML Directory tree the following query can be used.

/***************************/
set long 2000
select sys.UriFactory.getUri('/home/demo/demo2.xml').getclob() from dual;

Input XML as XMLTYPE (SQLLoader)

SQL Loader is the fastest load path for XML, if the direct path load method of XML is used. It can load several files at once. The loading works like the usual BLOB loading, except that the keyword XMLTYPE(columnname) is used to guide the loader. Also, the loader can load only XMLType tables, not columns.

/************ create table project2 *************/
Create table project2 of XMLType;

/************** Save as file load.ctl ***************/
LOAD DATA
INFILE *
INTO TABLE PROJECT2 TRUNCATE
xmltype(xmldata)
FIELDS
(
ext_fname filler char(20),
xmldata LOBFILE (ext_fname) TERMINATED BY EOF
)
BEGINDATA
demo1.xml
demo2.xml

/************* In command prompt *************/
sqlldr xdb/xdb load.ctl

Data Extraction from XMLDB by SQL

They can be of the following types,

• Extracting Relational Data from XML Documents by SQL
• Extracting Relational Data from XMLType columns by SQL

1. Extracting XML data from XML Documents or XML Type Columns (XPath Query)

This can be done with the getUri or get xmldburitype routines as shown below.

/************************************************************/
set long 2000
select extract(xmltype(xdburitype('/home/demo/demo1.xml').getclob()), '/Project/Employee[@EmplID>1001]') from dual;

/********** returns all employees except 1001 *************/

The portion '/Project/Employee[@EmplID>1001]' is the XPath search string. It has its own standard in the world of XML.

2, Extracting Relational data from XML Documents or XML Type Columns (XPath Query)

XPath Queries extract XML data and return XML data. To convert them to relational data we can use the object relational features. The following query shows the result from table PROJECT2 in a relational form. The next two queries shows how to extract data from XMLType tables or columns,

/**************** qry1 ***********/
SQL> column projid format a10
SQL> column Sal format a40
SQL> SELECT extract(p.object_value,'/Project/@ProjID') PROJID,
  2         extract(p.object_value,'/Project/Employee/Sal') Sal
  3  FROM PROJECT2 p
  4  WHERE existsNode(p.object_value,'/Project/Employee[Department=10]') = 1;

PROJID     SAL
---------- ----------------------------------------
P001       <Sal>1000</Sal>
           <Sal>2000</Sal>
           <Sal>1500</Sal>
           <Sal>1200</Sal>
           <Sal>1300</Sal>

P002       <Sal>1000</Sal>
           <Sal>2000</Sal>

This shows how multiple data from the same document is clubbed in the same column; seven SAL values are made to fit only two columns. This can be split further by object relational features.

/****************************/
SQL> select value(d)
  2  FROM PROJECT2 p,
  3  table(xmlsequence(extract(p.object_value,'/Project/Employee/Sal'))) d
  4  WHERE existsNode(p.object_value,'/Project/Employee[Department=10]') = 1;

VALUE(D)
--------------------------------------------------------------------------------

<Sal>1000</Sal>
<Sal>2000</Sal>
<Sal>1500</Sal>
<Sal>1200</Sal>
<Sal>1300</Sal>
<Sal>1000</Sal>
<Sal>2000</Sal>

7 rows selected.

Extracting XML

XML data can be extracted from an Oracle database by means of the following methods:

• Extracting XML from Relational Data and saving as document
• Extracting XML from URL

1. Extracting XML Document from Relational Data or XMLType Columns (DBMS_XDB)

The DBMS_XDB package can be used to save data from XMLType columns or even from simple tables as XML Documents in the XML Virtual Directories. The XML can be composed as simple string and written to the file. A sample is given below.

/*****************************************/
conn system
grant select on scott.emp to xdb;
conn xdb/xdb

/*****************************************/
declare
bool1 boolean;
cstr clob;
begin

SELECT xmlelement("Project",xmlagg(XMLELEMENT("Emp", XMLELEMENT("name", e.ename),XMLELEMENT ( "hiredate", e.hiredate)))).getclobval()
into cstr 
FROM scott.emp e 
WHERE deptno = 10;

bool1 := dbms_xdb.createresource('/home/demo/demo3.xml',cstr);
end;
/

/***********************************/
select xdburitype('/home/demo/demo3.xml').getclob() from dual;

XDBURITYPE('/HOME/DEMO/DEMO3.XML').GETCLOB()
----------------------------------------------

<Project>
  <Emp>
    <name>CLARK</name>
    <hiredate>09-JUN-81</hiredate>
  </Emp>
  <Emp>
    <name>KING</name>
    <hiredate>17-NOV-81</hiredate>
  </Emp>
  <Emp>
    <name>MILLER</name>
    <hiredate>23-JAN-82</hiredate>
  </Emp>
</Project>

To generate XML Data from relational data the SQL/XML Funcions (xmlelement(), xmlattribute(), xmlagg, etc) can be used, or it can be constructed programmatically.

2. Accessing Relational Data as XML through URLs

Relational Data can be viewed from the database tables by using the URL http://localhost:8080/oradb/schema/table -- for example: http://localhost:8080/oradb/SCOTT/EMP

Queries are also possible. The syntax is XPath http://localhost:8080/oradb/SCOTT/EMP/ROW[EMPNO=7521]

The above examples assume that localhost is the database server.

Conclusion:

There are more features in XML DB, such as XML Schema validation, update/delete of XML, and others. This article is a quick-start on XML DB for traditional Oracle developers. The most important point to note in Oracle XDB DB as it stands is that, simply by using SQL or PLSQL it is possible to achieve all objectives without using any external programmatic interface in C or Java.

Appendix A:

File "demo1.xml"

<Project ProjID="P001">
  <Employee EmplID="1001">
    <Department>10</Department>
    <Sal>1000</Sal>
  </Employee>
  <Employee EmplID="1002">
    <Department>20</Department>
    <Sal>2000</Sal>
  </Employee>
  <Employee EmplID="1003">
    <Department>30</Department>
    <Sal>1500</Sal>
  </Employee>
  <Employee EmplID="1004">
    <Department>40</Department>
    <Sal>1200</Sal>
  </Employee>
  <Employee EmplID="1005">
    <Department>10</Department>
    <Sal>1300</Sal>
  </Employee>
</Project>

File "demo2.xml"

<Project ProjID="P002">
  <Employee EmplID="1001">
    <Department>10</Department>
    <Sal>1000</Sal>
  </Employee>
  <Employee EmplID="1002">
    <Department>20</Department>
    <Sal>2000</Sal>
  </Employee>
</Project>