10 Replies Latest reply: Jan 14, 2013 11:00 AM by odie_63 RSS

    opening file for dbms_xmldom

    BISP
      Hello, I'm using Oracle 10g and trying to parse an XML file using dbms_xmldom. How do I retrieve the XML from the UNIX directory? Do I use util_file.fopen?
        • 1. Re: opening file for dbms_xmldom
          odie_63
          Hi,

          Create an Oracle directory object that points to your UNIX dir, e.g.
          create or replace directory TEST_DIR as '/home/oracle/xml' ;
          (make sure the db has access rights to the OS dir)

          Then you can use something like this :
          DECLARE
          
            p   dbms_xmlparser.Parser;
            doc dbms_xmldom.DOMDocument;
            
          BEGIN
           
            p := dbms_xmlparser.newParser;
            
            -- charset parameter is optional if the same as the db's
            doc := dbms_xmlparser.parse('TEST_DIR/test.xml', nls_charset_id('AL32UTF8'));
            dbms_xmlparser.freeParser(p);
            
            -- ... your code goes here ...
            
            dbms_xmldom.freeDocument(doc);
            
          END;
          /
          Have you thought about using XMLType's parsing methods as an alternative to DOM?
          • 2. Re: opening file for dbms_xmldom
            BISP
            Thanks, I will try that. I do have database directories set up so this should work. I only thought of dbms_xmldom because I used it a few years back to create an xml document. Is XMLtype better?
            • 3. Re: opening file for dbms_xmldom
              odie_63
              Is XMLtype better?
              It depends on the requirement.

              Do you want to extract XML data in a relational form, or just parse a few elements/attributes?
              The size of the file matters too.

              Post some sample data and expected output, we'll be able to help you further in using XMLType, if you're interested.
              • 4. Re: opening file for dbms_xmldom
                BISP
                I just need a a couple of values from a small XML file.

                I tried this code:

                BEGIN

                f := utl_file.fopen('INDATA_DIR',fname,'R');
                p := dbms_xmlparser.newParser;

                -- charset parameter is optional if the same as the db's
                doc := dbms_xmlparser.parse(f, nls_charset_id('AL32UTF8'));
                dbms_xmlparser.freeParser(p);

                -- ... your code goes here ...
                dbms_output.new_line;
                dbms_output.put_line('Method 2');
                l_nodelist := dbms_xmldom.getelementsbytagname(doc, 'POH_COMM_METHOD');
                -- get first item from list, could loop as shown above
                l_node := dbms_xmldom.item(l_nodelist, 0);
                l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
                dbms_output.put_line('Emp Name: '||l_value);


                dbms_xmldom.freeDocument(doc);

                END;

                and I got this:
                PLS-00306: wrong number or types of arguments in call to 'PARSE'
                INDATA_DIR is defined as a database directory

                If I hardcode the file name instead of using "f"
                I get "Invalid resource handle or path name "/jcibi/frd8/indata/POXPRPOP_4578933.xml"
                ORA-06512: at "SYS.XDBURITYPE", line 11
                ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 142"
                • 5. Re: opening file for dbms_xmldom
                  odie_63
                  Hi,
                  I tried this code:

                  BEGIN

                  f := utl_file.fopen('INDATA_DIR',fname,'R');
                  p := dbms_xmlparser.newParser;

                  ...

                  and I got this:
                  PLS-00306: wrong number or types of arguments in call to 'PARSE'
                  INDATA_DIR is defined as a database directory

                  If I hardcode the file name instead of using "f"
                  I get "Invalid resource handle or path name "/jcibi/frd8/indata/POXPRPOP_4578933.xml"
                  ORA-06512: at "SYS.XDBURITYPE", line 11
                  ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 142"
                  I'm wondering... what's the point of posting an example if you don't follow it at all?

                  Do you see any UTL_FILE call in my example?
                  Answer : no.

                  Assuming <tt>fname</tt> contains only the filename (ie "POXPRPOP_4578933.xml"), try :
                    ...
                  
                    p := dbms_xmlparser.newParser;
                  
                    doc := dbms_xmlparser.parse('INDATA_DIR' || '/' || fname, nls_charset_id('AL32UTF8'));
                    dbms_xmlparser.freeParser(p);
                    
                    ...
                  • 6. Re: opening file for dbms_xmldom
                    odie_63
                    Example with XMLType :

                    test.xml
                    <?xml version="1.0" encoding="UTF-8" ?>
                    <root>
                      <emp>
                        <id>103</id>
                        <name>SMITH</name>
                      </emp>
                    </root>
                    SQL> DECLARE
                      2  
                      3    v_filename varchar2(260) := 'test.xml';
                      4  
                      5    v_empid    number(3);
                      6    v_empname  varchar2(80);
                      7  
                      8  BEGIN
                      9  
                     10    select x.empid
                     11         , x.empname
                     12    into v_empid
                     13       , v_empname
                     14    from xmltable(
                     15          '/root/emp'
                     16          passing xmltype(
                     17                    bfilename('TEST_DIR', v_filename)
                     18                  , nls_charset_id('AL32UTF8')
                     19                  )
                     20          columns
                     21            empid   number(3)    path 'id'
                     22          , empname varchar2(80) path 'name'
                     23         ) x ;
                     24  
                     25    dbms_output.put_line('EmpID = '|| v_empid);
                     26    dbms_output.put_line('EmpName = '|| v_empname);
                     27  
                     28  END;
                     29  /
                     
                    EmpID = 103
                    EmpName = SMITH
                     
                    PL/SQL procedure successfully completed
                     
                    • 7. Re: opening file for dbms_xmldom
                      BISP
                      First of all, I really appreciate the help on this. However, I don't appreciate being treated like a 6 year old. Maybe I did not make it clear, but I did follow your example but I got the "Invalid resource handle" error. It was only then that I tried the utl_file approach. Whatever the case, I reran my original procedure and the "Invalid resource handle" error is no longer occurring. Thanks again for the solution and the xmltype tip
                      • 8. Re: opening file for dbms_xmldom
                        Jason_(A_Non)
                        Just a follow-up given I recognize your example code.

                        A pure PL/SQL example, based off Method 4 from [url http://anononxml.blogspot.com/2010/05/one-question-that-comes-up-with-some.html]Basic XML Parsing via PL/SQL  would be
                        (not tested)
                        DECLARE 
                           v_filename varchar2(260) := 'test.xml';
                           v_xmltype  XMLType;
                           v_empid    number(3);
                           v_empname  varchar2(80);
                        BEGIN
                           v_xmltype := xmltype(
                                                bfilename('TEST_DIR', v_filename)
                                              , nls_charset_id('AL32UTF8')
                                              );
                        
                           v_empid   := v_xmltype.extract('/root/emp/id/text()').getStringVal();
                           v_empname := v_xmltype.extract('/root/emp/name/text()').getStringVal();
                                             
                           dbms_output.put_line('EmpID = '|| v_empid);
                           dbms_output.put_line('EmpName = '|| v_empname);
                                             
                        END;
                        It is much easier to read than the DOMDocument version and comparable the XMLTable method. For large XML, the XMLTable, though a different version than shown, outperforms the PL/SQL approach. For small XML, which performs better will be left up to you.
                        • 9. Re: opening file for dbms_xmldom
                          odie_63
                          However, I don't appreciate being treated like a 6 year old.
                          Did I ?
                          Sorry, didn't mean it, but the layout of your post really says you've tried the UTL_FILE bit first.
                          • 10. Re: opening file for dbms_xmldom
                            odie_63
                            A_Non wrote:
                            A pure PL/SQL example, based off Method 4 from [url http://anononxml.blogspot.com/2010/05/one-question-that-comes-up-with-some.html]Basic XML Parsing via PL/SQL  would be
                            (not tested)
                            And to be even more complete, let's not forget to unescape character entities (if any) :
                            SQL> DECLARE
                              2  
                              3    v_doc  xmltype := xmltype('<root><name>SMITH&amp;WESSON</name></root>');
                              4    v_name varchar2(30);
                              5  
                              6  BEGIN
                              7  
                              8    v_name := v_doc.extract('/root/name/text()').getStringVal();
                              9    dbms_output.put_line('name = '||v_name);
                             10  
                             11    v_name := dbms_xmlgen.convert(
                             12                v_doc.extract('/root/name/text()').getStringVal()
                             13              , dbms_xmlgen.ENTITY_DECODE
                             14              );
                             15    dbms_output.put_line('name = '||v_name);
                             16  
                             17    v_name := utl_i18n.unescape_reference(v_doc.extract('/root/name/text()').getStringVal());
                             18    dbms_output.put_line('name = '||v_name);
                             19  
                             20  END;
                             21  /
                             
                            name = SMITH&amp;WESSON
                            name = SMITH&WESSON
                            name = SMITH&WESSON
                             
                            PL/SQL procedure successfully completed