We are creating a huge XML file with patient-drug information. We read the tag values from tables and create the XML file and insert to a CLOB column in a table.
Before inserting we want to make sure the XML is validated against an XSD.
In order to test this I created a small SP.
My DB is : SELECT banner FROM v$version WHERE ROWNUM = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
The user has the following privs:
USERNAME PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
PEGA CREATE SYNONYM NO
PEGA UNLIMITED TABLESPACE NO
PEGA CREATE ANY TRIGGER NO
My SP is:
create or replace
procedure validate_xml_test as
-- Local variables here
-- XML and XSD got from there : http://www.w3schools.com/schema/schema_howto.asp
res BOOLEAN;
tempXML XMLTYPE;
xmlDoc XMLTYPE;
xmlSchema XMLTYPE;
schemaURL VARCHAR2 (256) := 'testcase.xsd';
v_error_at number;
BEGIN
v_error_at := 05;
dbms_xmlSchema.deleteSchema (schemaURL, /* 4 */ DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE);
v_error_at := 10;
xmlSchema :=
xmlType('<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.w3schools.com"
xmlns="http://www.w3schools.com"
elementFormDefault="qualified">
<xs:element name="note">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string"/>
<xs:element name="from" type="xs:string"/>
<xs:element name="heading" type="xs:string"/>
<xs:element name="body" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
');
v_error_at := 20;
dbms_xmlschema.registerschema(schemaurl => schemaURL,
schemadoc => xmlSchema
, local => TRUE
, genTypes => FALSE
, genbean => FALSE
, genTables => FALSE);
v_error_at := 30;
xmlDoc :=
xmltype('<?xml version="1.0"?>
<note xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="'|| schemaURL || '">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>');
v_error_at := 40;
xmlDoc := xmlDoc.createSchemaBasedXML();
v_error_at := 50;
xmlDoc.schemaValidate ();
-- if we are here, xml is valid
DBMS_OUTPUT.put_line ('Schema is OK.');
exception
when others then
DBMS_OUTPUT.put_line ('Error @ : ' || v_error_at || ' -> ' || SQLErrm);
END;
Before running I check this: select * from user_xml_schemas;
Gives no rows.
Here is the output of the SP:
BEGIN validate_xml_test(); END;
Error @ : 5 -> ORA-31000: Resource 'testcase.xsd' is not an XDB schema document
You can copy and paste the above SP in your SQL*Developer and run.
As you can see, the error is thrown at the every 1st statement, here: dbms_xmlSchema.deleteSchema
Any help would be greatly appreciated. Solutions on similar errors in Google did not help.
PS: I did some reading and found out that the above error could be because the schema actually does not exist.
Now I modified my SP like this:
create or replace
procedure validate_xml_test as
-- Local variables here
-- XML and XML got from there : http://www.w3schools.com/schema/schema_howto.asp
res BOOLEAN;
tempXML XMLTYPE;
xmlDoc XMLTYPE;
xmlSchema XMLTYPE;
schemaURL VARCHAR2 (256) := 'testcase.xsd';
v_error_at number;
v_count number;
BEGIN
v_error_at := 05;
--dbms_xmlSchema.deleteSchema (schemaURL, /* 4 */ DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE);
SELECT COUNT(1) INTO v_count FROM user_xml_schemas WHERE schema_url = 'testcase.xsd';
IF v_count > 0 THEN
GOTO label1;
END IF;
v_error_at := 10;
xmlSchema :=
xmlType('<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.w3schools.com"
xmlns="http://www.w3schools.com"
elementFormDefault="qualified">
<xs:element name="note">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string"/>
<xs:element name="from" type="xs:string"/>
<xs:element name="heading" type="xs:string"/>
<xs:element name="body" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
');
v_error_at := 20;
dbms_xmlschema.registerschema(schemaurl => schemaURL,
schemadoc => xmlSchema
, local => TRUE
, genTypes => FALSE
, genbean => FALSE
, genTables => FALSE);
<<label1>>
v_error_at := 30;
xmlDoc :=
xmltype('<?xml version="1.0"?>
<note xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="'|| schemaURL || '">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>');
v_error_at := 40;
-- xmlDoc := xmlDoc.createSchemaBasedXML();
v_error_at := 50;
xmlDoc.schemaValidate ();
-- if we are here, xml is valid
DBMS_OUTPUT.put_line ('Schema is OK.');
exception
when others then
DBMS_OUTPUT.put_line ('Error @ : ' || v_error_at || ' -> ' || SQLErrm);
END;
I queried the DD: SELECT substr(schema_url, 1, 30) schema_url, local, substr(schema, 1, 15) schema, SUBSTR(qual_schema_url, 1, 100) qual_sch_url fROM user_xml_schemas;
SCHEMA_URL LOCAL SCHEMA QUAL_SCH_URL
------------------------------ ----- --------------- ----------------------------------------------------------------------------------------------------
testcase.xsd YES <?xml version=" http://xmlns.oracle.com/xdb/schemas/PEGA/testcase.xsd
Now When I run this I get: Error @ : 50 -> ORA-30937: No schema definition for 'note' (namespace '') in parent '/'
But, I have given the XML in correct format as the XSD.