After the post on models for storing XML data in Oracle and examples of binary XML storage, here’s a detailed look at the rigorous and performant structured XML storage or object-relational storage.
Object-relational storage of XML data is based on "shredding" the XML content into a set of SQL objects. During XML Schema registration, the required SQL types are generated and mapped to XML Schema data types.
This article showcases object-relational storage using the same XML data (empdata.xml) and a copy of the XML Schema (EmployeeOR.xsd) as used for the binary XML storage example.
Object-Relational Storage — Stepwise Execution
A summary of the steps to set up object-relational XML storage:
- 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 object-relational use
- Create table of XMLType as object-relational, associated with the registered XML Schema
- Insert data into table of XMLType
Steps 1-3 for setting up the Oracle XML repository need not be repeated if completed already, as we have done for binary XML usage. Refer to the same for object-relational storage as well (link). The steps are included in the script available for download in the Summary section.
Stepwise execution for 4-7 follows.
4. Load the XML Schema into Oracle XML repository as a resource
After the Oracle XML repository has been created, the XML schema EmployeeOR.xsd can be loaded as a "resource" from xml_dir staging area into the repository, using dbms_xdb.createresource.
SQL> declare
2 xmldoc XMLType;
3 resource_flag boolean;
4 begin
5 xmldoc:=(XMLType(bfilename('XML_DIR'
6 , 'EmployeeOR.xsd')
7 , nls_charset_id('AL32UTF8')));
8
9 resource_flag := dbms_xdb.createresource (
10 abspath => '/public/xsd/EmployeeOR.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.
Once the XSD is loaded, we should be able to access it through the web browser.
Yes, the XSD can be found in the XML repository. Its contents can be verified using xdbURIType in SQL.
SQL> -- XDBUriType to retrieve resources from XML DB Repository
SQL> select xdbURIType ('/public/xsd/EmployeeOR.xsd').getClob()
2 from dual;
XDBURITYPE('/PUBLIC/XSD/EMPLOYEEOR.XSD').GETCLOB()
----------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFo
rmDefault="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 object-relational use
At this point, the XML schema EmployeeOR.xsd has been loaded into Oracle XML repository and is ready for the registration process.
We call dbms_xmlschema.registerschema to register the schema for object-relational usage, with two key differences from binary XML usage:
a. gentypes, gentables is set to true
b. options => dbms_xmlschema.register_binaryxml is removed from the input options
SQL> -- Register the XSD for object-relational use
SQL> -- Set gentypes, gentables to true
SQL> begin
2 dbms_xmlschema.registerschema(
3 schemaurl => 'http://localhost:8080/public/xsd/EmployeeOR.xsd',
4 schemadoc => xdbURIType('/public/xsd/EmployeeOR.xsd').getClob(),
5 local => false,
6 gentypes => true, -- generate object types
7 genbean => false, -- no java beans
8 gentables => true, -- generate object tables
9 force => false,
10 owner => user
11 );
12 end;
13 /
PL/SQL procedure successfully completed.
When you verify the newly registered object-relational schema EmployeeOR.xsd via user_xml_schemas, you will see a few things markedly different from the binary XML schema Employee.xsd.
SQL> -- Verify after registering schema
SQL> select schema_url
2 , schema
3 from user_xml_schemas
4 where schema_url like '%EmployeeOR.xsd';
SCHEMA_URL
------------------------------------------------------------
SCHEMA
------------------------------------------------------------
http://localhost:8080/public/xsd/EmployeeOR.xsd
<?xml version="1.0" encoding="WINDOWS-1252"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" eleme
ntFormDefault="qualified" attributeFormDefault="unqualified"
xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:storeVarr
ayAsTable="true" oraxdb:flags="2105655" oraxdb:schemaURL="ht
tp://localhost:8080/public/xsd/EmployeeOR.xsd" oraxdb:schema
Owner="HR" oraxdb:numProps="7">
<xs:element name="Employee" oraxdb:propNumber="3134" oraxd
b:global="true" oraxdb:SQLName="Employee" oraxdb:SQLType="Em
ployee349_T" oraxdb:SQLSchema="HR" oraxdb:memType="258" orax
db:defaultTable="Employee352_TAB" oraxdb:defaultTableSchema=
"HR">
<xs:complexType oraxdb:SQLType="Employee349_T" oraxdb:SQ
LSchema="HR">
<xs:sequence>
<xs:element name="r" maxOccurs="unbounded" oraxdb:pr
opNumber="3133" oraxdb:global="false" oraxdb:SQLName="r" ora
xdb:SQLType="r350_T" oraxdb:SQLSchema="HR" oraxdb:memType="2
58" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:
JavaInline="false" oraxdb:SQLCollType="r351_COLL" oraxdb:SQL
CollSchema="HR">
<xs:complexType oraxdb:SQLType="r350_T" oraxdb:SQL
Schema="HR">
<xs:sequence>
<xs:element name="empno" type="xs:int" oraxdb:
propNumber="3130" oraxdb:global="false" oraxdb:SQLName="empn
o" oraxdb:SQLType="NUMBER" oraxdb:memType="3" oraxdb:memByte
Length="4" oraxdb:MemInline="true" oraxdb:SQLInline="true" o
raxdb:JavaInline="true"/>
<xs:element name="ename" type="xs:string" orax
db:propNumber="3131" oraxdb:global="false" oraxdb:SQLName="e
name" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:Me
mInline="true" oraxdb:SQLInline="true" oraxdb:JavaInline="tr
ue"/>
<xs:element name="sal" type="xs:int" oraxdb:pr
opNumber="3132" oraxdb:global="false" oraxdb:SQLName="sal" o
raxdb:SQLType="NUMBER" oraxdb:memType="3" oraxdb:memByteLeng
th="4" oraxdb:MemInline="true" oraxdb:SQLInline="true" oraxd
b:JavaInline="true"/>
</xs:sequence>
<xs:attribute name="deptno" type="xs:int" oraxdb
:propNumber="3129" oraxdb:global="false" oraxdb:SQLName="dep
tno" oraxdb:SQLType="NUMBER" oraxdb:memType="3" oraxdb:memBy
teLength="4"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="xmlns" type="xs:string" oraxdb:pro
pNumber="3128" oraxdb:global="false" oraxdb:SQLName="xmlns"
oraxdb:SQLType="VARCHAR2" oraxdb:memType="1"/>
</xs:complexType>
</xs:element>
</xs:schema>
Notice the SQL object details within the schema contents:
oraxdb:SQLType="Employee349_T"
oraxdb:defaultTable="Employee352_TAB"
etc.
Oracle silently created these SQL types for object-relational storage during XML Schema registration, giving them names using an internal algorithm.
A SQL type definition is created for each complexType in the XML Schema, and each element/attribute in the complexType becomes a SQL attribute in the corresponding SQL type. A varray type is generated for each element, which can occur multiple times.
When an insert is made to an object-relational XMLType table, XML content compliant with the XML Schema is decomposed and stored in SQL objects without any loss of information. Object-relational XML storage helps Oracle manage database space efficiently as well as speed up queries and updates to XML.
More details of the object-relational structure can be viewed by querying user_nested_tables and drilling down from there.
SQL> -- Check the components created SQL> -- for structured XML storage SQL> select table_name 2 , parent_table_name 3 , parent_table_column 4 from user_nested_tables; TABLE_NAME PARENT_TABLE_NAME ------------------------------ ---------------------- PARENT_TABLE_COLUMN ---------------------------------------- SYS_NTZBHJSPebQMuAyn6p2dcdGQ== Employee352_TAB "XMLDATA"."r"
SQL> -- Describe the table found via user_nested_tables SQL> desc "SYS_NTZBHJSPebQMuAyn6p2dcdGQ==" Name Null? Type ----------------------------- -------- -------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T deptno NUMBER(10) empno NUMBER(10) ename VARCHAR2(4000 CHAR) sal NUMBER(10)
SQL> -- Describe the parent table found via user_nested_tables SQL> desc "Employee352_TAB" Name Null? Type ----------------------------- -------- -------------------- TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/public/xsd/EmployeeOR.xsd" Element "Employee") STORAGE Object-relational TYPE "Employee349_T"
SQL> -- Describe the object type listed SQL> -- in the XMLType table definition SQL> desc "Employee349_T" Name Null? Type ----------------------------- -------- -------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T xmlns VARCHAR2(4000 CHAR) r r351_COLL
6. Create table of XMLType as object-relational, associated with the registered XML Schema
The XML Schema for structured storage has been loaded and registered, and the required SQL types needed for persistence have been auto-generated. We are all set to create an XMLType table for structured storage.
For structured storage, the CREATE TABLE statement needs to include this additional information:
(i) the registered XML schema for object-relational storage
(ii) valid globally defined element in the registered XML schema
Let’s try an error scenario first: what happens if we specify a schema that has been registered all right, but *not* for object-relational storage?
SQL> -- Structured XML storage, error scenario
SQL> -- Registered XML schema with gentypes, gentables false
SQL> create table emp_xmltab_structured of XMLType
2 XMLType store as object relational
3 XMLSchema -- registered XML Schema
4 "http://localhost:8080/public/xsd/Employee.xsd",
5 element -- globally defined element in registered XML schema
6 "Employee";
"http://localhost:8080/public/xsd/Employee.xsd",
*
ERROR at line 4:
ORA-19002: Missing XMLSchema URL
The error message isn’t too enlightening but we do know why this has happened. Employee.xsd had been registered for binary storage (gentypes, gentables were FALSE), not for object-relational storage.
Let’s try again, with the right schema this time:
SQL> -- Structured XML storage SQL> -- Create table of XMLType, as object-relational SQL> create table emp_xmltab_structured of XMLType 2 XMLType store as object relational 3 XMLSchema -- registered XML Schema 4 "http://localhost:8080/public/xsd/EmployeeOR.xsd" 5 element -- globally defined element in registered XML schema 6 "Employee"; Table created.
That worked! Describe the table and see what it says:
SQL> desc emp_xmltab_structured
Name Null? Type
------------------------------- -------- ----------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/public/xsd/EmployeeOR.xsd"
Element "Employee") STORAGE Object-relational TYPE "Employee349_T"
So the table description references the XSD, its root element, mentions its storage model (bbject-relational) and the SQL Type that we had drilled down to earlier.
We will attempt loading data into the new XMLType table in two ways:
(1) loading XML data through a file
(2) loading XML directly via the INSERT statement
Method 1: Load XML data through a file
Place an XSD-conforming XML file (empdata.xml) in the xml_dir path and include it in the insert statement.
SQL> -- Method 1: Loading XML data through a file
SQL> insert into emp_xmltab_structured
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 specifying the XML data SQL> -- in the INSERT statement SQL> insert into emp_xmltab_structured 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 <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.
Structured XML Storage: Validations during INSERT
Structured XML storage is mandatorily associated with an XML schema, unlike the flexible binary XML which can be set up to work without an XML schema. As such, structured XML storage is consistently strict in the XML content it allows during inserts.
The XML content being inserted must conform to the associated XML Schema; non-conformance leads to errors.
Are the errors for non-conforming XML inserts in structured storage, the same as those in case of binary XML (with XSD) storage?
We tested with identical XML inserts on identical XML schemas, and found the error messages differ when storage models differ.
SQL> -- Structured XML Storage
SQL> -- Insert non-conforming XML: mising particle
SQL> insert into emp_xmltab_structured
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_structured
*
ERROR at line 1:
ORA-30937: No schema definition for 'employeeno' (namespace
'##local') in parent '/Employee/r[1]'
With the same test case applied on binary XML storage, the error is:
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
Another example of non-conforming XML – insertion of an undefined element:
SQL> -- Structured XML Storage SQL> -- Insert non-conforming XML: undefined element SQL> insert into emp_xmltab_structured 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>')); (XMLType( * ERROR at line 3: ORA-30937: No schema definition for 'EmpWrong' (namespace '') in parent '/'
With the same test case applied on binary XML storage, the error is:
ORA-31061: XDB error: XML event error ORA-19202: Error occurred in XML processing LSX-00021: undefined element "EmpWrong"
Caution: Do not access the SQL objects directly
The SQL types generated for Oracle XML object-relational storage are not meant for direct access. Applications are expected to access them only via the XML abstraction.
Summary
This article demonstrates how to use structured (object-relational) XML storage in Oracle XML DB. It drills down to the SQL types created in the database for supporting structured XML storage., abd shares approaches for loading XML data into the structured XMLType table, with examples of valid as well as exception scenarios.
For download: structured XML storage scripts, XSD and XML.
{ 1 comment… read it below or add one }
The best resource I have found for object-relational XML storage in Oracle. Thank you for explaining it so well.