Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-01 |
|
This chapter describes the SQL-based mechanisms, RESOURCE_VIEW
and PATH_VIEW
, used to access Oracle XML DB Repository data. It discusses the SQL operators UNDER_PATH
and EQUALS_PATH
used to query resources based on their path names and the SQL operators PATH
and DEPTH
that return the resource path names and depth.
It contains the following sections:
Figure 15-1 shows how Oracle XML DB RESOURCE_VIEW
and 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_VIEW
s, and vice versa.
RESOURCE_VIEW
and 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 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
The 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 PATH_VIEW
s.
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 RESOURCE_VIEW
and PATH_VIEW
is:
PATH_VIEW
displays all the path names to a particular resource whereas RESOURCE_VIEW
displays one of the possible path names to the resourcePATH_VIEW
also displays the properties of the linkFigure 15-3 illustrates the difference between Resource and PATH_VIEW
.
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.
The 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.
You can perform the following operations using UNDER_PATH
and EQUALS_PATH
:
See the "Using the Resource View and Path View API" and EQUALS_PATH
.
This section describes the RESOURCE_VIEW
and PATH_VIEW
operators:
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 UNDER_PATH
syntax.
Table 15-1 describes the UNDER_PATH
syntax.
The 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.
EQUALS_PATH INTEGER EQUALS_PATH( resource_column,pathname);
where:
path_view
or resource_view.
pathname
is 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:
PATH VARCHAR2 PATH( correlation);
where:
correlation
is an integer that can be used to correlate the UNDER_PATH
operator (a primary operator) with ancillary operators (PATH
and DEPTH
).DEPTH is an ancillary operator that returns the folder depth of the resource under the specified starting path.
DEPTH INTEGER DEPTH( correlation);
where:
correlation
is an integer that can be used to correlate the UNDER_PATH
operator (a primary operator) with ancillary operators (PATH
and DEPTH
).
The following RESOURCE_VIEW
and PATH_VIEW
examples use operators UNDER_PATH
, EQUALS_PATH
, PATH
, and DEPTH
.
The following examples illustrate how you can access paths, resources, and link properties in the Repository:
select any_path from resource_view where any_path like '/sys%';
select any_path, extract(res, '/display_name') from resource_view where under_path(res, '/sys') = 1;
select path(1) from resource_view where under_path (res, 3, '/sys',1)=1;
select path(1) PATH,depth(1) depth from path_view where under_path(RES, 3,'/sys',1)=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%';
select path(1) from path_view where under_path(res, 3,'/sys', 1) > 0 ;
select any_path from resource_view where equals_path(res, '/sys') > 0;
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:
delete from resource_view where any_path = '/home/SCOTT/example
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';
update path_view set path = '/home/XDB' where path = '/home/SCOTT/example'
Note: 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 The query plan for Example 15-1 will be more optimal than that of Example 15-2. |
See Also:
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 RESOURCE_VIEW
and PATH_VIEW
in SQL.
For example, you can perform the following operations with these resource_view and PATH_VIEW
SQL clauses:
UPDATE RESOURCE_VIEW SET ... WHERE extractValue(resource, '/display_name') = 'My stuff'
SELECT FROM RESOURCE_VIEW WHERE UNDER_PATH(resource, '/sys') ...
INSERT INTO PATH_VIEW SELECT .... FROM PATH_VIEW WHERE ...
|
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|