I'm trying to validate a document XML during it's inserting into a table in ORACLE database. I've define a XML Schema and a XMLTYPE with this schema in my table but the db allow me to insert a wrong xml.
My schema:
BEGIN
dbms_xmlschema.registerschema(
schemaurl => 'http://www.testXml.com/schema.xsd',
schemadoc => xmltype('<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="Name_tp">
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="Id" type="xs:positiveInteger"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:complexType name="Category_tp">
<xs:attribute name="NumPrize" type="xs:positiveInteger"/>
<xs:attribute name="From" type="xs:integer"/>
<xs:attribute name="To" type="xs:positiveInteger"/>
<xs:attribute name="Type" type="xs:string"/>
<xs:attribute name="Age" type="xs:positiveInteger"/>
</xs:complexType>
<xs:complexType name="Match_tp">
<xs:attribute name="typeMatch" type="xs:string"/>
</xs:complexType>
<xs:complexType name="GolfCompetition_tp">
<xs:sequence>
<xs:element name="Name" type="Name_tp"/>
<xs:element name="Date" type="xs:date"/>
<xs:element name="Sponsor" type="xs:string"/>
<xs:element name="Category" maxOccurs="unbounded" type="Category_tp"/>
<xs:element name="Reserved" type="Match_tp" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:element name="GolfCompetition" type="GolfCompetition_tp"/>
</xs:schema>'),
local => true,
gentypes => false,
gentables => false
);
END;
My db schema:
CREATE TYPE t_gara AS OBJECT (
id INTEGER,
informazioni XMLTYPE
);
CREATE TABLE gara OF t_gara () XMLType COLUMN informazioni
XMLSCHEMA "http://www.testXml.com/schema.xsd"
ELEMENT "GolfCompetition";
When i try to insert this row i've not problem:
INSERT INTO GARA VALUES(1, XMLType('<GolfCompetition>
<Name Id="324">Coppa del Presidente</Name>
<Date>2009-12-25</Date>
<Sponsor>Lavazza S.p.A</Sponsor>
<Category NumPrize="3" From="0" To="12" Type="First"/>
<Category NumPrize="3" From="13" To="24" Type="Second"/>
<Category NumPrize="2" From="25" To="36" Type="Third"/>
<Category NumPrize="1" Type="Lady"/>
<Category NumPrize="1" Type="Over" Age="40"/>
</GolfCompetition>'));
But this works to:
INSERT INTO GARA VALUES(2, XMLType('<GolfCompetition>
<Category NumPrize="3" From="0" To="12" Type="First"/>
<Category NumPrize="3" From="13" To="24" Type="Second"/>
<Category NumPrize="2" From="25" To="36" Type="Third"/>
<Category NumPrize="1" Type="Lady"/>
<Category NumPrize="1" Type="Over" Age="40"/>
</GolfCompetition>'));
How can I solve?
Automatic schema validation requires storing the XML in a binary format, not object-relationally.
From the XML Developer's Guide:
For XMLType data that is stored object-relationally, full validation requires building a DOM, which can be costly in terms of memory management. For this reason, Oracle XML DB does not automatically perform full validation when you insert or update data that is stored object-relationally.
Re-register the schema, adding this parameter:
...
options => dbms_xmlschema.REGISTER_BINARYXML
...
Create the table like this:
create table gara
(
id number,
informazioni xmltype
) xmltype informazioni
store as binary xml
XMLSCHEMA "http://www.testXml.com/schema.xsd"
ELEMENT "GolfCompetition";
Now the first insert will work but the second will fail with this error:
ORA-64464: XML event error
ORA-19202: Error occurred in XML processing
LSX-00213: only 0 occurrences of particle "Name", minimum is 1