XFILES (Part 5) – How to Open Up the XML DB Repository for APEX ?

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

  2
true             false
09-SEP-09 02.56.13.606247 PM
09-SEP-09 02.56.16.938907 PM

OE

en-US
US-ASCII
application/octet-stream

OE
SYS
OE


                                 7113E7BB25E83A99E040E40ADCDE1F15

OE

/home/XDBPM
737BA0F79CBDEA44E040FC0A67FC7FC2

  2

true             false
13-SEP-09 08.45.48.464188 PM
18-SEP-09 09.59.35.624711 PM

XDBPM
Home folder for user : XDBPM
en-US
UTF-8
application/octet-stream
XDBPM
XDB
XDB



                                 7113E7BB25E83A99E040E40ADCDE1F15

XDBPM

/home/XFILES APEX Install.txt
790BEB48819BFF34E040FC0A67FC4293

  2
false            false
23-NOV-09 04.00.05.840062 PM
23-NOV-09 04.00.05.840062 PM


XFILES APEX Install.txt

en-US
UTF-8
text/plain
XFILES
XFILES
XFILES


                                 711520CE146B276BE040E40ADCDE2121
6C3FCF2D9D354DC1E03408002087A0B7                83
XFILES APEX Install.txt



3 rows selected.

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.

xfiles_home_folder

Hope to be of help regarding your first steps into the XML DB Repository world and some of its “secrets”.

😎

Related Content

Marco Gralike Written by: