This chapter describes the predefined public views, RESOURCE_VIEW
and PATH_VIEW
, that provide access to Oracle XML DB repository data. It discusses SQL functions under_path
and equals_path
that query resources based on their path names and path
and depth
that return resource path names and depths, respectively.
This chapter contains these topics:
Figure 22-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 the repository using protocols such as FTP and WebDAV, or using application program interfaces (APIs), can be accessed in SQL using RESOURCE_VIEW
values and PATH_VIEW
values.
RESOURCE_VIEW
consists of a resource, itself an XMLType
, that contains the name of the resource that can be queried, its ACLs, and its properties, static or extensible.
If the content comprising the resource is XML, stored somewhere in an XMLType
table or view, then the RESOURCE_VIEW
points to the XMLType
row that stores the content.
If the content is not XML, then the RESOURCE_VIEW
stores it as a LOB.
Parent-child relationships between folders (necessary to construct the hierarchy) are maintained and traversed efficiently using the hierarchical index. Text indexes are available to search the properties of a resource, and internal B*Tree indexes over Names and ACLs speed up access to these attributes of the Resource XMLType
.
RESOURCE_VIEW
and PATH_VIEW
, along with PL/SQL package DBMS_XDB
, provide all query-based access to Oracle XML DB and DML functionality that is available through the API.
The base table for RESOURCE_VIEW
is XDB.XDB$RESOURCE
. This table should only be accessed through RESOURCE_VIEW
or the DBMS_XDB
API.
See Also:
Chapter 3, "Using Oracle XML DB"Figure 22-1 Accessing Repository Resources Using RESOURCE_VIEW and PATH_VIEW
The RESOURCE_VIEW
contains one row for each resource in Oracle XML DB Repository. Table 22-1 describes its structure.
The PATH_VIEW
contains one row for each unique path to access a resource in Oracle XML DB Repository. Table 22-2 describes its structure.
Table 22-2 Structure of PATH_VIEW
Column | Datatype | Description |
---|---|---|
|
|
An (absolute) path to repository resource |
|
|
The resource referred to by column |
|
|
Link property |
|
|
Resource OID |
Figure 22-2 illustrates the structure of RESOURCE_VIEW
and PATH_VIEW
.
Note:
Each resource may have multiple paths, called links.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 SQL function under_path
, which 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 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.
Paths in the ANY_PATH
column of the RESOURCE_VIEW
and the PATH
column in the PATH_VIEW
are absolute paths: they start at the root.
Paths returned by the path
function are relative paths under the path name specified by function under_path
. For example, if there are two resources referenced by path names /a/b/c
and /a/d
, respectively, then a path
expression that retrieves paths under folder /a
will return relative paths b/c
and d
.
When there are multiple links to the same resource, only paths under the path name specified by function under_path
are returned. Suppose /a/b/c
, /a/b/d
and /a/e
are links to the same resource, a query on the PATH_VIEW
that retrieves all the paths under /a/b
return only /a/b/c
and /a/b/d
, not /a/e
.
Figure 22-2 RESOURCE_VIEW and PATH_VIEW Structure
Views RESOURCE_VIEW
and PATH_VIEW
differ as follows:
PATH_VIEW
displays all the path names to a particular resource. RESOURCE_VIEW
displays one of the possible path names to the resource
PATH_VIEW
also displays the properties of the link
Figure 22-3 illustrates this difference between RESOURCE_VIEW
and PATH_VIEW
.
Because 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, then the index does not have to do as much work to determine all the possible paths.
Note:
When using theRESOURCE_VIEW
, if you are specifying a path with functions under_path
or equals_path
, then they will find the resource regardless of whether or not that path is the arbitrary one chosen to normally appear with that resource using RESOURCE_VIEW
.Figure 22-3 RESOURCE_VIEW and PATH_VIEW Explained
You can perform the following operations using under_path
and equals_path
:
Given a path name:
Get a resource or its OID
List the directory given by the path name
Create a resource
Delete a resource
Update a resource
Given a condition, containing SQL function under_path
or other SQL functions:
Update resources
Delete resources
Get resources or their OID
See the "Using the RESOURCE_VIEW and PATH_VIEW APIs" and equals_path
.
This section describes the SQL functions applicable to RESOURCE_VIEW
and PATH_VIEW
.
SQL function under_path
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, then a functional implementation of under_path
can be chosen that walks back up the repository. This can be more efficient, because a much smaller number of links are required to be traversed. Figure 22-4 shows the under_path
syntax.
Table 22-3 details the under_path
syntax.
Table 22-3 RESOURCE_VIEW and PATH_VIEW API Syntax: UNDER_PATH
Syntax | Description |
---|---|
|
Determines if a resource is under a specified path. Parameters:
|
|
Determines if a resource is under a specified path, with a depth argument to restrict the number of levels to search. Parameters:
|
|
Determines if a resource is under a specified path, with a correlation argument for related SQL functions. Parameters:
|
|
Determines if a resource is under a specified path with a depth argument to restrict the number of levels to search, and with a correlation argument for related SQL functions. Parameters:
Note that only one of the accessible paths to the resource must be under the path argument for a resource to be returned. |
SQL function equals_path
is used to find the resource with the specified path name. It is functionally equivalent to under_path
with a depth restriction of zero.
equals_path(resource_column, pathname);
where:
resource_column
is the column name or column alias of the RESOURCE
column in the PATH_VIEW
or RESOURCE_VIEW.
pathname
is the path name to resolve.
Figure 22-5 illustrates the complete equals_path
syntax.
SQL function path
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(correlation);
where:
correlation
is an integer that can be used to correlate under_path
with related SQL functions (path
and depth
).
Note:
If a path is not under the specifiedpathname
argument, a NULL
value is returned as the output of the current path.Figure 22-6 illustrates the path
syntax.
The following RESOURCE_VIEW
and PATH_VIEW
examples use SQL functions under_path
, equals_path
, path
, and depth
.
The following examples illustrate how you can access paths, resources, and link properties in Oracle XML DB Repository. The first few examples use resources specified by the following paths:
/a/b/c /a/b/c/d /a/e/c /a/e/c/d
Example 22-1 Determining Paths Under a Path: Relative
This example uses SQL function path
to retrieve the relative paths under path /a/b
.
SELECT path(1) FROM RESOURCE_VIEW WHERE under_path(RES, '/a/b', 1) = 1;
Returns the following:
PATH(1) ------- c c/d 2 rows selected.
Example 22-2 Determining Paths Under a Path: Absolute
This example uses ANY_PATH
to retrieve the absolute paths under path /a/b
.
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, '/a/b') = 1;
This returns the following:
ANY_PATH -------- /a/b/c /a/b/c/d 2 rows selected.
Example 22-3 Determining Paths Not Under a Path
This is the same example as Example 22-2, except that the test is not-equals (!=
) instead of equals (=
). This query finds all paths in the repository that are not under path /a/b
.
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, '/a/b') != 1
This produces a result like the following:
ANY_PATH -------- /a /a/b /a/e /a/e/c /a/e/c/d /home /home/OE /home/OE/PurchaseOrders /home/OE/PurchaseOrders/2002 /home/OE/PurchaseOrders/2002/Apr /home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml /home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml /home/OE/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml . . . /public /sys /sys/acls /sys/acls/all_all_acl.xml /sys/acls/all_owner_acl.xml /sys/acls/bootstrap_acl.xml /sys/acls/ro_all_acl.xml /sys/apps /sys/databaseSummary.xml /sys/log /sys/schemas /sys/schemas/OE /sys/schemas/OE/localhost:8080 . . . 202 rows selected.
Example 22-4 Determining Paths Using Multiple Correlations
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 selected.
To obtain all of the resources under a directory, you can use LIKE
, as shown in Example 22-5. To obtain all of the resources up to a certain number of levels or to obtain the relative path, use SQL function under_path
, as shown in Example 22-7. Example 22-5 is more effiicient than Example 22-7.
Example 22-5 Using ANY_PATH with LIKE
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE ANY_PATH LIKE '/sys%';
This produces a result like the following:
ANY_PATH
--------
/sys
/sys/acls
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/apps
/sys/databaseSummary.xml
/sys/log
/sys/schemas
/sys/schemas/OE
/sys/schemas/OE/localhost:8080
/sys/schemas/OE/localhost:8080/source
/sys/schemas/OE/localhost:8080/source/schemas
/sys/schemas/OE/localhost:8080/source/schemas/poSource
/sys/schemas/OE/localhost:8080/source/schemas/poSource/xsd
/sys/schemas/OE/localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd
/sys/schemas/PUBLIC
/sys/schemas/PUBLIC/www.w3.org
/sys/schemas/PUBLIC/www.w3.org/2001
/sys/schemas/PUBLIC/www.w3.org/2001/xml.xsd
/sys/schemas/PUBLIC/xmlns.oracle.com
. . .
42 rows selected.
Example 22-6 Relative Path Names for Three Levels of Resources
SELECT path(1) FROM RESOURCE_VIEW WHERE under_path (RES, 3, '/sys', 1) = 1;
This produces a result like the following:
PATH(1) ------- acls acls/all_all_acl.xml acls/all_owner_acl.xml acls/bootstrap_acl.xml acls/ro_all_acl.xml apps databaseSummary.xml log schemas schemas/OE schemas/OE/localhost:8080 schemas/PUBLIC schemas/PUBLIC/www.w3.org schemas/PUBLIC/xmlns.oracle.com 14 rows selected.
Example 22-7 Extracting Resource Metadata using UNDER_PATH
SELECT ANY_PATH, extract(RES, '/Resource') FROM RESOURCE_VIEW
WHERE under_path(RES, '/sys') = 1;
This produces a result like the following:
ANY_PATH
--------
EXTRACT(RES,'/RESOURCE')
------------------------
/sys/acls
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
<CreationDate>2005-02-07T18:31:53.093179</CreationDate>
<ModificationDate>2005-02-07T18:31:55.852963</ModificationDate>
<DisplayName>acls</DisplayName>
<Language>en-US</Language>
<CharacterSet>ISO-8859-1</CharacterSet>
<ContentType>application/octet-stream</ContentType>
<RefCount>1</RefCount>
</Resource>
/sys/acls/all_all_acl.xml
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
<CreationDate>2005-02-07T18:31:55.745970</CreationDate>
<ModificationDate>2005-02-07T18:31:55.745970</ModificationDate>
<DisplayName>all_all_acl.xml</DisplayName>
<Language>en-US</Language>
<CharacterSet>ISO-8859-1</CharacterSet>
<ContentType>text/xml</ContentType>
<RefCount>1</RefCount>
</Resource>
. . .
41 rows selected.
Example 22-8 Using Functions PATH and DEPTH with PATH_VIEW
SELECT path(1) path, depth(1) depth FROM PATH_VIEW
WHERE under_path(RES, 3, '/sys', 1) = 1;
This produces a result like the following:
PATH DEPTH ---- ----- acls 1 acls/all_all_acl.xml 2 acls/all_owner_acl.xml 2 acls/bootstrap_acl.xml 2 acls/ro_all_acl.xml 2 apps 1 databaseSummary.xml 1 log 1 schemas 1 schemas/OE 2 schemas/OE/localhost:8080 3 schemas/PUBLIC 2 schemas/PUBLIC/www.w3.org 3 schemas/PUBLIC/xmlns.oracle.com 3 14 rows selected.
Example 22-9 Extracting Link and Resource Information from PATH_VIEW
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%';
This produces a result like the following:
/sys
sys
/
sys
sys
/sys/acls
acls
sys
acls
acls
/sys/acls/all_all_acl.xml
all_all_acl.xml
acls
all_all_acl.xml
all_all_acl.xml
/sys/acls/all_owner_acl.xml
all_owner_acl.xml
acls
all_owner_acl.xml
all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
bootstrap_acl.xml
acls
bootstrap_acl.xml
bootstrap_acl.xml
. . .
42 rows selected.
Example 22-10 All Paths to a Certain Depth Under a Path
SELECT path(1) FROM PATH_VIEW WHERE under_path(RES, 3, '/sys', 1) > 0 ;
This produces a result like the following:
PATH(1) ------- schemas acls log schemas/PUBLIC schemas/PUBLIC/xmlns.oracle.com acls/bootstrap_acl.xml acls/all_all_acl.xml acls/all_owner_acl.xml acls/ro_all_acl.xml schemas/PUBLIC/www.w3.org apps databaseSummary.xml 12 rows selected.
Example 22-11 Using EQUALS_PATH to Locate a Path
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE equals_path(RES, '/sys') > 0;
This produces the following result:
ANY_PATH -------- /sys 1 row selected.
Example 22-12 Retrieve RESID of a Given Resource
SELECT RESID FROM RESOURCE_VIEW WHERE extract(RES, '/Resource/Dispname') = 'example';
This produces a result like the following:
RESID -------------------------------- F301A10152470252E030578CB00B432B 1 row selected.
Example 22-13 Obtaining the Path Name of a Resource from its RESID
DECLARE
resid_example RAW(16);
path VARCHAR2(4000);
BEGIN
SELECT RESID INTO resid_example FROM RESOURCE_VIEW
WHERE extractValue(RES, '/Resource/DisplayName') = 'example';
SELECT ANY_PATH INTO path FROM RESOURCE_VIEW WHERE RESID = resid_example;
DBMS_OUTPUT.put_line('The path is: ' || path);
END;
/
The path is: /public/example
PL/SQL procedure successfully completed.
Example 22-14 Folders Under a Given Path
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, 1, '/sys') = 1 AND existsNode(RES, '/Resource[@Container="true"]') = 1;
This produces a result like the following:
ANY_PATH -------- /sys/acls /sys/apps /sys/log /sys/schemas 4 rows selected.
Example 22-15 Joining RESOURCE_VIEW with an XMLType Table
SELECT ANY_PATH, extract(value(e), '/PurchaseOrder/LineItems').getclobval() FROM purchaseorder e, RESOURCE_VIEW r WHERE extractValue(r.RES, '/Resource/XMLRef') = ref(e) AND ROWNUM < 2;
This produces the following result:
ANY_PATH
--------------------------------------------------------------------------------
EXTRACT(VALUE(E),'/PURCHASEORDER/LINEITEMS').GETCLOBVAL()
--------------------------------------------------------------------------------
/home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml
<LineItems>
<LineItem ItemNumber="1">
<Description>Salesman</Description>
<Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>Big Deal on Madonna Street</Description>
<Part Id="37429155424" UnitPrice="29.95" Quantity="1"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Hearts and Minds</Description>
<Part Id="37429166321" UnitPrice="39.95" Quantity="1"/>
</LineItem>
. . .
<LineItem ItemNumber="23">
<Description>Great Expectations</Description>
<Part Id="37429128022" UnitPrice="39.95" Quantity="4"/>
</LineItem>
</LineItems>
1 row selected.
The following examples illustrate how you can delete resources and paths.
Example 22-16 Deleting Resources
If you delete only leaf resources, then you can use DELETE FROM RESOURCE_VIEW
:
DELETE FROM RESOURCE_VIEW WHERE ANY_PATH = '/public/myfile';
For multiple links to the same resource, deleting from RESOURCE_VIEW
deletes the resource together with all of its links; deleting from PATH_VIEW
deletes only the link with the specified path.
Example 22-17 Deleting Links to Resources
For example, suppose '/home/myfile1'
is a link to '/public/myfile'
:
CALL DBMS_XDB.link('/public/myfile', '/home', 'myfile1');
This SQL DML statement deletes everything in Oracle XML DB Repository that is found at path /home/myfile1
– both the link and the resource:
DELETE FROM RESOURCE_VIEW WHERE equals_path(RES, '/home/myfile1') = 1;
This DML statement deletes only the link with path /home/file1
:
DELETE FROM PATH_VIEW WHERE equals_path(RES, '/home/file1') = 1;
The DELETE
DML operator is not allowed on a nonempty folder. If you try to delete a nonempty folder, you must first delete its contents and then delete the resulting empty folder. This rule must of course be applied recursively to any folders contained in the target folder.
However, the order of the paths returned from a WHERE
clause is not guaranteed, and the DELETE
operator does not allow an ORDER BY
clause in its table-expression subclause. This means that you cannot do the following:
DELETE FROM (SELECT 1 FROM RESOURCE_VIEW WHERE under_path(RES, '/public', 1) = 1 ORDER BY depth(1) DESCENDING);
Example 22-18 illustrates how to delete a nonempty folder.
Example 22-18 Deleting a Nonempty Folder
In this example, folder example
is deleted, along with its subfolder example1
.
SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/home/US1') = 1; PATH -------------------------- /home/US1/example /home/US1/example/example1 2 rows selected. DECLARE CURSOR c1 IS SELECT ANY_PATH p FROM RESOURCE_VIEW WHERE under_path(RES, '/home/US1', 1) = 1 AND existsNode(RES, '/Resource[Owner="US1"]') = 1 ORDER BY depth(1) DESC; del_stmt VARCHAR2(500) := 'DELETE FROM RESOURCE_VIEW WHERE equals_path(RES, :1)=1'; BEGIN FOR r1 IN c1 LOOP EXECUTE IMMEDIATE del_stmt USING r1.p; END LOOP; END; / PL/SQL procedure successfully completed. SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/home/US1') = 1; no rows selected
Note:
As always, care should be taken to avoid deadlocks with concurrent transactions when operating on multiple rows.This section illustrates how to update resources and paths.
Example 22-19 Updating a Resource
This example changes the resource at path /test/HR/example/paper
. This is the complete resource before the update:
SELECT r.RES.getClobVal()
FROM RESOURCE_VIEW r WHERE equals_path(RES, '/test/HR/example/paper') = 1;
R.RES.GETCLOBVAL()
--------------------------------------------------------------------------------
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
ef="true">
<CreationDate>2005-04-29T16:30:01.588835</CreationDate>
<ModificationDate>2005-04-29T16:30:01.588835</ModificationDate>
<DisplayName>paper</DisplayName>
<Language>en-US</Language>
<CharacterSet>ISO-8859-1</CharacterSet>
<ContentType>application/octet-stream</ContentType>
<RefCount>1</RefCount>
<ACL>
<acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
emaLocation="http://xmlns.oracle.com/xdb/acl.xsd http:
//xmlns.oracle.com/xdb/acl.xsd">
<ace>
<principal>PUBLIC</principal>
<grant>true</grant>
<privilege>
<all/>
</privilege>
</ace>
</acl>
</ACL>
<Owner>TESTUSER1</Owner>
<Creator>TESTUSER1</Creator>
<LastModifier>TESTUSER1</LastModifier>
<SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary</SchemaElement
>
<Contents>
<binary>4F7261636C65206F7220554E4958</binary>
</Contents>
</Resource>
1 row selected.
All of the XML elements shown above are resource metadata elements, with the exception of Contents
, which contains the resource content.
This UPDATE
statement updates (only) the DisplayName
metadata element.
UPDATE RESOURCE_VIEW r SET r.RES = updateXML(r.RES, '/Resource/DisplayName/text()', 'My New Paper') WHERE ANY_PATH='/test/HR/example/paper'; 1 row updated. SELECT r.RES.getClobVal() FROM RESOURCE_VIEW r WHERE equals_path(RES, '/test/HR/example/paper') = 1; R.RES.GETCLOBVAL() -------------------------------------------------------------------------------- <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR ef="true"> <CreationDate>2005-04-29T16:30:01.588835</CreationDate> <ModificationDate>2005-04-29T16:30:01.883838</ModificationDate> <DisplayName>My New Paper</DisplayName> <Language>en-US</Language> . . . <Contents> <binary>4F7261636C65206F7220554E4958</binary> </Contents> </Resource> 1 row selected.
See Also:
Chapter 26, "User-Defined Repository Metadata" for additional examples of updating resource metadataNote that, by default, the DisplayName
element content, paper
, was the same text as the last location step of the resource path, /test/HR/example/paper
. This is only the default value, however. The DisplayName
is independent of the resource path, so updating it does not change the path.
Element DisplayName
is defined by the WebDAV standard, and it is recognized by WebDAV applications. Non-WebDAV applications, such as an FTP client, will not recognize the DisplayName
of a resource. An FTP client lists the above resource as paper
(using FTP command ls
, for example) even after the UPDATE
operation.
Example 22-20 Updating a Path in the PATH_VIEW
This example changes the path for the above resource from /test/HR/example/paper
to /test/myexample
. It is analogous to using the Unix command mv /test/HR/example/paper /test/myexample
.
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, '/test') = 1; ANY_PATH -------- /test/HR /test/HR/example /test/HR/example/paper 3 rows selected. UPDATE PATH_VIEW SET PATH = '/test/myexample' WHERE PATH = '/test/HR/example/paper'; ANY_PATH -------- /test/HR /test/HR/example /test/myexample 3 rows selected.
See Also:
Table 20-3, "Accessing Oracle XML DB Repository: API Options" for additional examples that use the SQL functions that apply toRESOURCE_VIEW
and PATH_VIEW
The repository operations listed in Table 20-3 typically apply to a single 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 SQL with RESOURCE_VIEW
and PATH_VIEW
.
For example, you can perform the following operations:
Updating based on attributes – see Example 22-21
Finding resources inside a folder – see Example 22-22
Copying a set of Oracle XML DB resources – see Example 22-23
Example 22-21 Updating Resources Based on Attributes
UPDATE RESOURCE_VIEW
SET RES = updateXML(RES, '/Resource/DisplayName/text()', 'My New Paper')
WHERE extractValue(resource, '/Resource/DisplayName') = 'My Paper';
SELECT ANY_PATH FROM RESOURCE_VIEW
WHERE extractValue(RES, '/Resource/DisplayName') = 'My New Paper';
ANY_PATH
---------------
/test/myexample
1 row selected.
Example 22-22 Finding Resources Inside a Folder
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(resource, '/sys/schemas/PUBLIC/xmlns.oracle.com/xdb') = 1; ANY_PATH -------------------------------------------------------------- /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBFolderListing.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBResource.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBStandard.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/acl.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/dav.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log/ftplog.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log/httplog.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log/xdblog.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/stats.xsd /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/xdbconfig.xsd 12 rows selected.
Example 22-23 Copying Resources
This SQL DML statement copies all of the resources in folder public
to folder newlocation
. It is analogous to the Unix command cp /public/* /newlocation
. Target folder newlocation
must exist before the copy.
SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/test') = 1; PATH ----------------- /test/HR /test/HR/example /test/myexample 3 rows selected. INSERT INTO PATH_VIEW SELECT '/newlocation/' || path(1), RES, LINK, NULL FROM PATH_VIEW WHERE under_path(RES, '/test', 1) = 1 ORDER BY depth(1); 3 rows created. SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/newlocation') = 1; PATH ------------------------ /newlocation/HR /newlocation/HR/example /newlocation/myexample 3 rows selected.
Oracle XML DB uses the xdbconfig.xml
file for configuring the system and protocol environment. It includes an element parameter resource-view-cache-size
that defines the in-memory size of the RESOURCE_VIEW
cache. The default value is 1048576
.
The performance of some queries on RESOURCE_VIEW
and PATH_VIEW
can be improved 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, but you may want to enlarge your resource-view-cache-size
element when querying a sizable RESOURCE_VIEW
.
The extensible optimizer decides whether SQL functions under_path
and equals_path
are evaluated by a domain index scan or by functional implementation. To achieve the optimal query plan, the optimizer needs statistics for Oracle XML DB. Statistics can be collected by analyzing the Oracle XML DB tables and hierarchical index under XDB
using the ANALYZE
command or the DBMS_STATS
package. The following is an example of using the ANALYZE
command:
ANALYZE TABLE XDB$H_LINK COMPUTE STATISTICS; ANALYZE TABLE XDB$RESOURCE COMPUTE STATISTICS; ANALYZE INDEX XDBHI_IDX DELETE STATISTICS;
The default limits for the following elements are soft limits. The system automatically adapts when these limits are exceeded.
XDBCORE-LOADABLEUNIT-SIZE
- This element indicates the maximum size to which a loadable unit (partition) can grow in Kilobytes. When a partition is read into memory or a partition is built while consuming a new document, the partition is built until it reaches the maximum size. The default value is 16 Kb.
XDBCORE-XOBMEM-BOUND
- This element indicates the maximum memory in kilobytes that a document is allowed to occupy. The default value is 1024 Kb. Once the document exceeds this number, some loadable units (partitions) are swapped out.
Table XDB$RESOURCE
in database schema XDB
stores the metadata and content of repository resources. You can search for resources that contain a specific keyword by using SQL function contains
with RESOURCE_VIEW
or PATH_VIEW
.
Example 22-24 Find All Resources Containing "Paper"
SELECT PATH FROM PATH_VIEW WHERE contains(RES, 'Paper') > 0; PATH ----------------------- /newlocation/myexample /test/myexample 2 rows selected.
Example 22-25 Find All Resources Containing "Paper" that are Under a Specified Path
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE contains(RES, 'Paper') > 0 AND under_path(RES, '/test') > 0; ANY_PATH ---------------- /test/myexample 1 row selected.
To evaluate such queries, you must first 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:
If Oracle XML DB contains only XML documents, that is, no binary data, a regular Context Index can be created on the XDB$RESOURCE
table. This is the case for Example 22-25.
CREATE INDEX xdb$resource_ctx_i ON XDB.XDB$RESOURCE(OBJECT_VALUE) INDEXTYPE IS CTXSYS.CONTEXT;
If Oracle XML DB contains binary data such as Microsoft Word documents, a user filter is required to filter such documents prior to indexing. Use package DBMS_XDBT
(dbmsxdbt.sql
) to create and configure the Context Index.
-- Install the package - connected as SYS @dbmsxdbt -- Create the preferences EXEC DBMS_XDBT.createPreferences; -- Create the index EXEC DBMS_XDBT.createIndex;
See Also:
Oracle Database PL/SQL Packages and Types Reference, for information on installing and using DBMS_XDBT
.
Package DBMS_XDBT
also includes procedures to synchronize and optimize the index. You can use procedure configureAutoSync()
to configure automatically sync the index by using job queues.