|Oracle9i XML Database Developer's Guide - Oracle XML DB
Release 2 (9.2)
Part Number A96620-02
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.
This chapter 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, can be accessed in SQL using RESOURCE_VIEWs 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.
The base table for
XDB.XDB$RESOURCE and should only be accessed through
RESOURCE_VIEW or the
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 and 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 PATH_VIEW and RESOURCE_VIEW columns is of
XMLType. DML on 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 (
Here are some examples of a
RESOURCE_VIEW that include resources specified by paths:
Returns the following:
Returns the following:
For absolute paths use
This returns the following:
SELECT ANY_PATH, path(1), path(2) FROM resource_view WHERE UNDER_PATH(res, '/a/b', 1) = 1 or UNDER_PATH(res, '/a/e', 2) = 1;
This returns the following:
ANY_PATH PATH(1) PATH(2) ---------------------------------------------------- /a/b/c c /a/b/c/d c/d /a/e/c c /a/e/c/d c/d 4 rows returned
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-4.
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:
XML DB uses the
xdbconfig file for configuring the system and protocol environment. In Release 2 (220.127.116.11) it includes an element
resource-view-cache-size parameter that defines the in-memory size of the RESOURCE_VIEW cache. The default value is 1048576.
Some queries on RESOURCE_VIEW and PATH_VIEW can be sped up by tuning
resource-view-cache-size. In general, the bigger the cache size, the faster the query. The default
resource-view-cache-size is appropriate for most cases. However you may want to enlarge your resource-view-cache-size element when querying sizable RESOURCE_VIEWs.
XDB$RESOURCE table in Oracle XML DB user schema stores in Oracle XML DB the metadata and data corresponding to resources, such as files and folders. You can search for resources containing a specific keyword by using the
CONTAINS operator in
select any_path from resource_view where contains(res, 'Oracle') > 0 and under_path(res, '/myDocuments') > 0;
To evaluate such queries, you must create a Context Index on the
XDB$RESOURCE table. Depending on the type of documents stored in Oracle XML DB, choose one of the following options for creating your Context Index:
dbmsxdbt.sql) to create and configure the Context Index.
DBMS_XDBT package also includes procedures to sync and optimize the index. You can use the
configureAutoSync() procedure to configure automatic sync of the index by using job queues.