Validating XML files against schema in Oracle PL/SQL

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 privilegesORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159ORA-06512: at "JANI.XML_VALIDATOR", line 38ORA-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 privilegesORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159ORA-06512: at "JANI.XML_VALIDATOR", line 38ORA-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 privilegesORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159ORA-06512: at "JANI.XML_VALIDATOR", line 38ORA-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.

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;