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:
