8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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
- Create Folders
- Connecting To XML DB
- Register XML Schema
- Storing XML Documents
- Retrieving XML Documents
- Programmatic Resource Manipulation
- Constraining XML Documents
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:
- From the desktop, double click the "My Network Places" icon.
- Double click the "Add Network Place" icon.
- Type in the location of the folder (http://<server-name>:8080/) and click "Next".
- Enter a suitable name for the folder and click "Finish".
- Access the XML DB repository via Windows Explorer "My Network Places".
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.
- oraxdb:defaultTable="MESSAGES" - Specifies the name of the table created.
- oraxdb:SQLType="MESSAGE_TYPE" - Specifies the name of the type created.
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:
- Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2)
- Oracle9i XML API Reference - XDK and Oracle XML DB Release 2 (9.2)
- Oracle9i XML Database Developer's Guide Release 2 (9.2)
- Oracle9i XML Developer's Kits Guide - XDK Release 2 (9.2)
Hope this helps. Regards Tim...