Parse XML File for Insert into PL/SQL tables - Oracle 10g

Could you post the procedure, the line which gave the ORA-31001 error, and the statement that called the procedure? Your sample code indicates two variable declarations followed by a procedure call followed by a variable assignment followed by another procedure call. This is syntactically incorrect and may or may not be the problem. I'm not familiar with xmldom (we use a sax parser) but perhaps it may be looking for an xmltype datatype and not a text (i.e. varchar2)name for the file. These comments may not be helpful, but I wanted to add a comment so I can see any responses from some experts who are more knowledgeable than I.

I have pasted the procedure which I'm testing via execution from sqlplus.  Though I haven't gotten farther than the parse statements ... The error refers to the "xmlparser.parse(parser, xml_file);".  I have also pasted the exec statement and results.  If it is of help, I have pasted the XML file I am testing with.  As I stated I have 'registered' the schema, yet I'm not sure where/when the XML file is to be validated against it.  

I will be processing numerous XML files as input into an application database.  I do not want to load the files themselves ...just the data.  Each file will contain data for one or more table.  I had looked at xmltype datatype, but I wasn't sure how this would be used when I only want to 'read' the files.  

Thanks again for any guidance ...

 ***** PROCEDURE *****

CREATE OR REPLACE PROCEDURE load_remit_proc
(xml_file       VARCHAR2)

IS

  dom_doc      xmldom.domdocument;
  parser          xmlparser.parser;
  remit_list       xmldom.domnodelist;
  remit_rec      remit_code%ROWTYPE;
  v_node         xmldom.domnode;

  /*************************************/
  FUNCTION selected_nodes (
     doc_in     IN      xmldom.domdocument,
     xpath_in   IN      VARCHAR2
  )

    RETURN xmldom.domnodelist
  IS
    retval xmldom.domnodelist;

  BEGIN
    retval := xslprocessor.selectnodes (
              xmldom.makenode (doc_in),
              xpath_in );
    RETURN retval;
  END;

  /************************************/

BEGIN

dbms_output.put_line('START parse: '||xml_file);

    parser := xmlparser.newparser;
    xmlparser.parse (parser, xml_file);
    dom_doc := xmlparser.getdocument (parser);
    xmlparser.freeparser (parser);

dbms_output.put_line('END parse');

  <<load_remit>>
  BEGIN
     remit_list := selected_nodes(dom_doc, '/root/detail');

     FOR entry_index IN 0 .. xmldom.getlength(remit_list) -1
     LOOP
        v_node := xmldom.item (remit_list, entry_index);
        remit_rec.remit_cd := xslprocessor.valueof (v_node, 'remit_cd');
        remit_rec.address1 := xslprocessor.valueof (v_node, 'address1');
        remit_rec.address2 := xslprocessor.valueof (v_node, 'address2');
        remit_rec.city := xslprocessor.valueof (v_node, 'city');
        remit_rec.state := xslprocessor.valueof (v_node, 'state');
        remit_rec.country := xslprocessor.valueof (v_node, 'country');
        remit_rec.zip := xslprocessor.valueof (v_node, 'zip');
        remit_rec.status_cd := xslprocessor.valueof (v_node, 'status_cd');
        remit_rec.status_date := xslprocessor.valueof (v_node, 'status_date');

dbms_output.put_line('rcd: '||remit_rec.remit_cd||' '||remit_rec.city||' '||remit_rec.state);

        INSERT INTO remit_code
                 (remit_cd,
                  address1,
                  address2,
                  city,
                  state,
                  zip,
                  country,
                  status_cd,
                  status_date)
          VALUES (remit_rec.remit_cd,
                  remit_rec.address1,
                  remit_rec.address2,
                  remit_rec.city,
                  remit_rec.state,
                  remit_rec.zip,
                  remit_rec.country,
                  remit_rec.status_cd,
                  remit_rec.status_date);
      END LOOP;
  END load_remit;

  <<cleanup>>
  BEGIN
      COMMIT;
      xmldom.freedocument(dom_doc);
  END cleanup;

EXCEPTION
         --  checks for xmldom errors and raise error processing
END load_remit_proc;
/

  ****** EXECUTION ******

SQL> exec load_remit_proc('/opt/data/ext/test/remit.xml');
START parse: /opt/data/ext/test/remit.xml                              --- dbms output print statement
BEGIN load_remit_proc('/opt/data/ext/test/remit.xml'); END;

*
ERROR at line 1:
ORA-31001: Invalid resource handle or path name "/opt/data/ext/test/remit.xml"
ORA-06512: at "SYS.XDBURITYPE", line 11
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 142
ORA-29280: invalid directory path
ORA-29280: invalid directory path
ORA-29280: invalid directory path
ORA-06512: at "XDB.DBMS_XMLPARSER", line 119
ORA-06512: at "VM_OWNER.LOAD_REMIT_PROC", line 38
ORA-06512: at line 1

 ****** XML FILE ******
<?xml version="1.0" encoding="UTF-8"?>
<ROOT xsi:noNamespaceSchemaLocation="Remit.xsd"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <sold_from_company_cd>0123456789</sold_from_company_cd>
        <submission_date>2007-08-08</submission_date>
        <extract_type>REMIT</extract_type>
        <DETAIL>
                <remit_cd>99</remit_cd>
                <address1>P.O. BOX 12345</address1>
                <address2></address2>
                <city>City</city>
                <state>OH</state>
                <zip>123456789</zip>
                <country>USA</country>
                <status_cd>A</status_cd>
                <status_date>2007-08-08</status_date>
        </DETAIL>
        <DETAIL>
                <remit_cd>97</remit_cd>
                <address1>P.O. BOX 67890</address1>
                <address2></address2>
                <city>CITY</city>
                <state>OH</state>
                <zip>123456789</zip>
                <country>USA</country>
                <status_cd>A</status_cd>
                <status_date>2007-08-08</status_date>
        </DETAIL>
</ROOT>

Thanks!
I had the DBA run the 'create directory' command and I do not get the error and the file loads successfully.  Yea!  

I'm still uncertain about the validation of the schema ...any answers out there on that question?  Does this parsing process validate against the registered schema or do I need to perform other steps to do that?

Again, I haven't done much with schemas in XMLDB so everything I'm going to say is pretty much a semi-educated guess.

I'm thinking that if the XML has the schema spelled out in the namespace then it should validate every time it's accessed and therefore should be validated on a parse.  If not, I'm pretty sure you'll need to explicitly validate it.  A brief scan of the docs didn't help much.

Sorry I can't be more help.  I suggest you just try it and see.  Set up a simple test and try to parse a doc you know won't validate against the schema.

I did as you suggested and altered some file values to be different than the schema definition and I did receive an error in the parsing logic.  Guess I should have tried that first!

Thanks for your help!