I am attempting to import an xml file into the repository and have it shred into the object-relational tables. I registered a schema and it created object-relational xml_tables, triggers, and types. The full xml file is > 80MB. XDB created all the fields as VARCHAR2(4000).
error code when importing the full XML file. This is what I need to ultimately fix.
DECLARE
res BOOLEAN;
BEGIN
res := DBMS_XDB.createResource('/home/pharma/drugbank.xml',
bfilename('XMLPHARMA', 'drugbank.xml'),
nls_charset_id('AL32UTF8'));
END;
/
COMMIT;
XML file encounters errors on the import.
An error was encountered performing the requested operation
ORA-30951: Element or attribute at Xpath references exceeds maximum length
ORA-06512 at "XDB.DBMS_XDB", line 315
ORA-06512 at line 4
30951.00000 - "Element or attribute at Xpath %x exceeds maximum length"
*Cause: An attempt was made to insert a node of length exceeding the maximum length (specified by the maxLength facet) into an XML document.
*Action: Do not attempt to add a node exceeding the maximum length to XML documents.
Vendor code 30951Error at Line:18
I would guess that some of the fields have max lengths > 4000. I plan to annotate the schema with SQLType="CLOB", but need to know which fields to change. I was able to copy the xml file and reduce that xml file to one record, which imported into the tables. My plan was to write a view over the bfile to get the lengths of certain fields, annotate the .xsd with CLOBS where needed, and then import again. In order to do that, I have a 1 record file imported into a table and as a bfile.
Here is my code selecting length from a generated object-relational table of only one record.
CREATE OR REPLACE VIEW pharma.drugs_vw AS
SELECT d.*
FROM drugs, XMLTABLE
('/drugs' PASSING OBJECT_VALUE COLUMNS
drugbank_id VARCHAR2(20) PATH 'drug/drugbank-id',
name VARCHAR2(50) PATH 'drug/name',
description VARCHAR2(4000) PATH 'drug/description'
) d
/
select max(length(drugbank_id)) as dbidlen,
max(length(name)) as nmlen,
max(length(description)) as desclen
from drugs_vw;
DBIDLEN NMLEN DESCLEN
--------------- --------------- ---------------
7 9 229
1 row selected.
Here is the code for the bfile. Same results, but using deprecated functions. I read the whitepaper, Oracle XML DB: Best practices to get optimal performance out of XML Queries. It says that extract(), extractvalue(), Table(XMLSequence()), and XMLType() are deprecated in 11gr2.
-- Note extractvalue is deprecated in 11gr2 replaced by W3C standard
-- XMLCast(XMLQuery())
-- TABLE(XMLSequence) is replaced by XMLTable
-- XMLType() is replaced by XMLParse()
SELECT max(length(extractvalue(column_value, '/drug/drugbank-id'))) dbidlen,
max(length(extractvalue(column_value, '/drug/name'))) nmlen,
max(length(extractvalue(column_value, '/drug/description'))) desclen
FROM TABLE(XMLSequence(XMLTYPE(bfilename('XMLPHARMA',
'db00001.xml'),nls_charset_id('AL32UTF8')).extract('/drugs/drug'))) d
WHERE ROWNUM <= 5;
DBIDLEN NMLEN DESCLEN
--------------- --------------- ---------------
7 9 229
Is this better code for getting the maximum length of xml fields from a bfile? Here is what I have so far. This works on a single drugbank-id.
SELECT max(length(drugbank_id)) AS dbidlen,
max(length(name)) AS nmlen,
max(length(description)) AS desclen
FROM (XMLTABLE('*'
PASSING (XMLType(bfilename('XMLPHARMA', 'db00001.xml'),nls_charset_id('AL32UTF8')))
COLUMNS
drugbank_id VARCHAR2(20) PATH 'drug/drugbank-id',
name VARCHAR2(50) PATH 'drug/name',
description VARCHAR2(4000) PATH 'drug/description'
)
);
I try to run it on the full file and get this error
Error report:
SQL Error: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.
The code to create schema and object-relational tables. This worked fine.
set serveroutput on
-- Create resource file for schema
DECLARE
res BOOLEAN;
BEGIN
res := DBMS_XDB.createResource('/home/pharma/drugbank.xsd',
bfilename('XMLPHARMA', 'drugbank.xsd'),
nls_charset_id('AL32UTF8'));
COMMIT;
END;
/
-- optional debugging of create types and tables if you want a trace
ALTER SESSION SET EVENTS = '31098 TRACE NAME CONTEXT FOREVER';
BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://localhost:8080/home/pharma/drugbank.xsd',
SCHEMADOC => bfilename('XMLPHARMA', 'drugbank.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => TRUE,
OWNER => 'PHARMA');
COMMIT;
END;
/
SQL> select * from v$version;
BANNER
---------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
The xml schema follows. Sorry about the length, but I think I might break something if I snipped it.
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
>
<!-- General type definitions -->
<xs:simpleType name="DecimalOrEmptyType">
<xs:union memberTypes="xs:decimal EmptyStringType"/>
</xs:simpleType>
<xs:simpleType name="EmptyStringType">
<xs:restriction base="xs:string">
<xs:enumeration value=""/>
</xs:restriction>
</xs:simpleType>
<!-- Element Definitions -->
<!-- Drug secondary accession number definition begins -->
<xs:element name="secondary-accession-numbers" xdb:defaultTable="SECONDARY_ACCESSION_NUMBERS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="secondary-accession-number" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug secondary accession number definition ends -->
<!-- Drug groups definition begins -->
<xs:element name="groups" xdb:defaultTable="GROUPS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="group">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="approved"/>
<xs:enumeration value="illicit"/>
<xs:enumeration value="experimental"/>
<xs:enumeration value="withdrawn"/>
<xs:enumeration value="nutraceutical"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug groups definition ends -->
<!-- Drug taxonomy definition begins -->
<xs:element name="substructure">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="class" type="xs:string" use="required"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="substructures" xdb:defaultTable="SUBSTRUCTURES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="substructure"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="taxonomy" xdb:defaultTable="TAXONOMY">
<xs:complexType>
<xs:sequence>
<xs:element name="kingdom" type="xs:string"/>
<xs:element ref="substructures"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug taxonomy definition ends -->
<!-- Drug brands definition begins -->
<xs:element name="brands" xdb:defaultTable="BRANDS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="brand" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug brands definition ends -->
<!-- Drug mixtures definition begins -->
<xs:element name="mixture">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="ingredients" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="mixtures" xdb:defaultTable="MIXTURES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="mixture"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug mixtures definition ends -->
<!-- Drug packagers definition begins -->
<xs:element name="packager">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="url" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="packagers" xdb:defaultTable="PACKAGERS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="packager"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug packagers definition ends -->
<!-- Drug manufacturers definition begins -->
<xs:element name="manufacturer">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="generic" type="xs:string" use="required"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="manufacturers" xdb:defaultTable="MANUFACTURERS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="manufacturer"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug manufactures definition ends -->
<!-- Drug pricing definition begins -->
<xs:element name="cost">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="currency" type="xs:string" use="required"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="price">
<xs:complexType>
<xs:sequence>
<xs:element name="description" type="xs:string"/>
<xs:element ref="cost"/>
<xs:element name="unit" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="prices" xdb:defaultTable="PRICES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug pricing definition ends -->
<!-- Drug categories definition begins -->
<xs:element name="categories" xdb:defaultTable="CATEGORIES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="category" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug categories definition ends -->
<!-- Drug affected orgainsms definition begins -->
<xs:element name="affected-organisms" xdb:defaultTable="AFFECTED_ORGANISMS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="affected-organism" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug affected organisms definition ends -->
<!-- Drug dosage definition begins -->
<xs:element name="dosage">
<xs:complexType>
<xs:sequence>
<xs:element name="form" type="xs:string"/>
<xs:element name="route" type="xs:string"/>
<xs:element name="strength" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="dosages" xdb:defaultTable="DOSAGES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="dosage"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug dosages definition ends -->
<!-- Drug ATC codes definition begins -->
<xs:element name="atc-codes" xdb:defaultTable="ATC_CODES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="atc-code" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug ATC codes definition ends -->
<!-- Drug AHFS codes definition begins -->
<xs:element name="ahfs-codes" xdb:defaultTable="AHFS_CODES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="ahfs-code" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug AHFS codes definition ends -->
<!-- Drug Patent definition begins -->
<xs:element name="patent">
<xs:complexType>
<xs:sequence>
<xs:element name="number" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
<xs:element name="approved" type="xs:string"/>
<xs:element name="expires" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="patents" xdb:defaultTable="PATENTS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="patent"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug patent definition ends -->
<!-- Drug food interactions definition begins -->
<xs:element name="food-interactions" xdb:defaultTable="FOOD_INTERACTIONS">
<xs:complexType>
<xs:sequence>
<xs:element name="food-interaction" type="xs:string" maxOccurs="unbounded" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug food interactions definition ends -->
<!-- Drug drug interactions definition begins -->
<xs:element name="drug-interaction">
<xs:complexType>
<xs:sequence>
<xs:element name="drug" type="xs:integer"/>
<xs:element name="description" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="drug-interactions" xdb:defaultTable="DRUG_INTERACTIONS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="drug-interaction"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug drug interactions definition ends -->
<!-- Drug protein sequences (biotech) definition begins -->
<xs:element name="protein-sequences" xdb:defaultTable="PROTEIN_SEQUENCES">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="protein-sequence" type="SequenceType"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug protein sequences (biotech) definition ends-->
<!-- Drug external links definition begins -->
<xs:element name="external-link">
<xs:complexType>
<xs:sequence>
<xs:element name="resource" type="xs:string"/>
<xs:element name="url" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="external-links" xdb:defaultTable="EXTERNAL_LINKS">
<xs:complexType>
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element ref="external-link"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug external links definition ends -->
<!-- Drug targets definition begins -->
<xs:element name="targets" xdb:defaultTable="TARGETS">
<xs:complexType>
<xs:sequence>
<xs:element name="target" type="TargetBondType" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug targets definition ends -->
<!-- Drug enzymes definition begins -->
<xs:element name="enzymes" xdb:defaultTable="ENZYMES">
<xs:complexType>
<xs:sequence>
<xs:element name="enzyme" type="BondType" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug enzmes definition ends -->
<!-- Drug transporters definition begins -->
<xs:element name="transporters" xdb:defaultTable="TRANSPORTERS">
<xs:complexType>
<xs:sequence>
<xs:element name="transporter" type="BondType" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug transporters definition ends -->
<!-- Drug carriers definition begins -->
<xs:element name="carriers" xdb:defaultTable="CARRIERS">
<xs:complexType>
<xs:sequence>
<xs:element name="carrier" type="BondType" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Drug carriers definition ends -->
<!-- Partner Pfams definition begins -->
<xs:element name="pfam">
<xs:complexType>
<xs:sequence>
<xs:element name="identifier" type="xs:string"/>
<xs:element name="name" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="pfams" xdb:defaultTable="PFAMS">
<xs:complexType>
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element ref="pfam"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Partner Pfams definition end -->
<!-- Partner GO Classification definition begins -->
<xs:element name="go-classifier">
<xs:complexType>
<xs:sequence>
<xs:element name="category" type="xs:string"/>
<xs:element name="description" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="go-classifiers" xdb:defaultTable="GO_CLASSIFIERS">
<xs:complexType>
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element ref="go-classifier"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- Partner GO Classification definition ends -->
<!-- Partner Essentiality definition begins -->
<xs:element name="essentiality">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Essential"/>
<xs:enumeration value="Non-Essential"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<!-- Partner Essentiality definition ends -->
<!-- Complex Type Definitions -->
<xs:complexType name="SequenceType">
<xs:sequence>
<xs:element name="header" type="xs:string"/>
<xs:element name="chain" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="PropertyType">
<xs:sequence>
<xs:element name="kind">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="logP"/>
<xs:enumeration value="logS"/>
<xs:enumeration value="logP/hydrophobicity"/>
<xs:enumeration value="Water Solubility"/>
<xs:enumeration value="caco2 Permeability"/>
<xs:enumeration value="pKa"/>
<xs:enumeration value="IUPAC Name"/>
<xs:enumeration value="Molecular Weight"/>
<xs:enumeration value="Monoisotopic Weight"/>
<xs:enumeration value="SMILES"/>
<xs:enumeration value="Molecular Formula"/>
<xs:enumeration value="InChI"/>
<xs:enumeration value="InChIKey"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="value" type="xs:string"/>
<xs:element name="source">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="JChem"/>
<xs:enumeration value="ALOGPS"/>
<xs:enumeration value=""/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="PropertiesType">
<xs:sequence>
<xs:element name="property" type="PropertyType" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SynonymsType">
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="synonym" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="IdentifiersType">
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="external-identifier">
<xs:complexType>
<xs:sequence>
<xs:element name="resource" type="xs:string"/>
<xs:element name="identifier" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="BondActionsType">
<xs:sequence maxOccurs="unbounded" minOccurs="0">
<xs:element name="action" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="BondType">
<xs:sequence>
<xs:element name="actions" type="BondActionsType"/>
<xs:element name="references" type="xs:string"/>
</xs:sequence>
<xs:attribute name="position" type="xs:integer" use="optional"/>
<xs:attribute name="partner" type="xs:integer"/>
</xs:complexType>
<xs:complexType name="TargetBondType">
<xs:complexContent>
<xs:extension base="BondType">
<xs:sequence>
<xs:element name="known-action">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="yes"/>
<xs:enumeration value="no"/>
<xs:enumeration value="unknown"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="PartnerType">
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="general-function" type="xs:string"/>
<xs:element name="specific-function" type="xs:string"/>
<xs:element name="gene-name" type="xs:string"/>
<xs:element name="locus" type="xs:string"/>
<xs:element name="reaction" type="xs:string"/>
<xs:element name="signals" type="xs:string"/>
<xs:element name="cellular-location" type="xs:string"/>
<xs:element name="transmembrane-regions" type="xs:string"/>
<xs:element name="theoretical-pi" type="DecimalOrEmptyType"/>
<xs:element name="molecular-weight" type="DecimalOrEmptyType"/>
<xs:element name="chromosome" type="xs:string"/>
<xs:element ref="essentiality"/>
<xs:element name="references" type="xs:string"/>
<xs:element name="external-identifiers" type="IdentifiersType"/>
<xs:element name="synonyms" type="SynonymsType"/>
<xs:element name="protein-sequence" type="SequenceType" minOccurs="0"/>
<xs:element name="gene-sequence" type="SequenceType" minOccurs="0"/>
<xs:element ref="pfams"/>
<xs:element ref="go-classifiers"/>
</xs:sequence>
<xs:attribute name="id" type="xs:integer" use="required"/>
</xs:complexType>
<xs:complexType name="DrugType">
<xs:sequence>
<xs:element name="drugbank-id" type="xs:string"/>
<xs:element name="name" type="xs:string"/>
<xs:element name="description" type="xs:string"/>
<xs:element name="cas-number" type="xs:string"/>
<xs:element name="general-references" type="xs:string"/>
<xs:element name="synthesis-reference" type="xs:string"/>
<xs:element name="indication" type="xs:string"/>
<xs:element name="pharmacology" type="xs:string"/>
<xs:element name="mechanism-of-action" type="xs:string"/>
<xs:element name="toxicity" type="xs:string"/>
<xs:element name="biotransformation" type="xs:string"/>
<xs:element name="absorption" type="xs:string"/>
<xs:element name="half-life" type="xs:string"/>
<xs:element name="protein-binding" type="xs:string"/>
<xs:element name="route-of-elimination" type="xs:string"/>
<xs:element name="volume-of-distribution" type="xs:string"/>
<xs:element name="clearance" type="xs:string"/>
<xs:element ref="secondary-accession-numbers"/>
<xs:element ref="groups"/>
<xs:element ref="taxonomy"/>
<xs:element name="synonyms" type="SynonymsType"/>
<xs:element ref="brands"/>
<xs:element ref="mixtures"/>
<xs:element ref="packagers"/>
<xs:element ref="manufacturers"/>
<xs:element ref="prices"/>
<xs:element ref="categories"/>
<xs:element ref="affected-organisms"/>
<xs:element ref="dosages"/>
<xs:element ref="atc-codes"/>
<xs:element ref="ahfs-codes"/>
<xs:element ref="patents"/>
<xs:element ref="food-interactions"/>
<xs:element ref="drug-interactions"/>
<xs:element ref="protein-sequences" minOccurs="0"/><!-- Only present for biotech drugs -->
<xs:element name="calculated-properties" type="PropertiesType" minOccurs="0"/><!-- Only present for small molecule drugs -->
<xs:element name="experimental-properties" type="PropertiesType"/>
<xs:element name="external-identifiers" type="IdentifiersType"/>
<xs:element ref="external-links"/>
<xs:element ref="targets"/>
<xs:element ref="enzymes"/>
<xs:element ref="transporters"/>
<xs:element ref="carriers"/>
</xs:sequence>
<xs:attribute name="type" use="required">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="small molecule"/>
<xs:enumeration value="biotech"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="updated" type="xs:string" use="required"/>
<xs:attribute name="created" type="xs:string" use="required"/>
<xs:attribute name="version" type="xs:decimal" use="required"/>
</xs:complexType>
<xs:element name="drugs" xdb:defaultTable="DRUGS">
<xs:complexType>
<xs:sequence>
<xs:element name="drug" type="DrugType" minOccurs="0" maxOccurs="unbounded" />
<xs:element name="partners">
<xs:complexType>
<xs:sequence>
<xs:element name="partner" type="PartnerType" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:keyref name="targetPartnerIdKeyRef" refer="partnerIdKey">
<xs:selector xpath="drug/targets/*"/>
<xs:field xpath="@partner"/>
</xs:keyref>
<xs:keyref name="enzymePartnerIdKeyRef" refer="partnerIdKey">
<xs:selector xpath="drug/enzymes/*"/>
<xs:field xpath="@partner"/>
</xs:keyref>
<xs:keyref name="transporterPartnerIdKeyRef" refer="partnerIdKey">
<xs:selector xpath="drug/transporters/*"/>
<xs:field xpath="@partner"/>
</xs:keyref>
<xs:keyref name="carrierPartnerIdKeyRef" refer="partnerIdKey">
<xs:selector xpath="drug/carriers/*"/>
<xs:field xpath="@partner"/>
</xs:keyref>
<xs:key name="partnerIdKey">
<xs:selector xpath=".//partner"/>
<xs:field xpath="@id"/>
</xs:key>
</xs:element>
</xs:schema>
Query optimizing whitepaper
http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf