Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

XML DB

XML DB is an out-of-the-box solution for storing and retrieving XML documents within Oracle. In addition, the registration of XML schemas allows XML DB to shred the XML documents and store them as object-relational structures to improve query performance within the database. No additional software is needed for this process as all the functionality is built into the Oracle9i Release 2 kernel. In this article I'll explain how to store and retrieve XML documents using XML DB where the documents are stored in XMLTYPEs and shredded into object-relational structures.

Create User

First we create a user to work with.

CREATE USER dev IDENTIFIED BY dev 
  TEMPORARY TABLESPACE temp 
  DEFAULT TABLESPACE users;

GRANT connect, resource, dba, xdbadmin TO dev;

CONN dev/dev@w2k1

Create Folders

Next we create some folders within the XML DB repository to work with.

DECLARE 
  v_return  BOOLEAN; 
BEGIN 
  v_return := dbms_xdb.createFolder('/home/');
  v_return := dbms_xdb.createFolder('/home/DEV/');
  v_return := dbms_xdb.createFolder('/home/DEV/xsd/');
  v_return := dbms_xdb.createFolder('/home/DEV/messages/');
  v_return := dbms_xdb.createFolder('/home/DEV/employees/');
  COMMIT;
END; 
/

Connecting To XML DB

Connections to the XML DB repository are made via HTTP, FTP or WebDAV. The port settings are defined in the /xdbconfig.xml file within XML DB itself.

Connecting via HTTP and FTP is achieved using the following URLs.

http://<server-name>:8080/
ftp://<server-name>:2100/

Connecting via WebDAV under Windows 2000 is achieved as follows:

Valid database username/password combinations are required for authentication unless anonymous access is required.

Register XML Schema

The basic_message.xsd XML schema defines the structure of the XML message documents we are trying to store. For this example we will use the message.xsd XML schema which contains some additional annotations that affect the way XML DB functions.

Save the message.xsd XML schema on your filesystem and copy it to the "/home/DEV/xsd/" folder in the XML DB repository (See Connecting To XML DB).

At this point the XML schema has stored within the XML DB repository. To make XML DB aware of the XML schema it must be registered.

-- Register XML schema
BEGIN 
  DBMS_XMLSchema.registerSchema(
    schemaurl=>'http://marge:8080/home/DEV/xsd/message.xsd', 
    schemadoc=>sys.UriFactory.getUri('/home/DEV/xsd/message.xsd')); 
END; 
/

-- Delete XML schema
BEGIN 
  DBMS_XMLSchema.deleteSchema(
    schemaurl=>'http://marge:8080/home/DEV/xsd/message.xsd',
    delete_option=>DBMS_XMLSchema.Delete_Cascade_Force); 
END; 
/

Annotations within the XML schema allow the DBA to influence the way XML DB creates objects.

The process of XML schema registration involves the creation of objects to hold shredded XML documents.

SQL> desc messages
 Name                                                                                                   
 ---------------------------------------------------------------------------------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://marge:8080/home/DEV/xsd/message.xsd" Element "message") STORAGE 

SQL> desc message_type
 message_type is NOT FINAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 to                                                 VARCHAR2(4000)
 from                                               VARCHAR2(4000)
 subject                                            VARCHAR2(4000)
 body                                               VARCHAR2(4000)

SQL>

Storing XML Documents

With the XML schema registered we can store XML documents in the XML DB repository, via HTTP, FTP or WebDAV, and they will be automatically shredded into their constituent parts.

Save the message1.xml and employee1.xml XML documents onto your filesystem and copy them to the "/home/DEV/messages/" and "/home/DEV/employees/" folders in the XML DB repository. Remember, the "xsi:noNamespaceSchemaLocation" of the XML documents must be amended to reference your XML schema.

At this point you will see that both documents have been stored in the XML DB repository but only message1.xml has been shredded as employee1.xml was not associated with a registered XML schema.

SQL> SELECT COUNT(*) FROM messages;

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

SQL>

The association with the XML schema allows Oracle to validate the contents of the document. If you attempt to store message2.xml XML document in the XML DB repository it will fail since the file definition does not match the XML schema definition.

Retrieving XML Documents

Whether the documents are shredded or not they are still accessible in their original form where they can be updated and deleted.

http://<server-name>:8080/home/DEV/messages/message1.xml
http://<server-name>:8080/home/DEV/employees/employee1.xml
ftp://<server-name>:2100/home/DEV/messages/message1.xml
ftp://<server-name>:2100/home/DEV/employees/employee1.xml

In addition the documents can be accessed via SQL. All resources within the XML DB repository are visible in the RESOURCE_VIEW and PATH_VIEW views.

SET LONG 2000
SELECT rv.res.getClobVal()
FROM   resource_view rv
WHERE  rv.any_path = '/home/DEV/messages/message1.xml';

SELECT rv.res.getClobVal()
FROM   resource_view rv
WHERE  rv.any_path = '/home/DEV/employees/employee1.xml';

The UNDER_PATH() function can be used to limit the selection to those resources below a specified path.

SELECT COUNT(*)
FROM   resource_view rv
WHERE  under_path(rv.res, '/home/DEV/employees/') = 1;

The following examples show how the data can be retrieved from the RESOURCE_VIEW view.

SELECT extract(res, '/Resource/Contents/*').getClobVal() xml_doc
FROM   resource_view
WHERE  any_path = '/home/DEV/messages/message1.xml';

SELECT extract(res, '/Resource/Contents/*').getClobVal() xml_doc
FROM   resource_view
WHERE  any_path = '/home/DEV/employees/employee1.xml';

Shredded documents can be queried directly via the object-relational tables which gives better performance. Oracle performs query-rewrite optimizations when shredded documents are accessed like non-shredded documents. These examples show the usage of the extractValue() and existsNode() functions.

SELECT COUNT(*)
FROM   messages;

SELECT value(x).getClobVal() xml_doc
FROM   messages x
WHERE  existsNode(value(x),'/message[to="info@oracle-base.com"]') = 1;

SELECT extractValue(value(x), '/message/body') body
FROM   messages x
WHERE  existsNode(value(x),'/message[to="info@oracle-base.com"]') = 1;

The XDBURIType can be used to access a resource in the repository associated with a logical path.

SELECT extractValue(XDBURIType('/home/DEV/messages/message1.xml').getXML(),
                    '/message/body/text()') body
FROM   dual;

SELECT extractValue(XDBURIType('/home/DEV/employees/employee1.xml').getXML(),
                    '/employee/ename/text()') ename
FROM   dual;

Programmatic Resource Manipulation

Resources can be created, updated and deleted programmatically via SQL and PL/SQL:

-- Create resource
DECLARE
  v_xml     XMLTYPE;
  v_result  BOOLEAN;
BEGIN
  v_xml := XMLTYPE.createXML('<?xml version="1.0"?>
            <message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:noNamespaceSchemaLocation="http://marge:8080/home/DEV/xsd/message.xsd">
              <to>info@oracle-base.com</to>
              <from>somebody@yahoo.com</from>
              <subject>Question 3</subject>
              <body>Does this demo work again?</body>
            </message>');

  -- Using DBMS_XDB
  v_result := DBMS_XDB.createResource(
                '/home/DEV/messages/message3.xml',
                v_xml);
  
  COMMIT;
END;
/

-- Update resource
UPDATE resource_view
SET    res = updateXML(res, '/Resource/DisplayName/text()','message3_upd.xml')
WHERE any_path = '/home/DEV/messages/message3.xml';
COMMIT;

-- Delete resource
DECLARE
  v_result  BOOLEAN;
BEGIN
  -- Using DBMS_XDB
  DBMS_XDB.deleteResource('/home/DEV/messages/message1.xml');

  -- Using RESOURCE_VIEW
  DELETE FROM resource_view
  WHERE  any_path = '/home/DEV/messages/message3.xml';

  COMMIT;         
END;
/

Resources can be created by directly inserting into the RESOURCE_VIEW but the correct WebDAV implementation must be used along with your document contents, making it easier to use the DBMS_XDB.createResource function.

Constraining XML Documents

The contents of XML documents can be constrained using SQL commands such as.

-- Enforce unique constraint.
ALTER TABLE my_table 
ADD CONSTRAINT reference_is_unique 
UNIQUE (xmldata."reference")
/

-- Enforce referential integrity.
ALTER TABLE my_table 
ADD CONSTRAINT user_is_valid 
FOREIGN KEY (xmldata."user") REFERENCES scott.emp(ename)
/

-- Only store well formed XML documents.
CREATE TRIGGER validate_message
BEFORE INSERT ON message
FOR EACH ROW
DECLARE
  v_xmldata  XMLTYPE;
BEGIN
  v_xmldata := :new.sys_nc_rowinfo$;
  XMLTYPE.schemaValidate(v_xmldata);
END;
/

The first two constraints are not relevant to this example since it is a stand alone table with no unique constraint. The trigger can be used if you wish.

For further more see:

Hope this helps. Regards Tim...

Back to the Top.