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.

  1. XML Table with a column based on Binary Storage
  2. XML Table with a column based on Binary Storage making use of a XML Schema (registered for binary use)
  3. XMLType Table based on Binary Storage
  4. 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).

:-)

m4s0n501
Written by: