<p>I have a requirement to validate an incoming file against an XSD. Both will be on the server file system.<br /></p>

<p>I've looked at <code>dbms_xmlschema</code>, but have had issues getting it to work.</p>

<p>Could it be easier to do it with some Java?<br />What's the simplest class I could put in the database?</p>

<p>Here's a simple example:</p>

<pre><code>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 =&gt; v_schema_url,
                                schemaDoc =&gt; '
&lt;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"&gt;
&lt;xs:element name="something"  type="xs:string"/&gt;
&lt;/xs:schema&gt;',
                                local =&gt; TRUE);

  v_xml := XMLTYPE.createxml('&lt;something xmlns="http://www.xx.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://www.example.com/schema.xsd"&gt;
data
&lt;/something&gt;');

  IF v_xml.isschemavalid(v_schema_url) = 1 THEN
    dbms_output.put_line('valid');
  ELSE
    dbms_output.put_line('not valid');
  END IF;
END;
</code></pre>

<p>This generates the following error:</p>

<pre><code>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
</code></pre>

[{'answer_id': 83488, 'author': 'Adam Hawkes', 'author_id': 6703, 'author_profile': 'https://Stackoverflow.com/users/6703', 'pm_score': -1, 'selected': False, 'text': "<p>If I remember correctly, that error message is given when XDB (Oracle's XML DataBase package) is not properly installed. Have the DBA check this out.</p>\n"}, {'answer_id': 178790, 'author': 'Jim Hudson', 'author_id': 8051, 'author_profile': 'https://Stackoverflow.com/users/8051', 'pm_score': 0, 'selected': False, 'text': '<p>Once you get past the install issues, there are challenges in some Oracle versions when the schemas get big, particularly when you have schemas that include other schemas. I know we had that issue in 9.2, not sure about 10.2 or 11.</p>\n\n<p>For small schemas like your example, though, it should just work.</p>\n'}, {'answer_id': 5274406, 'author': 'Tomasz Żuk', 'author_id': 655526, 'author_profile': 'https://Stackoverflow.com/users/655526', 'pm_score': 2, 'selected': False, 'text': '<p>You must have <code>ALTER SESSION</code> privilege granted in order to register a schema.</p>\n'}, {'answer_id': 6367816, 'author': 'user272735', 'author_id': 272735, 'author_profile': 'https://Stackoverflow.com/users/272735', 'pm_score': 3, 'selected': False, 'text': '<p><strong>Update</strong></p>\n\n<p>XML Schema registration requires following privileges:</p>\n\n<pre><code>grant alter session to &lt;USER&gt;;\ngrant create type to &lt;USER&gt;; /* required when gentypes =&gt; true */\ngrant create table to &lt;USER&gt;; /* required when gentables =&gt; true */\n</code></pre>\n\n<p>For some reason it\'s not enough if those privileges are granted indirectly via roles, but the privileges need <em>to be granted directly to schema/user</em>.</p>\n\n<p><strong>Original Answer</strong></p>\n\n<p>I have also noticed that default values of parameters <code>gentables</code> and <code>gentypes</code> raise <code>insufficient privileges</code> 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.</p>\n\n<p>I\'m running on Oracle Database 11g Release 11.2.0.1.0</p>\n\n<h2>gentypes => true, gentables => true</h2>\n\n<pre><code>dbms_xmlschema.registerschema(schemaurl =&gt; name,\n                              schemadoc =&gt; xmltype(schema),\n                              local =&gt; true\n                              --gentypes =&gt; false,\n                              --gentables =&gt; false\n                              );\n\nORA-01031: insufficient privileges\nORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55\nORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159\nORA-06512: at "JANI.XML_VALIDATOR", line 38\nORA-06512: at line 7\n</code></pre>\n\n<h2>gentypes => false, gentables => true</h2>\n\n<pre><code>dbms_xmlschema.registerschema(schemaurl =&gt; name,\n                              schemadoc =&gt; xmltype(schema),\n                              local =&gt; true,\n                              gentypes =&gt; false\n                              --gentables =&gt; false\n                              );\n\nORA-31084: error while creating table "JANI"."example873_TAB" for element "example"\nORA-01031: insufficient privileges\nORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55\nORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159\nORA-06512: at "JANI.XML_VALIDATOR", line 38\nORA-06512: at line 7\n</code></pre>\n\n<h2>gentypes => true, gentables => false</h2>\n\n<pre><code>dbms_xmlschema.registerschema(schemaurl =&gt; name,\n                              schemadoc =&gt; xmltype(schema),\n                              local =&gt; true,\n                              --gentypes =&gt; false\n                              gentables =&gt; false\n                              );\n\nORA-01031: insufficient privileges\nORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55\nORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159\nORA-06512: at "JANI.XML_VALIDATOR", line 38\nORA-06512: at line 7\n</code></pre>\n\n<h2>gentypes => false, gentables => false</h2>\n\n<pre><code>dbms_xmlschema.registerschema(schemaurl =&gt; name,\n                              schemadoc =&gt; xmltype(schema),\n                              local =&gt; true,\n                              gentypes =&gt; false,\n                              gentables =&gt; false\n                              );\n\nPL/SQL procedure successfully completed.\n</code></pre>\n'}, {'answer_id': 12053644, 'author': 'Pierre-Gilles Levallois', 'author_id': 2162529, 'author_profile': 'https://Stackoverflow.com/users/2162529', 'pm_score': 2, 'selected': False, 'text': '<p>here is a piece of code that works for me.\nuser272735\'s answer is right, I wrote another answer as far as I can\'t write all the code in a comment (too long).</p>\n\n<pre><code>/* Formatted on 21/08/2012 12:52:47 (QP5 v5.115.810.9015) */\nDECLARE\n   -- Local variables here\n   res          BOOLEAN;\n   tempXML      XMLTYPE;\n   xmlDoc       XMLTYPE;\n   xmlSchema    XMLTYPE;\n   schemaURL    VARCHAR2 (256) := \'testcase.xsd\';\nBEGIN\n   dbms_xmlSchema.deleteSchema (schemaURL, 4);\n   -- Test statements here\n   xmlSchema :=\n      xmlType(\'&lt;?xml version="1.0" encoding="UTF-8"?&gt;\n&lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"\nelementFormDefault="qualified" attributeFormDefault="unqualified"&gt;\n&lt;xs:element name="root" xdb:defaultTable="ROOT_TABLE"&gt;\n&lt;xs:complexType&gt;\n&lt;xs:sequence&gt;\n&lt;xs:element name="child1"/&gt;\n&lt;xs:element name="child2"/&gt;\n&lt;/xs:sequence&gt;\n&lt;/xs:complexType&gt;\n&lt;/xs:element&gt;\n&lt;/xs:schema&gt;\n\');\n    -- http://stackoverflow.com/questions/82047/validating-xml-files-against-schema-in-oracle-pl-sql\n    dbms_xmlschema.registerschema(schemaurl =&gt; schemaURL,\n                                  schemadoc =&gt; xmlSchema,\n                                  local =&gt; true,\n                                  gentypes =&gt; false,\n                                  gentables =&gt; false\n                                  );\n   xmlDoc :=\n      xmltype(\'&lt;root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="\'\n              || schemaURL\n              || \'"&gt;&lt;child1&gt;foo&lt;/child1&gt;&lt;child2&gt;bar&lt;/child2&gt;&lt;/root&gt;\');\n   xmlDoc.schemaValidate ();\n   -- if we are here, xml is valid\n   DBMS_OUTPUT.put_line (\'OK\');\nexception\n    when others then\n    DBMS_OUTPUT.put_line (SQLErrm);\nEND;\n</code></pre>\n'}, {'answer_id': 48176686, 'author': 'Robin', 'author_id': 6010174, 'author_profile': 'https://Stackoverflow.com/users/6010174', 'pm_score': 0, 'selected': False, 'text': '<p>Registering the XSD leads to creation of tables, types and triggers. Therefore you need the following grants:</p>\n\n<pre><code>grant create table to &lt;user&gt;;\ngrant create type to &lt;user&gt;;\ngrant create trigger to &lt;user&gt;;\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/82047', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1895/']