Using Oracle XML DB Repository

Another variation on accessing and manipulating XML content stored in Oracle database is provided by Oracle XML DB repository, which is an essential component of Oracle XML DB.

NOTE: Oracle XML DB repository, also known as XML repository, is a hierarchically organized repository seamlessly integrated with Oracle Database, containing resources that can be manipulated using a file/ folder/URL metaphor.

The most significant thing about XML repository is that it makes it possible to access and manipulate XML data in a number of different ways, including SQL, PL/SQL, and standard internet protocols, such as HTTP, FTP, and WebDAV. Graphically, it looks as shown in the following figure.

You may find it convenient to think of Oracle XML DB repository as a file system whose metadata and data are stored in the database. Like a conventional file system, Oracle XML DB repository contains resources: files and folders. However, in the case of XML repository, each resource also can be accessed through SQL.

NOTE: Although XML repository is optimized for working with XML data, you can use it to store non-XML data as well. For example, you might store a collection of pictures there.

Manipulating Repository Resources with PL/SQL

Oracle XML DB provides PL/SQL package DBMS_XDB to access Oracle XML DB repository programmatically from within PL/SQL code.

For example, to create a repository folder and then a resource in that folder, you might use the DBMS_XDB.createFolder and DBMS_XDB.createResource function respectively, as follows:

 
CONN xmlusr/xmlusr;
   DECLARE
   rslt BOOLEAN;
   xmldoc VARCHAR2(250) :=
   '<EMPLOYEE
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:noNamespaceSchemaLocation="employee.xsd"
   id="303">
   <ENAME>Locke</ENAME>
   <SALARY>7000</SALARY>
   </EMPLOYEE>';
   BEGIN
   IF (NOT DBMS_XDB.existsResource('/public/xmlusr')) THEN
      rslt:=DBMS_XDB.createFolder('/public/xmlusr');
   END IF;
   IF (NOT DBMS_XDB.existsResource('/public/xmlusr/emps')) THEN
      rslt:=DBMS_XDB.createFolder('/public/xmlusr/emps');
   END IF;
   rslt := DBMS_XDB.createResource('/public/xmlusr/emps/emp303.xml',
   xmldoc);
COMMIT;
END;
/ 
   
%0D%0ACONN%20xmlusr%2Fxmlusr%3B%0D%0A%26nbsp%3B%20%26nbsp%3BDECLARE%0D%0A%26nbsp%3B%20%26nbsp%3Brslt%20BOOLEAN%3B%0D%0A%26nbsp%3B%20%26nbsp%3Bxmldoc%20VARCHAR2%28250%29%20%3A%3D%0D%0A%26nbsp%3B%20%26nbsp%3B%27%3CEMPLOYEE%0D%0A%26nbsp%3B%20%26nbsp%3Bxmlns%3Axsi%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance%22%0D%0A%26nbsp%3B%20%26nbsp%3Bxsi%3AnoNamespaceSchemaLocation%3D%22employee.xsd%22%0D%0A%26nbsp%3B%20%26nbsp%3Bid%3D%22303%22%3E%0D%0A%26nbsp%3B%20%26nbsp%3B%3CENAME%3ELocke%3C%2FENAME%3E%0D%0A%26nbsp%3B%20%26nbsp%3B%3CSALARY%3E7000%3C%2FSALARY%3E%0D%0A%26nbsp%3B%20%26nbsp%3B%3C%2FEMPLOYEE%3E%27%3B%0D%0A%26nbsp%3B%20%26nbsp%3BBEGIN%0D%0A%26nbsp%3B%20%26nbsp%3BIF%20%28NOT%20DBMS_XDB.existsResource%28%27%2Fpublic%2Fxmlusr%27%29%29%20THEN%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3Brslt%3A%3DDBMS_XDB.createFolder%28%27%2Fpublic%2Fxmlusr%27%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3BEND%20IF%3B%0D%0A%26nbsp%3B%20%26nbsp%3BIF%20%28NOT%20DBMS_XDB.existsResource%28%27%2Fpublic%2Fxmlusr%2Femps%27%29%29%20THEN%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3Brslt%3A%3DDBMS_XDB.createFolder%28%27%2Fpublic%2Fxmlusr%2Femps%27%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3BEND%20IF%3B%0D%0A%26nbsp%3B%20%26nbsp%3Brslt%20%3A%3D%20DBMS_XDB.createResource%28%27%2Fpublic%2Fxmlusr%2Femps%2Femp303.xml%27%2C%0D%0A%26nbsp%3B%20%26nbsp%3Bxmldoc%29%3B%0D%0ACOMMIT%3B%0D%0AEND%3B%0D%0A%2F

As you can see, when creating a resource, regardless of whether it is a file or folder, you must specify an absolute path to that resource. This is required because, as in a conventional file system, each resource in the XML repository is identified by a path and name.

Using Oracle XML DB Repository

Manipulating Repository Resources with PL/SQL

Tags