XML-Enabled Applications - Part 4

[next]

XML-Enabled Applications - Part 4

By Yuli Vasiliev

Digg This Add to del.icio.us

[This is an excerpt from the book, PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax, by Yuli Vasiliev. Published by Packt Publishing Ltd., 2007

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.

[ 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 fi le system, Oracle XML DB repository contains resources: fi les and folders. However, in the case of XML repository, each resource also can be accessed through SQL.

[ 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="https://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; /

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.

Accessing Repository Resources with SQL

In fact, Oracle XML DB repository resources are stored in a set of database tables and indexes, which can be accessed via SQL. You are not supposed to access those tables directly. Instead, Oracle XML DB provides two public views RESOURCE_VIEW and PATH_VIEW through which you can access repository resources.

For example, you might issue the following query against the RESOURCE_VIEW view to access the employee XML document stored in the XML repository as /public/xmlusr/emps/emp303.xml, assuming that you have executed the PL/SQL block shown in the preceding section.

SELECT extract(r.RES, '/Resource/Contents/*').getStringVal() RESULT FROM RESOURCE_VIEW r WHERE equals_path(res, '/public/xmlusr/emps/emp303.xml') = 1;

This should produce the following result:

<EMPLOYEE xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="employee.xsd" id="303"> <ENAME>Locke</ENAME> <SALARY>7000</SALARY> </EMPLOYEE>

However, in this particular example you don't have to query RESOURCE_VIEW to retrieve the above XML document through SQL. Instead, you might issue the following query against the employeesXMLType table:

SELECT extract(OBJECT_VALUE,'/').getStringVal() RESULT FROM employees WHERE existsNode(OBJECT_VALUE, '/EMPLOYEE/@id="303"') = 1;

You might be asking yourself: How could that have happened—a document uploaded into the XML repository appeared in an XMLType table? As you might recall from the listing describing the employee.xsd XML schema registration in the Using XML Schemas section, the employees XMLType table is specified as a default table in the employee.xsd XML schema and so it must have been generated during the schema registration process. Since the employee XML document inserted into the XML repository by the PL/SQL code as discussed in the preceding section is based on the employee.xsd XML schema, this document has been automatically inserted into the employees XMLType table.

[next]

URL: