HOWTO: Create XMLType Table for Binary XML Usage

Binary XML storage can also be combined with an XML Schema. As shown in Mark Drakes presentation whitepaper (slide 6), the lower right of the diagram, shown below, marks the section for XML Schema (structured, schema based) binary XML storage.

Slide 6 of “Oracle Database 11g XML DB Presentation (PDF) July 2007″

XML Use Cases - Oracle Database 11g XML DB Presentation (PDF) July 2007, Mark Drake, Oracle

Click picture to enlarge

The following example will show how you can create an XMLType table making use of a defined XML Schema and how to avoid the ORA-44424 error (BINARY XML storage requires XML Schema registered for BINARY usage).

SQL> begin
  1     DBMS_XMLSCHEMA.registerSchema(
  2       SCHEMAURL  => 'http://localhost/public/xsd/NORMAL.xsd',
  3       SCHEMADOC  => xdbURIType('/public/root.xsd').getClob(),
  4       LOCAL      => FALSE,     -- local
  5       GENTYPES   => FALSE,     -- generate object types
  6       GENBEAN    => FALSE,     -- no java beans
  7       GENTABLES  => FALSE,     -- generate object tables
  8       FORCE      => FALSE,
  9       OWNER      => USER);
 10 end;
 11 /
 
PL/SQL procedure successfully completed.
 
SQL> create table XMLTABLE of XMLType
  2  XMLTYPE store as BINARY XML
  3  XMLSCHEMA "http://localhost/public/xsd/NORMAL.xsd" 
  4       ELEMENT "ROOT";
create table XMLTABLE of XMLType
*
ERROR at line 1:
ORA-44424: 
BINARY XML storage requires XML Schema registered for BINARY usage
.

So how to avoid the ORA-44424 error?

ORA-44424: BINARY XML storage requires XML Schema registered for BINARY usage

  • Cause: An attempt was made to specify BINARY XML storage using an XML schema not registered for BINARY.
  • Action: Specify a schema registered for BINARY usage. Else specify CLOB or OBJ-REL storage for column.

Preparing the environment

The following scripting shows the environment, the creation of a database schema called “marco” and the content of the used XML Schema called “root.xsd“. The environment is a Oracle Enterprise Edition database Version 11.1.0.6.0 on Oracle Enterprise Linux Version 5.

[oracle@homework xml]$ . oraenv
ORACLE_SID = [oracle] ? XMLDB
 
The Oracle base FOR ORACLE_HOME=/oracle/product/11.1.0/db_1 IS /oracle
 
[oracle@homework xml]$ sqlplus /nolog
 
SQL*Plus: Release 11.1.0.6.0 - Production ON Fri Aug 17 14:14:44 2007
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
SQL> CONNECT  / AS sysdba
Connected.
 
SQL> host
 
[oracle@homework xml]$ pwd
/oracle/xml
 
[oracle@homework xml]$ ls
 
root.xsd
root.xml
 
[oracle@homework xml]$ exit
exit

Click on the links below to see the content of the root.xsd or root.xml files:

The root.xsd file can also presented via XMLSpy as shown below:

XML Schema of “root.xsd”

 

Now let’s create a oracle database user and a directory alias so we can access the files on disk.

SQL> CREATE USER marco IDENTIFIED BY marco;
 
USER created.
 
SQL> GRANT dba, xdbadmin TO marco;
 
GRANT succeeded.
 
SQL> CONNECT marco/marco;
Connected.
 
SQL> CREATE directory XMLDIR AS '/oracle/xml';
 
Directory created.

The next step is to load the XML Schema into the repository as a resource, an ’31098″ event is set to get extra info regarding the XML Schema registration.

SQL> ALTER SESSION SET events='31098 trace name context forever';
 
SESSION altered.
 
SQL> SET serveroutput ON
SQL> SET long 10000000
SQL> SET pages 5000
 
 
/* -- Delete resource if exist
 
SQL> begin
  2     dbms_xdb.deleteresource('/public/root.xsd');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
*/
 
SQL> DECLARE
  2     XMLdoc XMLType;
  3  BEGIN
  4          XMLdoc:=(XMLTYPE(bfilename('XMLDIR','root.xsd'),NLS_CHARSET_ID('AL32UTF8')));
  5
  6     IF(DBMS_XDB.CREATERESOURCE('/public/root.xsd', XMLdoc))
  7          THEN
  8             DBMS_OUTPUT.PUT_LINE('Resource is created');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('Cannot create resource');
 11     END IF;
 12     COMMIT;
 13  END;
 14  /
 
Resource IS created
 
PL/SQL PROCEDURE successfully completed.
 
 
SQL> SELECT xdbURIType ('/public/root.xsd').getClob() FROM dual;
 
XDBURITYPE('/PUBLIC/ROOT.XSD').GETCLOB()
--------------------------------------------------------------------------------
< ?xml version="1.0" encoding="UTF-8"?>
<xs :schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.o
racle.com/xdb" elementFormDefault="qualified" attributeFormDefault="unqualified"
 xdb:storeVarrayAsTable="true">
  </xs><xs :element name="ROOT">
    </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>

To register the XML Schema for use with binary XML, the package DBMS_XMLSCHEMA.REGISTER_BINARYXML has to be used for the OPTIONS in the package DBMS_XMLSCHEMA.REGISTERSCHEMA.

 
/* -- 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; 
/ 
*/
 
SQL> -- Register schema for binary usage
 
SQL> BEGIN
  2   DBMS_XMLSCHEMA.registerSchema(
  3     SCHEMAURL  => 'http://localhost/public/xsd/myapp.xsd',
  4     SCHEMADOC  => xdbURIType('/public/root.xsd').getClob(),
  5     LOCAL      => FALSE,  -- local
  6     GENTYPES   => FALSE,  -- generate object types
  7     GENBEAN    => FALSE,  -- no java beans
  8     GENTABLES  => FALSE,  -- generate object tables
  9     FORCE      => FALSE,
 10     OPTIONS    => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
 11     OWNER      => USER);
 12  END;
 13  /
 
PL/SQL PROCEDURE successfully completed.
 
 
SQL> CREATE TABLE XMLBIN OF XMLType
  2  XMLTYPE store AS BINARY XML
  3  XMLSCHEMA "http://localhost/public/xsd/myapp.xsd" ELEMENT "ROOT";
 
TABLE created.
 
SQL> DESC XMLBIN
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ------------------
TABLE OF SYS.XMLTYPE(XMLSchema "http://localhost/public/xsd/myapp.xsd" 
 Element "ROOT") STORAGE BINARY
 
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','XMLBIN')
  2  FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','XMLBIN')
--------------------------------------------------------------------------------
 
  CREATE TABLE "MARCO"."XMLBIN" OF "SYS"."XMLTYPE"
  XMLTYPE STORE AS BASICFILE BINARY XML  (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
  XMLSCHEMA "http://localhost/public/xsd/myapp.xsd" ELEMENT "ROOT" ID 4382 DISALLOW 
  NONSCHEMA PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

Now we can load the root.xml file to be inserted in the XMLType table XMLBIN (and/or any other XML data that conforms to the XML Schema used).

SQL> DECLARE
  2     XMLdoc XMLType;
  3  BEGIN
  4          XMLdoc:=(XMLTYPE(bfilename('XMLDIR','root.xml'),NLS_CHARSET_ID('AL32UTF8')));
  5
  6     IF(DBMS_XDB.CREATERESOURCE('/public/root.xml', XMLdoc))
  7          THEN
  8             DBMS_OUTPUT.PUT_LINE('Resource is created');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('Cannot create resource');
 11     END IF;
 12     COMMIT;
 13  END;
 14  /
 
Resource IS created
 
PL/SQL PROCEDURE successfully completed.
 
 
SQL> SELECT * FROM xmlbin;
 
no ROWS selected
 
SQL> INSERT INTO XMLBIN
  2  VALUES
  3  (XMLTYPE(bfilename('XMLDIR','root.xml'),NLS_CHARSET_ID('AL32UTF8')));
 
1 ROW created.
 
SQL> SELECT * FROM XMLBIN;
 
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
< ?xml version="1.0" encoding="US-ASCII"?>
<!--Sample XML file generated by XMLSPY v2004 rel. 3 U (http://www.xmlspy.com)--
>
<root xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/
XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost/public/xsd/m
yapp.xsd">
  <id>0</id>
  <info>
    <info_id>0</info_id>
    <info_content>Text</info_content>
  </info>
</root>
 
 
-- NLS_LANG in client session not properly set therefore encoding shows "US-ASCII"

Here you can download the script that contains the statements used in this post:

The XML Schema file “root.xsd” and XML Instance “root.xml” can also be seen now via HTTP access as shown below.

Resources registered in XMLDB - presented via the HTTP access of the XMLDB Protocol Server

Click picture to enlarge

Hope I was of help.

:-)