XFILES (Part 4) – The Supporting XDB Utility Packages Needed

Its free. Its cool. You need them for the APEX XFILES demo application. As far a as I know now, you need two supporting PL/SQL packages to make the APEX XFILES application work:

• xdbpm.zip
• xdbutilities.zip

The origins of those PL/SQL packages, etc, are supporting code used by the XMLDB Development team themselves. More info, demo’s or scripts can be found on the OTN XMLDB website. The XMLDB Development team started to split the scripts up for use in specific areas. The old xdbutility zip package had most of them still combined. Now the xdbutilities.zip package with PL/SQL scripts and packages (aka. the “Oracle XML DB Ease of Use Tools for Structured Storage“) its specific usage is pinpointed on manipulating and managing XML Schemas which are used for XMLType-based storage (I saw that 99% of them reflect, are usable with XMLType tables Object Relational storage only). In the now, for the first time, available document it states:

This document describes XDB ease of use tools, which includes packages designed to help you control the schema registration options, thus optimizing storage.

These packages also contain functions to help with repetitive tasks, such as indexing and creating views. This is useful because XML DB structured storage provides relational performance for queries, but requires that you register the schema in an optimal way to support your particular use case.

Until now these packages were not wrapped. Now the XMLDB Development team started to wrap the code which most of the time is the first signal that it is near production readiness. So if you still have an old version somewhere then safeguard it.

The accompanied document in the zip file with PL/SQL code a good read and interesting because of a lot of “Performance Best Practices” (implemented also by the XMLDB Development team themselves), regarding handling XML. It describes these practices, which packages implement these and are automatically implemented while using them.

Structured Storage (xdbutilities.zip)

