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”
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:
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"?>
Thanks for the nice explaination.
Great tutorial!!! Thanks a lot!!!
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.