The biggest problem the APEX application has, regarding XML DB interaction, is that it doesn’t understand / support (yet) typical XML DB structures like the datatype / operator “XMLType”. To make APEX capable to support these structures some coding has to be done transforming an XMLtype value into other datatypes like CLOB or a varchar2.
In the XFILES application this is supported via the package “XFILES_APEX_SERVICES”. This package contains, among others, a function called “LISTDIRECTORY” which shows the content of files and (underneath) directories in a (root) directory.
For example, via WebDAV access, Oracle XML DB Repository content can be made visible while using Windows Explorer, of the following /home directory.
Click on the picture to enlarge
The actual content seen here (or via HTTP, FTP) is a representation of an XMLType table in the database called XDB$RESOURCE.
SQL> describe XDB.XDB$RESOURCE
Name Null? Type
------------------- ----- --------------------------
TABLE of SYS.XMLTYPE
(XMLSchema
"http://xmlns.oracle.com/xdb/XDBResource.xsd"
Element "Resource")
STORAGE Object-relational
TYPE "XDB$RESOURCE_T"
SQL> desc XDB."XDB$RESOURCE_T"
Name Null? Type
------------------- ----- --------------------------
VERSIONID NUMBER(38)
CREATIONDATE TIMESTAMP(6)
MODIFICATIONDATE TIMESTAMP(6)
AUTHOR VARCHAR2(128)
DISPNAME VARCHAR2(128)
RESCOMMENT VARCHAR2(128)
LANGUAGE VARCHAR2(128)
CHARSET VARCHAR2(128)
CONTYPE VARCHAR2(128)
REFCOUNT RAW(4)
LOCKS RAW(2000)
ACLOID RAW(16)
OWNERID RAW(16)
CREATORID RAW(16)
LASTMODIFIERID RAW(16)
ELNUM NUMBER(38)
SCHOID RAW(16)
XMLREF REF OF XMLTYPE
XMLLOB BLOB
FLAGS RAW(4)
RESEXTRA CLOB
ACTIVITYID NUMBER(38)
VCRUID RAW(16)
PARENTS XDB.XDB$PREDECESSOR_LIST_T
SBRESEXTRA XDB.XDB$XMLTYPE_REF_LIST_T
SNAPSHOT RAW(6)
ATTRCOPY BLOB
CTSCOPY BLOB
NODENUM RAW(6)
SIZEONDISK NUMBER(38)
RCLIST XDB.XDB$RCLIST_T
CHECKEDOUTBYID RAW(16)
BASEVERSION RAW(16)
The XDB.XDB$RESOURCE XMLType Object Relational Table should never be access directly, but accessed via one of the two following views called RESOURCE_VIEW and PATH_VIEW
The RESOURCE_VIEW contains one row for each resource in the Repository. The PATH_VIEW contains one row for each unique path to access a resource in the Repository. In SQL*Plus a describe statement would give you the following overview of the RESOURCE_VIEW and PATH_VIEW:
SQL> set heading off
SQL> set feedback on
SQL> set long 10000
SQL> set pagesize 5000
SQL> set trimspool on
SQL> describe RESOURCE_VIEW
Name Null? Type
----------------------------- -------- --------------------
RES SYS.XMLTYPE(XMLSchem
a "http://xmlns.orac
le.com/xdb/XDBResour
ce.xsd" Element "Res
ource")
ANY_PATH VARCHAR2(4000)
RESID RAW(16)
SQL> describe PATH_VIEW
Name Null? Type
----------------------------- -------- --------------------
PATH VARCHAR2(1024 CHAR)
RES SYS.XMLTYPE(XMLSchem
a "http://xmlns.orac
le.com/xdb/XDBResour
ce.xsd" Element "Res
ource")
LINK SYS.XMLTYPE
RESID RAW(16)
SQL> select * from RESOURCE_VIEW where rownum = 1;
2
/OLAP_XDS
711502AE8E4BB6ECE040E40ADCDE1FDE
1 row selected.
SQL> select * from PATH_VIEW where rownum < 2;
/OLAP_XDS
2
/
Still not very helpful due to the XMLType datatypes defined in those two views… The XDB$RESOURCE XMLType table is defined/bound by its XML Schema called XDBResource.xsd. Based on this XML Schema, that defines the elements in it, one can now derive elements and transform them into “readable” relational datatypes so the APEX application can handle them appropriately. Mark Drake has solved this in the mentioned “XFILES_APEX_SERVICES” package using an XMLTABLE statement wrapped in the “pipelined” function, so its output can be used in a “from TABLE ()”-construct.
The following SQL statement is the basis for opening up the Oracle XML DB Repository for the APEX XFiles application. It shows the data that you would need to create a WebDAV / Content Management Look-and-Feel regarding the content in the directory “/home” as shown before.
SQL> set heading off
SQL> set feedback on
SQL> set long 10000
SQL> set pagesize 5000
SQL> set trimspool on
SQL> select PATH, RESID, RES, R.*, L.*
2 from PATH_VIEW,
3 XMLTable
4 (
5 xmlNamespaces
6 (
7 default 'http://xmlns.oracle.com/xdb/XDBResource.xsd'
8 ),
9 '$RES/Resource' passing RES as "RES"
10 columns
11 IS_FOLDER VARCHAR2(5) PATH '@Container',
12 VERSION_ID NUMBER(38) PATH '@VersionID',
13 CHECKED_OUT VARCHAR2(5) PATH '@IsCheckedOut',
14 CREATION_DATE TIMESTAMP(6) PATH 'CreationDate',
15 MODIFICATION_DATE TIMESTAMP(6) PATH 'ModificationDate',
16 AUTHOR VARCHAR2(128) PATH 'Author',
17 DISPLAY_NAME VARCHAR2(128) PATH 'DisplayName',
18 "COMMENT" VARCHAR2(128) PATH 'Comment',
19 LANGUAGE VARCHAR2(128) PATH 'Language',
20 CHARACTER_SET VARCHAR2(128) PATH 'CharacterSet',
21 CONTENT_TYPE VARCHAR2(128) PATH 'ContentType',
22 OWNED_BY VARCHAR2(64) PATH 'Owner',
23 CREATED_BY VARCHAR2(64) PATH 'Creator',
24 LAST_MODIFIED_BY VARCHAR2(64) PATH 'LastModifier',
25 CHECKED_OUT_BY VARCHAR2(700) PATH 'CheckedOutBy',
26 LOCK_BUFFER VARCHAR2(128) PATH 'LockBuf',
27 VERSION_SERIES_ID RAW(16) PATH 'VCRUID',
28 ACL_OID RAW(16) PATH 'ACLOID',
29 SCHEMA_OID RAW(16) PATH 'SchOID',
30 GLOBAL_ELEMENT_ID NUMBER(38) PATH 'ElNum'
31 ) R,
32 XMLTable
33 (
34 xmlNamespaces
35 (
36 default 'http://xmlns.oracle.com/xdb/XDBStandard'
37 ),
38 '$LINK/LINK' passing LINK as "LINK"
39 columns
40 LINK_NAME VARCHAR2(128) PATH 'Name'
41 ) L
42 where under_path(RES,1,'/home') = 1
/home/OE
73274850D4A5B205E040FC0A67FC6D5C
In the APEX XFiles application the same “/home” folder would show up, except that in this case the “MARCO” APEX user doesn’t have the privileges to see the “XDBPM” and “OE” directories, due to the fact that they weren’t created by the APEX user “MARCO” (but by the Oracle database users XFILES and OE) and this APEX “MARCO” user, also wasn’t granted any ACL read privileges on those directories.
So therefore they don’t show up in XFiles. See the following APEX XFiles picture.
Hope to be of help regarding your first steps into the XML DB Repository world and some of its “secrets”.
😎
Related Content
