|Oracle9i XML Database Developer's Guide - Oracle XML DB
Release 2 (9.2)
Part Number A96620-01
This chapter describes the SQL-based mechanisms,
PATH_VIEW, used to access Oracle XML DB Repository data. It discusses the SQL operators
EQUALS_PATH used to query resources based on their path names and the SQL operators
DEPTH that return the resource path names and depth.
It contains the following sections:
Figure 15-1 shows how Oracle XML DB
PATH_VIEW provide a mechanism for using SQL to access data stored in Oracle XML DB Repository. Data stored in Oracle XML DB Repository through protocols like FTP, WebDAV, or programming API such as JNDI, can be accessed in SQL using the resource and
PATH_VIEWs, and vice versa.
PATH_VIEW together, along with PL/SQL package,
DBMS_XDB, provide all query-based access to Oracle XML DB and DML functionality that is available through the programming API.
RESOURCE_VIEW contains one row for each resource in the Repository. The following describes its structure:
Column Datatype Description ------ -------- ------------------------------------------------------- RES XMLTYPE A resource in Oracle XML Repository ANY_PATH VARCHAR2 A path that can be used to access the resource in the Repository
PATH_VIEW contains one row for each unique path to access a resource in the Repository. The following describes its structure:
Column Datatype Description ------ -------- ----------------------------- PATH VARCHAR2 Path name of a resource RES XMLTYPE The resource referred by PATH LINK XMLTYPE Link property
Figure 15-2 illustrates the structure of Resource and
The path in the
RESOURCE_VIEW is an arbitrary one of the accessible paths that can be used to access that resource. Oracle XML DB provides operator
UNDER_PATH that enables applications to search for resources contained (recursively) within a particular folder, get the resource depth, and so on. Each row in the views is of
XMLType. DML on the Oracle XML DB Repository views can be used to insert, rename, delete, and update resource properties and contents. Programmatic APIs must be used for some operations, such as creating links to existing resources.
The major difference between the
PATH_VIEWdisplays all the path names to a particular resource whereas
RESOURCE_VIEWdisplays one of the possible path names to the resource
PATH_VIEWalso displays the properties of the link
Figure 15-3 illustrates the difference between Resource and
Since many internet applications only need one URL to access a resource,
RESOURCE_VIEW is widely applicable.
PATH_VIEW contains the link properties as well as resource properties, whereas the
RESOURCE_VIEW only contains resource properties.
RESOURCE_VIEW benefit is generally optimization, if the database knows that only one path is needed, the index does not have to do as much work to determine all the possible paths.
When using the
You can perform the following operations using
UNDER_PATHoperator or other SQL operators:
See the "Using the Resource View and Path View API" and
This section describes the
UNDER_PATH operator uses the Oracle XML DB Repository hierarchical index to return the paths under a particular path. The hierarchical index is designed to speed access walking down a path name (the normal usage).
If the other parts of the query predicate are very selective, however, a functional implementation of
UNDER_PATH can be chosen that walks back up the Repository. This can be more efficient, since a much smaller number of links may need to be traversed. Figure 15-4 shows the
Table 15-1 describes the
Note that only one of the accessible paths to the resource needs to be under the path argument for a resource to be returned.
EQUALS_PATH operator is used to find the resource with the specified path name. It is functionally equivalent to
UNDER_PATH with a depth restriction of zero. The
EQUALS_PATH syntax is describe here and in Figure 15-5.
pathnameis the path name to resolve.
PATH is an ancillary operator that returns the relative path name of the resource under the specified
pathname argument. Note that the path column in the
RESOURCE_VIEW always contains the absolute path of the resource. The
PATH syntax is:
correlationis an integer that can be used to correlate the
UNDER_PATHoperator (a primary operator) with ancillary operators (
DEPTH is an ancillary operator that returns the folder depth of the resource under the specified starting path.
correlation is an integer that can be used to correlate the
UNDER_PATH operator (a primary operator) with ancillary operators (
PATH and D
PATH_VIEW examples use operators
The following examples illustrate how you can access paths, resources, and link properties in the Repository:
select any_path, extract(res, '/display_name') from resource_view where under_path(res, '/sys') = 1;
select path, extract(link, '/LINK/Name/text()').getstringval(), extract(link, '/LINK/ParentName/text()').getstringval(), extract(link, '/LINK/ChildName/text()').getstringval(), extract(res, '/Resource/DisplayName/text()').getstringval() from path_view where path LIKE '/sys%';
The following example illustrates how you can insert data into a resource:
insert into resource_view values(sys.xmltype.createxml(' <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd http://xmlns.oracle.com/xdb/XDBResource.xsd"> <Author>John Doe</Author> <DisplayName>example</DisplayName> <Comment>This resource was contrived for resource view demo</Comment> <Language>en</Language> <CharacterSet>ASCII</CharacterSet> <ContentType>text/plain</ContentType> </Resource>'), '/home/SCOTT');
The following examples illustrate how you can delete resources or paths:
If only leaf resources are deleted, you can perform a delete using
delete from resource_view where....
If only leaf resources are deleted, you can delete them using "
delete from resource_view where...". For example, one way to delete leaf node
'/public/test/doc.xml'is as follows: delete from resource_view where under_path(res, '/public/test/doc.xml') = 1;
However, if you attempt to delete a non-empty container recursively, the following rules apply:
Therefore you should guarantee that a container is deleted only after its children have been deleted.
For example, to recursively delete paths under '/public'), you may want to try the following:
delete from (select 1 from resource_view where UNDER_PATH(res, '/public', 1) = 1 order by depth(1) desc);
The following examples illustrate how to update resources and paths:
update resource_view set res = sys.xmltype.createxml(' <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd http://xmlns.oracle.com/xdb/XDBResource.xsd"> <Author>John Doe</Author> <DisplayName>example</DisplayName> <Comment>Has this got updated or not ? </Comment> <Language>en</Language> <CharacterSet>ASCII</CharacterSet> <ContentType>text/plain</ContentType> </Resource>') where any_path = '/home/SCOTT/example';
If you need to get all the resources under a directory, you can use the LIKE operator, as shown in Example 15-1.
If you need to get the resources up to a certain number of levels, or get the relative path, then use the
Chapter 13, "Oracle XML DB Foldering",Table 13-3, "Accessing Oracle XML DB Repository: API Options" for additional examples that use the
Operations listed in Table 13-3, Chapter 13, "Oracle XML DB Foldering", typically apply to only one resource at a time. To perform the same operation on multiple Oracle XML DB resources, or to find one or more Oracle XML DB resources that meet a certain set of criteria, use
PATH_VIEW in SQL.
For example, you can perform the following operations with these resource_view and
PATH_VIEW SQL clauses: