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.
