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" ?>
<xs :schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           elementFormDefault="qualified"
           attributeFormDefault="unqualified"
           xdb:storeVarrayAsTable="true">
  </xs><xs :element name="ROOT" xdb:defaultTable="ROOT_TBL" xdb:maintainDOM="false">
     </xs><xs :annotation>
         </xs><xs :documentation>Example XML Schema</xs>
 
     <xs :complexType>
        </xs><xs :sequence>
            <xs :element name="ID" type="xs:integer" />
            <xs :element ref="INFO" />
        </xs>
 
 
  <xs :element name="INFO">
     </xs><xs :complexType>
         </xs><xs :sequence>
           <xs :element name="INFO_ID" type="xs:integer" />
           <xs :element name="INFO_CONTENT" />
         </xs>
 
 
'
);
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" ?>
<xs :schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           elementFormDefault="qualified"
           attributeFormDefault="unqualified"
           xdb:storeVarrayAsTable="true">
  </xs><xs :element name="ROOT" xdb:defaultTable="ROOT_TBL" xdb:maintainDOM="false">
     </xs><xs :annotation>
         </xs><xs :documentation>Example XML Schema</xs>
 
     <xs :complexType>
        </xs><xs :sequence>
            <xs :element name="ID" type="xs:integer" />
            <xs :element ref="INFO" />
        </xs>
 
 
  <xs :element name="INFO">
     </xs><xs :complexType>
         </xs><xs :sequence>
           <xs :element name="INFO_ID" type="xs:integer" />
           <xs :element name="INFO_CONTENT" />
         </xs>
 
 
'
);
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: