Just an example of creating XMLType Tables

The following is just an simple example of creating XMLType Tables (binary and object relational), that I want to share here for future reference.

The XML Schema’s are scrabled, because I still didn’t find a proper WordPress plugin. The original example can be found on the OTN XMLDB Forum (“How to DESCRIBE a table (XML Binary storage)“).

Object Relational Storage

set serveroutput on
set long 1000000
set pages 5000
 
-----------------------------------------
-- Stored table with OR storage option --
-----------------------------------------
 
 
drop table root_tbl purge;
 
var schemaPath varchar2(256)
var schemaURL  varchar2(256)
 
begin
  :schemaURL := 'http://localhost/public/xsd/myschema.xsd';
  :schemaPath := '/public/myschema.xsd';
end;
/ 
 
call dbms_xmlSchema.deleteSchema(:schemaURL,4);
 
declare
  res boolean;
  xmlSchema xmlType := xmlType(
'< ?xml version="1.0" encoding="UTF-8" ?>

  
     
         Example XML Schema
     
     
        
            
            
        
     
  
  
     
         
           
           
         
     
  
'
);
begin
  if (dbms_xdb.existsResource(:schemaPath)) then
      dbms_xdb.deleteResource(:schemaPath);
  end if;
  res := dbms_xdb.createResource(:schemaPath,xmlSchema);
end;
/ 
 
alter session set events='31098 trace name context forever';
 
DECLARE
  BINARY_XML boolean:=FALSE;
BEGIN
	IF (BINARY_XML) 
	THEN
	   dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
	                                 SCHEMADOC => xdbUriType(:schemaPath).getXML(),
	                                 LOCAL     => TRUE,
	                                 GENTYPES  => FALSE,
	                                 GENBEAN   => FALSE,
	                                 GENTABLES => TRUE,
	                                 FORCE     => FALSE,
	                                 OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
	                                 OWNER     => USER);
	ELSE
	   dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
	                                 SCHEMADOC => xdbUriType(:schemaPath).getXML(),
	                                 LOCAL     => TRUE,
	                                 GENTYPES  => TRUE,
	                                 GENBEAN   => FALSE,
	                                 GENTABLES => TRUE,
	                                 FORCE     => FALSE,
	                                 OWNER     => USER);
	END IF;
END;
/ 
 
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ROOT_TBL                       TABLE
1 rows selected.
 
SQL> desc "ROOT_TBL"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://localhost/public/xsd/myschema.xsd" Element "ROOT") STORAGE Object-relational TYPE "ROOT667_T"
 
SQL> desc "ROOT667_T"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 INFO                                               INFO665_T
 
SQL> desc "INFO665_T"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 INFO_ID                                            NUMBER(38)
 INFO_CONTENT                                       VARCHAR2(4000 CHAR)
 

The same, but now based on binary storage.

XML Binary storage

------------------------------------------
-- Stored table with BIN storage option --
------------------------------------------
 
drop table root_tbl purge;
 
var schemaPath varchar2(256)
var schemaURL  varchar2(256)
 
begin
  :schemaURL := 'http://localhost/public/xsd/myschema_bin.xsd';
  :schemaPath := '/public/myschema.xsd';
end;
/ 
 
call dbms_xmlSchema.deleteSchema(:schemaURL,4);
 
declare
  res boolean;
  xmlSchema xmlType := xmlType(
'< ?xml version="1.0" encoding="UTF-8" ?>

  
     
         Example XML Schema
     
     
        
            
            
        
     
  
  
     
         
           
           
         
     
  
'
);
begin
  if (dbms_xdb.existsResource(:schemaPath)) then
      dbms_xdb.deleteResource(:schemaPath);
  end if;
  res := dbms_xdb.createResource(:schemaPath,xmlSchema);
end;
/ 
 
alter session set events='31098 trace name context forever';
 
DECLARE
  BINARY_XML boolean:=TRUE;
BEGIN
	IF (BINARY_XML) 
	THEN
	   dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
	                                 SCHEMADOC => xdbUriType(:schemaPath).getXML(),
	                                 LOCAL     => TRUE,
	                                 GENTYPES  => FALSE,
	                                 GENBEAN   => FALSE,
	                                 GENTABLES => TRUE,
	                                 FORCE     => FALSE,
	                                 OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
	                                 OWNER     => USER);
	ELSE
	   dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
	                                 SCHEMADOC => xdbUriType(:schemaPath).getXML(),
	                                 LOCAL     => TRUE,
	                                 GENTYPES  => TRUE,
	                                 GENBEAN   => FALSE,
	                                 GENTABLES => TRUE,
	                                 FORCE     => FALSE,
	                                 OWNER     => USER);
	END IF;
END;
/ 
  
SQL> desc "ROOT_TBL"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://localhost/public/xsd/myschema_bin.xsd" Element "ROOT") STORAGE BINARY
 
SQL> select schema_url, binary from user_xml_schemas;
 
SCHEMA_URL                                         BIN
-------------------------------------------------- ---
http://localhost/public/xsd/myschema_bin.xsd       YES
http://localhost/public/xsd/myschema.xsd           NO
 
2 rows selected.
.

For future reference a text file with the code can be downloaded below:

Marco.

 
Marco Gralike Written by: