Validating XML files against schema in Oracle PL/SQL
Solution 1
Update
XML Schema registration requires following privileges:
grant alter session to <USER>;
grant create type to <USER>; /* required when gentypes => true */
grant create table to <USER>; /* required when gentables => true */
For some reason it's not enough if those privileges are granted indirectly via roles, but the privileges need to be granted directly to schema/user.
Original Answer
I have also noticed that default values of parameters gentables
and gentypes
raise insufficient privileges
exception. Probably I'm just lacking of some privileges to use those features, but at the moment I don't have a good understanding what they do. I'm just happy to disable them and validation seems to work fine.
I'm running on Oracle Database 11g Release 11.2.0.1.0
gentypes => true, gentables => true
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true
--gentypes => false,
--gentables => false
);
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159
ORA-06512: at "JANI.XML_VALIDATOR", line 38
ORA-06512: at line 7
gentypes => false, gentables => true
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
gentypes => false
--gentables => false
);
ORA-31084: error while creating table "JANI"."example873_TAB" for element "example"
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159
ORA-06512: at "JANI.XML_VALIDATOR", line 38
ORA-06512: at line 7
gentypes => true, gentables => false
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
--gentypes => false
gentables => false
);
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159
ORA-06512: at "JANI.XML_VALIDATOR", line 38
ORA-06512: at line 7
gentypes => false, gentables => false
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
gentypes => false,
gentables => false
);
PL/SQL procedure successfully completed.
Solution 2
You must have ALTER SESSION
privilege granted in order to register a schema.
Solution 3
here is a piece of code that works for me. user272735's answer is right, I wrote another answer as far as I can't write all the code in a comment (too long).
/* Formatted on 21/08/2012 12:52:47 (QP5 v5.115.810.9015) */
DECLARE
-- Local variables here
res BOOLEAN;
tempXML XMLTYPE;
xmlDoc XMLTYPE;
xmlSchema XMLTYPE;
schemaURL VARCHAR2 (256) := 'testcase.xsd';
BEGIN
dbms_xmlSchema.deleteSchema (schemaURL, 4);
-- Test statements here
xmlSchema :=
xmlType('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="root" xdb:defaultTable="ROOT_TABLE">
<xs:complexType>
<xs:sequence>
<xs:element name="child1"/>
<xs:element name="child2"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
');
-- http://stackoverflow.com/questions/82047/validating-xml-files-against-schema-in-oracle-pl-sql
dbms_xmlschema.registerschema(schemaurl => schemaURL,
schemadoc => xmlSchema,
local => true,
gentypes => false,
gentables => false
);
xmlDoc :=
xmltype('<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="'
|| schemaURL
|| '"><child1>foo</child1><child2>bar</child2></root>');
xmlDoc.schemaValidate ();
-- if we are here, xml is valid
DBMS_OUTPUT.put_line ('OK');
exception
when others then
DBMS_OUTPUT.put_line (SQLErrm);
END;
Cooy
Work with Oracle PL/SQL, C# and C++. Play with Python when I find time. Windows for work, Linux for home. Interested in wikis and other ways of managing information. For fun I play guitar and run an acoustic music session and open mic evenings. I run for fitness.
Updated on June 19, 2022Comments
-
Cooy almost 2 years
I have a requirement to validate an incoming file against an XSD. Both will be on the server file system.
I've looked at
dbms_xmlschema
, but have had issues getting it to work.Could it be easier to do it with some Java?
What's the simplest class I could put in the database?Here's a simple example:
DECLARE v_schema_url VARCHAR2(200) := 'http://www.example.com/schema.xsd'; v_blob bLOB; v_clob CLOB; v_xml XMLTYPE; BEGIN begin dbms_xmlschema.deleteschema(v_schema_url); exception when others then null; end; dbms_xmlschema.registerSchema(schemaURL => v_schema_url, schemaDoc => ' <xs:schema targetNamespace="http://www.example.com" xmlns:ns="http://www.example.com" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified" version="3.0"> <xs:element name="something" type="xs:string"/> </xs:schema>', local => TRUE); v_xml := XMLTYPE.createxml('<something xmlns="http://www.xx.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.com/schema.xsd"> data </something>'); IF v_xml.isschemavalid(v_schema_url) = 1 THEN dbms_output.put_line('valid'); ELSE dbms_output.put_line('not valid'); END IF; END;
This generates the following error:
ORA-01031: insufficient privileges ORA-06512: at "XDB.DBMS_XDBZ0", line 275 ORA-06512: at "XDB.DBMS_XDBZ", line 7 ORA-06512: at line 1 ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3 ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14 ORA-06512: at line 12