There are two storage models that implement “structured storage” while using the XMLType: Binary XML and Object Relational. The Object Relational model is defined by its XML Schema. The huge advantage this method is that you are able to create an XMLType table based and configured sole on its XML Schema. Almost everything can be defined in this XML Schema by adding xdb:annotations which are defined by an XMLDB specific XML Schema (namespace reference: xmlns:xdb=”http://xmlns.oracle.com/xdb”).

Based on these xdb:annotations an XML Schema can be fed with extra info that the Oracle XMLDB environment can interpret and automatically generate all the structures needed like:

• default table name
• schema owner
• column naming, column precision
• partitioning information
• enabling or disabling DOM fidelity
• enabling or disabling ordering of elements
• partitioning info
• physical storage info, like table cache
• encoding
• etc

Some of the current XML Schema editors that actually have implemented these xdb:annotations have some “issues” and or you lose easily sight of which annotations have been done for which element and which not, especially if those XML Schemas are very big. After you have created an annotated such an XML Schema; Everything can be generated in one go, while registering this XML Schema.

For instance via the following method (example 1-3 in the manual):

declare
   xml_schema xmltype;
begin
   -- set the default table for purchaseOrder element
   SELECT out INTO xml_schema FROM annotation_tab;

   -- register the annotated schema
   dbms_xmlschema.registerSchema
      ('http://www.example.com/schemas/ipo.xsd',
       xml_schema, 
       TRUE, TRUE, FALSE);
end;
/

So in short the procedure described in the manual while using the xdbutilities PL/SQL packages is:

1. Create an annotation table (annotation_tab) that temporarily keeps tack of the annotation changes made in the XML Schema
2. Use the needed xdbutilities PL/SQL packages to annotate the XML Schema
3. Use the result with DBMS_XMLSCHEMA.REGISTERSCHEMA to register the XML Schema in the XDB Repository while auto-generating the structures needed
4. Store your XML in the generated XMLType table (Object Relational storage)

Using these packages are also useful to make the human process a bit less error prone. Especially while modifying some of those very very very huge XML Schemas out there, a structured way to approach the act of modifying such an XML Schema, via these packages, is very easy. If I can be, I am lazy (applying “kiss”)…Aren’t you as well, from time to time…

🙂

Content

Besides a lot of supporting types and views, views that give insight in the elements, annotations, etc used, the following packages, procedures and functions are created:

DBMS_XDB_CONSTANTS

• FOLDER_HOME
• FOLDER_PUBLIC
• FOLDER_USER_HOME
• ENCODING_UTF8
• ENCODING_WIN1252
• ENCODING_DEFAULT
• NAMESPACE_ORACLE_XDB
• NAMESPACE_RESOURCE
• NAMESPACE_XDBSCHEMA
• NAMESPACE_ACL
• NAMESPACE_ORACLE
• NAMESPACE_XMLSCHEMA
• NAMESPACE_XMLINSTANCE
• NAMESPACE_METADATA
• NAMESPACE_RESOURCE_EVENT
• NAMESPACE_RESOURCE_CONFIG
• NAMESPACE_XMLDIFF
• PREFIX_DEF_RESOURCE
• PREFIX_DEF_ACL
• PREFIX_DEF_XDB
• PREFIX_DEF_XMLSCHEMA
• PREFIX_DEF_XMLINSTANCE
• PREFIX_DEF_RESOURCE_EVENT
• PREFIX_DEF_RESOURCE_CONFIG
• PREFIX_DEF_XMLDIFF
• SCHEMAURL_XDBSCHEMA
• SCHEMAURL_ACL
• SCHEMAURL_RESOURCE
• SCHEMAELEM_RESCONTENT_BINARY
• SCHEMAELEM_RESCONTENT_TEXT
• SCHEMAELEM_RES_ACL

DBMS_XMLSCHEMA_ANNOTATE

• addXDBNamespace
• setDefaultTable
• removeDefaultTable
• setTableProps
• removeTableProps
• disableDefaultTableCreation
• disableDefaultTableCreation
• enableDefaultTableCreation
• enableDefaultTableCreation
• setSQLName
• removeSQLName
• setSQLType
• removeSQLType
• setSQLType
• removeSQLType
• setSQLTypeMapping
• removeSQLTypeMapping
• setTimeStampWithTimeZone
• removeTimeStampWithTimeZone
• setAnyStorage
• removeAnyStorage
• setSQLCollType
• removeSQLCollType
• setSQLCollType
• removeSQLCollType
• removeMaintainDom
• setMaintainDom
• setMaintainDom
• removeMaintainDom
• setOutOfLine
• removeOutOfLine
• setSchemaAnnotations
• getSchemaAnnotations

DBMS_XMLSTORAGE_MANAGE

• scopeXMLReferences
• indexXMLReferences
• disableIndexesAndConstraints
• enableIndexesAndConstraints
• xpath2TabColMapping

(Already) pretty complete, right?

😎

Handling XML in XMLDB (xdbpm.zip)

The origin of the xdbutility set of code is the code set called xdbpm. While xdbutilities now is installed under the XDB and SYS user account, xdbpm installs its set of code under a user schema called XDBPM. Its set of PL/SQL code and other utilities is even more extensive (eh massive), despite its xdbutilities (older?) part it also contains procedures, functions, packages, etc to handle (for example):

• Creating folder and resource structures in the XDB Repository
• Handling zip content, loading it directly into XDB from disk
• ACL Security handling
• XDB Versioning
• Uploading binary, text, file or XML content
• Enabling XML Index search
• Enabling XML Schema search
• Analyzing XML Schemas
• Managing XDB configuration
• Implementation of debugging
• A set of extra XML DOM utilities
• A set of extra helper utilities
• A set of extra XDB import utilities
• Enabling XDB Repository search
• etc

So if you are looking for methods and/or answers to solve thing in the XMLDB part of the Oracle database, besides the FAQ on the main page of the XMLDB OTN forum, have a look if one of the methods or solutions not already (partly) solved in one of those two utility sets xdbutilities or xdbpm. On the other hand there is a lot to learn from the “examples” described by the code in those packages.

HTH

Marco