The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.
This post shows working examples of binary XML storage
1.1. without XML Schema
1.2 with XML Schema
Example 1.1: Binary XML Storage, without XML Schema
A simple XML file, with no associated XML schema, can be stored as binary XML in Oracle.
XML can be entered into the database as:
- Table of XMLType
- Table with column of XMLType
Option A: Table of XMLType, no XSD
SQL> -- Binary XML storage (default) SQL> -- Option A: Table of XMLType, no XSD SQL> create table emp_xmltab_binary_noxsd of XMLType; Table created. SQL> insert into emp_xmltab_binary_noxsd 2 values 3 (XMLType( 4 '<?xml version="1.0" encoding="UTF-8"?> 5 <Employee xmlns:xs="http://www.w3.org/2001/XMLSchema"> 6 <r deptno="10"> 7 <empno>7839</empno> 8 <ename>KING</ename> 9 <sal>5000</sal> 10 </r> 11 <r deptno="30"> 12 <empno>7698</empno> 13 <ename>BLAKE</ename> 14 <sal>2850</sal> 15 </r> 16 <r deptno="10"> 17 <empno>7782</empno> 18 <ename>CLARK</ename> 19 <sal>2450</sal> 20 </r> 21 <r deptno="20"> 22 <empno>7566</empno> 23 <ename>JONES</ename> 24 <sal>2975</sal> 25 </r> 26 <r deptno="20"> 27 <empno>7788</empno> 28 <ename>SCOTT</ename> 29 <sal>3000</sal> 30 </r> 31 <r deptno="20"> 32 <empno>7902</empno> 33 <ename>FORD</ename> 34 <sal>3000</sal> 35 </r> 36 </Employee>')); 1 row created.
Option B: Table with column of XMLType, no XSD
SQL> -- Binary XML storage (default) SQL> -- Option B: Table with column of XMLType, no XSD SQL> create table emp_xmlcol_binary_noxsd 2 ( id number 3 , xmldata XMLType 4 ); Table created. SQL> insert into emp_xmlcol_binary_noxsd 2 (id, xmldata) 3 values 4 (1, XMLType( 5 '<?xml version="1.0" encoding="UTF-8"?> 6 <Employee xmlns:xs="http://www.w3.org/2001/XMLSchema"> 7 <r deptno="10"> 8 <empno>7839</empno> 9 <ename>KING</ename> 10 <sal>5000</sal> 11 </r> 12 <r deptno="30"> 13 <empno>7698</empno> 14 <ename>BLAKE</ename> 15 <sal>2850</sal> 16 </r> 17 <r deptno="10"> 18 <empno>7782</empno> 19 <ename>CLARK</ename> 20 <sal>2450</sal> 21 </r> 22 <r deptno="20"> 23 <empno>7566</empno> 24 <ename>JONES</ename> 25 <sal>2975</sal> 26 </r> 27 <r deptno="20"> 28 <empno>7788</empno> 29 <ename>SCOTT</ename> 30 <sal>3000</sal> 31 </r> 32 <r deptno="20"> 33 <empno>7902</empno> 34 <ename>FORD</ename> 35 <sal>3000</sal> 36 </r> 37 </Employee>')); 1 row created.
Example 1.2: Binary XML Storage, with XML Schema
This example shows an XML file with an associated XML schema stored as binary XML in Oracle.
We’ll use the same XML data as in Example 1.1 (empdata.xml), with an associated XSD Employee.xsd.
Employee.xsd:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
attributeFormDefault="unqualified">
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="r" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="empno" type="xs:int"></xs:element>
<xs:element name="ename" type="xs:string"></xs:element>
<xs:element name="sal" type="xs:int"></xs:element>
</xs:sequence>
<xs:attribute name="deptno" type="xs:int"></xs:attribute>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="xmlns" type="xs:string"></xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>
A summary of the steps to set up binary XML storage with XML Schema:
- Create directory alias to access the files on disk
- Grant read, write on directory alias to the required user(s)
- Create folder structure for Oracle XML repository
- Load the XML Schema into Oracle XML repository as a resource
- Register the XML Schema for use with binary XML
- Create table of XMLType as binary XML, associated with the registered XML Schema
- Insert data into table of XMLType as binary XML
Stepwise execution follows.
The scripts requires the user to have a few special DBA privileges in addition to the usual CREATE privileges:
- XDBADMIN role
- CREATE ANY DIRECTORY privilege
1. Create directory alias to access the files on disk
For this demo we have our XML Schema (XSD) placed in the path D:\XMLStore – a staging area on the Windows file system from which the XSD will be loaded into the XML repository.
![Oracle XML DB - XML Files on Disk[12]](https://www.oratable.com/wp-content/uploads/2017/06/Oracle-XML-DB-XML-Files-on-Disk12_thumb.png "Oracle XML DB - XML Files on Disk[12]")
To load and register the XSD, the first step is to create a directory alias. Calling it xml_dir:
SQL> -- Create directory alias to access the files on disk SQL> create directory xml_dir as 'D:\XMLStore';
2. Grant read, write on directory alias to the required user(s)
SQL> -- Grant read, write on directory alias SQL> -- to the required user(s) SQL> grant read, write 2 on directory xml_dir 3 to hr; Grant succeeded.
3. Create folder structure for Oracle XML repository
We will create a simple Oracle XML repository folder structure like this:
/public/xsd — to hold XML Schema files
/public/xml — to hold XML data files
The folder can be created using the function dbms_xdb.createfolder. Each sub-folder in the hierarchy needs to be created using successive calls to dbms_xdb.createfolder. To build the above structure, three calls will be needed:
Call 1 to create /public/.
Call 2 to create xsd/ under /public/.
Call 3 to create xml/ under /public/.
A largeish folder hierarchy is best created by putting each folder in a collection type variable and calling dbms_xdb.createfolder in a loop for each element of the collection.
This approach adapted from the XDB folder creation script at oracle-developer.net:
SQL> -- Create folder structure for Oracle XML repository SQL> declare 2 type folder_ntt is table of varchar2(1024); 3 nt_folders folder_ntt := folder_ntt( 4 '/public/', 5 '/public/xsd/', 6 '/public/xml/' 7 ); 8 v_return boolean; 9 folder_exists exception; 10 pragma exception_init(folder_exists, -31003); 11 begin 12 for i in nt_folders.first .. nt_folders.last loop 13 begin 14 v_return := dbms_xdb.createfolder(nt_folders(i)); 15 exception 16 when folder_exists then 17 null; 18 end; 19 end loop; 20 commit; 21 end; 22 / PL/SQL procedure successfully completed.
After the Oracle XML repository is created, the folder structure can be accessed using a web browser via these methods:
(a) http://[hostname_or_ip]:8080/
(b) WebDAV in Windows Explorer (XP Professional)
Using method (a) in a Windows 7 Professional environment, to verify the folder structure of the just-created Oracle XML repository — http://localhost:8080/public/xsd is accessible and currently empty:
![Oracle XDB Folder Contents[3]](https://www.oratable.com/wp-content/uploads/2017/06/Oracle-XDB-Folder-Contents3_thumb.png "Oracle XDB Folder Contents[3]")
The parallel sub-folder http://localhost:8080/public/xml is also accessible and currently empty:
[If prompted for user and password when accessing the folders via web browser, enter the database user and password with which the folders were created.]
4. Load the XML Schema into Oracle XML repository as a resource
After the Oracle XML repository has been created, the XML schema can be loaded as a "resource" from xml_dir staging area (created in step 1) into the repository.
The function call to use here is dbms_xdb.createresource.
SQL> -- Load the XSD into the Oracle XML repository as resource
SQL> declare
2 xmldoc XMLType;
3 resource_flag boolean;
4 begin
5 xmldoc:=(XMLType(bfilename('XML_DIR'
6 , 'Employee.xsd')
7 , nls_charset_id('AL32UTF8')));
8
9 resource_flag := dbms_xdb.createresource (
10 abspath => '/public/xsd/Employee.xsd'
11 , data => xmldoc);
12
13 if (resource_flag) then
14 dbms_output.put_line('Resource created.');
15 else
16 dbms_output.put_line('Error in creating resource');
17 end if;
18 commit;
19 end;
20 /
Resource created.
PL/SQL procedure successfully completed.
Remember: as with the utl_file example, the directory alias must be referenced in uppercase in Oracle even though it was created in lowercase.
Let’s check through the browser if the XSD has been loaded in the repository.
Sure enough, it has.
Note: You could use XDBUriType to retrieve resources from XML DB Repository.
SQL> select xdbURIType ('/public/xsd/Employee.xsd').getClob(
2 from dual;
XDBURITYPE('/PUBLIC/XSD/EMPLOYEE.XSD').GETCLOB()
------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" eleme
ntFormDefault="qualified" attributeFormDefault="unqualified"
>
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="r" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="empno" type="xs:int"/>
<xs:element name="ename" type="xs:string"/>
<xs:element name="sal" type="xs:int"/>
</xs:sequence>
<xs:attribute name="deptno" type="xs:int"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="xmlns" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:schema>
5. Register the XML Schema for use with binary XML
At this point, the XML Schema has been loaded into the XML repository. The next step is to register the XML schema for use with binary XML. To do this, we call dbms_xmlschema.registerschema with the appropriate options.
SQL> -- Register the XSD for binary usage
SQL> begin
2 dbms_xmlschema.registerschema(
3 schemaurl => 'http://localhost:8080/public/xsd/Employee.xsd',
4 schemadoc => xdbURIType('/public/xsd/Employee.xsd').getClob(),
5 local => false,
6 gentypes => false, -- no object types
7 genbean => false, -- no java beans
8 gentables => false, -- no object tables
9 force => false,
10 options => dbms_xmlschema.register_binaryxml,
11 owner => user
12 );
13 end;
14 /
PL/SQL procedure successfully completed.
After the schema is registered, it should be queryable from user_xml_schemas:
SQL> -- Verify after registering schema
SQL> select schema_url
2 , schema
3 from user_xml_schemas
4 where schema_url like '%Employee.xsd';
SCHEMA_URL
----------------------------------------------------------------
SCHEMA
----------------------------------------------------------------
http://localhost:8080/public/xsd/Employee.xsd
<?xml version="1.0" encoding="WINDOWS-1252"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:ora
xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified"
attributeFormDefault="unqualified" oraxdb:flags="2122033" oraxd
b:schemaURL="http://localhost:8080/public/xsd/Employee.xsd" orax
db:schemaOwner="HR" oraxdb:numProps="7" xmlns:csx="http://xmlns.
oracle.com/2004/CSX">
<xs:element name="Employee" xmlns:csx="http://xmlns.oracle.com
/2004/CSX" csx:propertyID="3148" oraxdb:global="true" oraxdb:mem
Type="258">
<xs:complexType>
<xs:annotation>
<xs:appinfo>
<csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSche
ma" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">
<csx:kid csx:propertyID="3147" kidNum="1"/>
<csx:kid csx:propertyID="3142" kidNum="2"/>
</csx:kidList>
</xs:appinfo>
</xs:annotation>
<xs:sequence>
<xs:element name="r" maxOccurs="unbounded" csx:propertyI
D="3147" oraxdb:global="false" oraxdb:memType="258" oraxdb:MemIn
line="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false">
<xs:complexType>
<xs:annotation>
<xs:appinfo>
<csx:kidList xmlns:xsd="http://www.w3.org/2001/X
MLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="tr
ue">
<csx:kid csx:propertyID="3144" kidNum="1"/>
<csx:kid csx:propertyID="3145" kidNum="2"/>
<csx:kid csx:propertyID="3146" kidNum="3"/>
<csx:kid csx:propertyID="3143" kidNum="4"/>
</csx:kidList>
</xs:appinfo>
</xs:annotation>
<xs:sequence>
<xs:element name="empno" type="xs:int" csx:propert
yID="3144" oraxdb:global="false" csx:encodingType="int" oraxdb:m
emType="3" oraxdb:memByteLength="4" oraxdb:MemInline="true" orax
db:SQLInline="true" oraxdb:JavaInline="true"/>
<xs:element name="ename" type="xs:string" csx:prop
ertyID="3145" oraxdb:global="false" csx:encodingType="string" or
axdb:memType="1" oraxdb:MemInline="true" oraxdb:SQLInline="true"
oraxdb:JavaInline="true"/>
<xs:element name="sal" type="xs:int" csx:propertyI
D="3146" oraxdb:global="false" csx:encodingType="int" oraxdb:mem
Type="3" oraxdb:memByteLength="4" oraxdb:MemInline="true" oraxdb
:SQLInline="true" oraxdb:JavaInline="true"/>
</xs:sequence>
<xs:attribute name="deptno" type="xs:int" csx:proper
tyID="3143" oraxdb:global="false" oraxdb:memType="3" oraxdb:memB
yteLength="4"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="xmlns" type="xs:string" csx:propertyID
="3142" oraxdb:global="false" oraxdb:memType="1"/>
</xs:complexType>
</xs:element>
</xs:schema>
6. Create table of XMLType as binary XML, associating with it the registered XML Schema
The XML Schema has been loaded and registered. All the prerequisite setup is now in place for creating a table as binary XML with XML schema.
For binary XML table with XSD, the CREATE TABLE statement includes additional information:
(i) the registered XML schema
(ii) valid globally defined element in the registered XML schema
SQL> -- Binary XML storage (default) SQL> -- Table of XMLType, with XSD SQL> create table emp_xmltab_binary_xsd of XMLType 2 XMLType store as binary xml 3 XMLSchema -- Registered XML Schema 4 "http://localhost:8080/public/xsd/Employee.xsd" 5 element -- globally defined element in registered XML schema 6 "Employee"; Table created.
The table is successfully created. The table structure can be verified by describing the table or using dbms_metadata.get_ddl:
SQL> desc emp_xmltab_binary_xsd
Name Null? Type
------------------------------ -------- ---------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/public/xsd/Employee.xsd" E
lement "Employee") STORAGE BINARY
SQL> select dbms_metadata.get_ddl('TABLE'
2 , 'EMP_XMLTAB_BINARY_XSD')
3 from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP_XMLTAB_BINARY_XSD')
--------------------------------------------------------------
CREATE TABLE "HR"."EMP_XMLTAB_BINARY_XSD" OF XMLTYPE
XMLTYPE STORE AS SECUREFILE BINARY XML (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
CACHE READS LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_F
LASH_CACHE DEFAULT))
XMLSCHEMA "http://localhost:8080/public/xsd/Employee.xsd" E
LEMENT "Employee" ID 3086 DISALLOW NONSCHEMA
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2
147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA
ULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Data can now be loaded into the new table. This can be done in a variety of ways:
Method 1: Load XML data through a file
Place the XML file (empdata.xml) in the xml_dir path, and use the file in the insert statement.
SQL> -- Method 1: Load XML data through a file
SQL> insert into emp_xmltab_binary_xsd
2 values
3 (XMLType(bfilename('XML_DIR','empdata.xml')
4 ,nls_charset_id('AL32UTF8')));
1 row created.
Method 2: Directly specify the XML data in the INSERT statement
SQL> -- Method 2: Directly specify the XML data SQL> -- in the INSERT statement SQL> insert into emp_xmltab_binary_xsd 2 values 3 (XMLType( 4 '<?xml version="1.0" encoding="UTF-8"?> 5 <Employee xmlns:xs="http://www.w3.org/2001/XMLSchema" 6 xmlns:xdb="http://xmlns.oracle.com/xdb"> 7 <!--XML generated from emp table--> 8 <r deptno="10"> 9 <empno>7839</empno> 10 <ename>KING</ename> 11 <sal>5000</sal> 12 </r> 13 <r deptno="30"> 14 <empno>7698</empno> 15 <ename>BLAKE</ename> 16 <sal>2850</sal> 17 </r> 18 <r deptno="10"> 19 <empno>7782</empno> 20 <ename>CLARK</ename> 21 <sal>2450</sal> 22 </r> 23 <r deptno="20"> 24 <empno>7566</empno> 25 <ename>JONES</ename> 26 <sal>2975</sal> 27 </r> 28 <r deptno="20"> 29 <empno>7788</empno> 30 <ename>SCOTT</ename> 31 <sal>3000</sal> 32 </r> 33 <r deptno="20"> 34 <empno>7902</empno> 35 <ename>FORD</ename> 36 <sal>3000</sal> 37 </r> 38 </Employee>')); 1 row created.
XML Binary Storage: Schema Validations during INSERT
In the scenario of binary XML storage with XML Schema, the XML data being inserted must conform to the associated XML schema.
Let’s see what happens when we attempt to insert into emp_xmltab_binary_xsd, some XML which violates the XML Schema Employee.xsd:
SQL> -- Binary XML Storage with XSD
SQL> -- Insert non-conforming XML: missing particle
SQL> insert into emp_xmltab_binary_xsd
2 values
3 (XMLType(
4 '<?xml version="1.0" encoding="UTF-8"?>
5 <Employee xmlns:xs="http://www.w3.org/2001/XMLSchema"
6 xmlns:xdb="http://xmlns.oracle.com/xdb">
7 <r deptno="10">
8 <employeeno>7839</employeeno>
9 <ename>KING</ename>
10 <sal>5000</sal>
11 </r>
12 </Employee>'));
insert into emp_xmltab_binary_xsd
*
ERROR at line 1:
ORA-31061: XDB error: XML event error
ORA-19202: Error occurred in XML processing
LSX-00213: only 0 occurrences of particle "empno",
minimum is 1
SQL> -- Binary XML Storage with XSD
SQL> -- Insert non-conforming XML: undefined element
SQL> insert into emp_xmltab_binary_xsd
2 values
3 (XMLType(
4 '<?xml version="1.0" encoding="UTF-8"?>
5 <EmpWrong xmlns:xs="http://www.w3.org/2001/XMLSchema"
6 xmlns:xdb="http://xmlns.oracle.com/xdb">
7 <r deptno="10">
8 <empno>7839</empno>
9 <ename>KING</ename>
10 <sal>5000</sal>
11 </r>
12 </EmpWrong>'));
insert into emp_xmltab_binary_xsd
*
ERROR at line 1:
ORA-31061: XDB error: XML event error
ORA-19202: Error occurred in XML processing
LSX-00021: undefined element "EmpWrong"
The same data is happily accepted by the table with binary XML storage without XML Schema.
SQL> -- Binary XML Storage without XSD SQL> -- No conformance check during insert SQL> insert into emp_xmltab_binary_noxsd 2 values 3 (XMLType( 4 '<?xml version="1.0" encoding="UTF-8"?> 5 <EmpWrong xmlns:xs="http://www.w3.org/2001/XMLSchema" 6 xmlns:xdb="http://xmlns.oracle.com/xdb"> 7 <r deptno="10"> 8 <empno>7839</empno> 9 <ename>KING</ename> 10 <sal>5000</sal> 11 </r> 12 </EmpWrong>')); 1 row created.
Summary
This article demonstrates how to use binary XML storage in Oracle XML DB
- without XML schema
- with XML schema
When using binary XML storage with XML schema (XSD), the article explains the series of steps needed to configure and register the XSD in Oracle before loading XML data conforming to that XSD. Finally, it shows methods of loading XML data into binary XML storage, with examples of valid as well as exception scenarios.
For download: binary XML storage scripts, XSD and XML.
For Further Reading
- XML DB Developer’s Guide (Oracle 12.2)
- Using Oracle XML DB to automatically shred Windows documents
- How to create XMLType table for binary XML usage
