HOWTO: The Four Create Table statements for XML Binary Storage

The following simple create table examples demonstrate the four different ways to create XML Binary XML data storage. It demonstrates, in a simple way, how to create a table for XML Binary data storage.

XML Table with a column based on Binary Storage

XML Table with a column based on Binary Storage making use of a XML Schema (registered for binary use)

XMLType Table based on Binary Storage

XMLType Table based on Binary Storage making use of a XML Schema (registered for binary use)


SQL> show user
USER is "SYSTEM"

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create user XMLTEST identified by XMLTEST account unlock;

User created.

SQL> grant dba, xdbadmin to xmltest;

Grant succeeded.

SQL> conn XMLTEST/XMLTEST
Connected.

SQL> create table XMLBIN_COLUMN
  2  (test XMLTYPE)
  3  XMLTYPE COLUMN "TEST" STORE AS BINARY XML
  4  ;

Table created.

SQL> create table XMLBIN_COLUMN_XSD
  2  (test XMLTYPE)
  3  XMLTYPE COLUMN "TEST" STORE AS BINARY XML
  4  XMLSCHEMA "http://localhost/public/xsd/myapp.xsd"
  5            ELEMENT "ROOT"
  6  ;

Table created.

SQL> create table XMLBIN_TABLE of XMLType
  2  XMLTYPE store AS BINARY XML;

Table created.

SQL> create table XMLBIN_TABLE_XSD of XMLType
  2  XMLTYPE store AS BINARY XML
  3  XMLSCHEMA "http://localhost/public/xsd/myapp.xsd" 
  4  ELEMENT "ROOT";

Table created.

How to register a XML Schema for XML Binary storage, is further demonstrated in the post: “HOWTO: Create XMLType Table for Binary XML Usage”.

A short reminder is shown here:

/* -- Register schema for binary usage
 
begin
 DBMS_XMLSCHEMA.registerSchema(
   SCHEMAURL  => 'http://localhost/public/xsd/myapp.xsd',
   SCHEMADOC  => xdbURIType('/public/root.xsd').getClob(),
   LOCAL      => FALSE,  -- local
   GENTYPES   => FALSE,  -- generate object types
   GENBEAN    => FALSE,  -- no java beans
   GENTABLES  => FALSE,  -- generate object tables
   FORCE      => FALSE,          
   OPTIONS    => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
   OWNER      => USER);
end; 
/ 
*/

Don’t forget that it is easy to see what has been created via the DBMS_METADATA.GET_DDL method; displaying the full syntax (even for the XML Schema referenced objects).

🙂

Related Posts: