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"?>

  
    
      Example XML Schema
    
      
        
        
      
      
    
      
        
        
      

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"?>

		
  • Sandeep
    January 30

    Thanks for the nice explaination.

  • JB
    April 9

    Great tutorial!!! Thanks a lot!!!

  • sky_lt
    August 26

    Hi,

    Colud you please provide an example how to create xml table with multiple
    XML schemas. I should be smt like that:

    CREATE TABLE TEST OF XMLType
    XMLTYPE store AS BINARY XML
    XMLSCHEMA “product.xsd” ELEMENT “PRODUCT”,
    XMLSCHEMA “person.xsd” ELEMENT “PERSON”;

    It generates error: ORA-19006: XMLType TYPE storage option not appropriate for storage type

    Database: 11.2.0.2.0

    XML schemas “product.xsd” and “person.xsd” are registered in binary

    Thanks

  • Comments are closed.

    Bad Behavior has blocked 435 access attempts in the last 7 days.