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
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
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
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
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
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
gentypes => false,
gentables => false
);
PL/SQL procedure successfully completed.
You must have ALTER SESSION
privilege granted in order to register a schema.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With