Parse XML File using PL/SQL

Hell hthukral

If you are not going to store data into DB I think it is not a good idea to use SQL for this. Use some script languages, Java, .NET or whatever.

If you want to practice you have to use DBMS_XML* packages. Documentation is here - Packages and Types Reference - here's the contents
http://www.oracle.com/pls/db102/show_toc?partno=b14258&remark=drilldown&word=


Good luck

I

I agree with ivostoykov.  Depending on the full requirements, there may be easier ways to acheive this.  It may be as simple as applying an XSL Stylesheet.

If you are set on using the DB to perform this, I wanted to throw out using DBMS_LOB.LOADCLOBFROMFILE to get the contents into a CLOB variable then there are several things you could do to it.  For example, use the DOM to process it of simple XML SQL calls.

Note:  Using DBMS_LOB or UTL_FILE, the file would need to be located on the DB server itself.

If possible, please provide a little more detail.

You asked a PL/SQL solution and I gave it as by my knowledge.
To use another tools is easy but there is a big prerequisite - one have to know and to posses experience in that tool.
Speaking retorically one can use Java, VB, VC++, C, Basic, Assembler, Fortran, Cobol, Lisp, Prolog, PL/I, ADA or even machine code directly.

Hello Everyone,

I agree to you all, but the thing is I dont have any other choice. If you see the file I'm basically interested where the code is empty, and down the file there are places where code has a value.

I just need some information out of it. If say I will store the info in the tables will it be easier....Can someone let me know how I parse....Rite now I'm doing the dirty way.....

Procedure          XML_TEST

IS
      v_XML     VARCHAR2(32767) := ' ';
        v_line      VARCHAR2(1000);
      newsXML     xmldom.DOMDocument;
     
      response      xmldom.DOMNodeList;
      year      xmldom.DOMNodeList;
      month      xmldom.DOMNodeList;
      week      xmldom.DOMNodeList;
      day      xmldom.DOMNodeList;
      code      xmldom.DOMNodeList;

      curNode     xmldom.DOMNode;
      yearNode    xmldom.DOMNode;
      monthNode   xmldom.DOMNode;
      weekNode    xmldom.DOMNode;
      dayNode     xmldom.DOMNode;
      codeNode    xmldom.DOMNode;

      chNodetext      VARCHAR(100);
      chNodeName      VARCHAR(100);
     
      yeartext      VARCHAR(100);
      yearName      VARCHAR(100);
     
      monthtext      VARCHAR(100);
      monthName      VARCHAR(100);
     
      weektext      VARCHAR(100);
      weekName      VARCHAR(100);
     
      daytext      VARCHAR(100);
      dayName      VARCHAR(100);
     
      codetext      VARCHAR(100);
      codeName      VARCHAR(100);

      nodeCount     integer;
      yearCount     integer;
      monthCount    integer;
      weekCount     integer;
      dayCount      integer;
      codeCount     integer;
     
     parser       xmlparser.Parser;
      
       pt_inputFile                            UTL_FILE.file_type;
       pt_outputFile                           UTL_FILE.file_type;
      
BEGIN
     
        pt_inputFile := utl_file.fopen('/opt/bin', 'RPC_A_2005.XML', 'r');
--        pt_inputFile := utl_file.fopen('/opt/bin', 'test.xml', 'r');
        
        BEGIN
        
            LOOP
                      utl_file.get_line(pt_inputFile, v_line);
                    v_XML := v_XML||TRIM(BOTH FROM v_line);              
              END LOOP;

            
          EXCEPTION WHEN NO_DATA_FOUND THEN
            
            utl_file.fclose(pt_inputFile);
            
            
        END;


      parser  := xmlparser.newParser;
      xmlparser.parseBuffer(parser, v_XML);
      newsXML := xmlparser.getDocument(parser);
     xmlparser.freeParser(parser);


     curNode := xmldom.makeNode(newsXML);
     curNode := xslprocessor.selectSingleNode(curNode,'//schedule');
     response := xslprocessor.selectNodes(curNode,'//crew');
     nodeCount := xmldom.getLength(response);
     
     yearNode := xmldom.makeNode(newsXML);
     yearNode := xslprocessor.selectSingleNode(yearNode,'//schedule');
     year := xslprocessor.selectNodes(yearNode,'//year');
     yearCount := xmldom.getLength(year);

      FOR j IN 0..nodeCount-1 LOOP
        FOR y IN 0..yearCount-1 LOOP

          curNode := xmldom.item(response, j);

          yearNode := xmldom.item(year, y);

          chNodeName := xmldom.getnodename(curNode);
          curNode := xmldom.getFirstChild(curNode);
          chNodetext := xmldom.getNodeValue(curNode);
           
          yearName := xmldom.getnodename(yearNode);
          yearNode := xmldom.getFirstChild(yearNode);
          yeartext := xmldom.getNodeValue(yearNode);
               
          dbms_output.put_line('Name: ' || chNodeName);
          dbms_output.put_line('Value: ' || chNodetext);
         
          dbms_output.put_line('Name: ' || yearName);
          dbms_output.put_line('Value: ' || yeartext);
         
         
         END LOOP;
      END LOOP;

      xmldom.freeDocument(newsXML);

END;

Thanks,
Harsimrat

Hello Everyone,

Even if it finds the NULL attribute Oracle craps out....is there any solution to get around it........

Please let me know...

Thanks,
Harsimrat

Well here is a samplese that I hope will help you

Good luck

I

Example : Parsing XML from a String and Using DOM

CREATE OR REPLACE FUNCTION idAttributeOfDocElement(xmldoc VARCHAR2 )
RETURN VARCHAR2 IS

  theXmlDoc xmldom.DOMDocument;
  theDocElt xmldom.DOMElement;
  retval    VARCHAR2(400);
  XMLParseError EXCEPTION;
  PRAGMA EXCEPTION_INIT( XMLParseError, -20100 );

  -- Local parse function keeps code cleaner. Return NULL if parse fails
  FUNCTION parse(xml VARCHAR2) RETURN xmldom.DOMDocument IS
    retDoc xmldom.DOMDocument;
    parser xmlparser.Parser;
  BEGIN
    parser := xmlparser.newParser;
    xmlparser.parseBuffer(parser,xml);
    retDoc := xmlparser.getDocument(parser);
    xmlparser.freeParser(parser);
    RETURN retdoc;
  EXCEPTION
    -- If the parse fails, we'll jump here.
    WHEN XMLParseError THEN
      xmlparser.freeParser(parser);
      RETURN retdoc;
  END;

BEGIN
  -- Parse the xml document passed in the VARCHAR2 argument
  theXmlDoc := parse(xmldoc);
  -- If the XML document returned is not NULL...
  IF NOT xmldom.IsNull(theXmlDoc) THEN
    -- Get the outermost enclosing element (aka "Document Element")
    theDocElt := xmldom.getDocumentElement(theXmlDoc);
    -- Get the value of the document element's "id" attribute
    retval    := xmldom.getAttribute(theDocElt,'id');
    -- Free the memory used by the parsed XML document
    xmldom.freeDocument(theXmlDoc);
    RETURN retval;
  ELSE
    RETURN NULL;
  END IF;
END;

If the above post doesn't help, can you post a little more information?

Like a valid XML file (1 to 3 nodes should work), expected results and brief descrpition of how you got the results.

I can't get the example to work as I cant read from the file........or I'm doing something terribly wrong which I think I'm doing as I'm new with XML in PL/SQL

THE actual XML looks like this :

- <sch>
  <cr> Fun</cr>
  <year>2005</year>
  <title />
- <month>
  <name>January</name>
- <week>
  <week_num>1</week_num>
- <day>
  <id>1</id>
  <code />
  <day_num />
  </day>
- <day>
  <id>2</id>
  <code />
  <day_num />
  </day>
- <day>
  <id>3</id>
  <code />
  <day_num />
  </day>
- <day>
  <id>4</id>
  <code />
  <day_num />
  </day>
- <day>
  <id>5</id>
  <code />
  <day_num />
  </day>
- <day>
  <id>6</id>
  <code />
  <day_num />
  </day>
- <day>
  <id>7</id>
  <code>H</code>
  <day_num>1</day_num>
  </day>
  </week>
- <week>
  <week_num>2</week_num>
- <day>
  <id>1</id>
  <code>M</code>
  <day_num>2</day_num>
  </day>
- <day>
  <id>2</id>
  <code>N</code>
  <day_num>3</day_num>
  </day>
- <day>
  <id>3</id>
  <code>N</code>
  <day_num>4</day_num>
  </day>
- <day>
  <id>4</id>
  <code>N</code>
  <day_num>5</day_num>

If you see in the above XML it was generated from an Excel where there was a calendar ....and for symmetry every month had 6 weeks and 7 days and when XML got generated some days_num fields are empty......so when the parser goes through that its just not happy.........

I'm trying to find the code ....where there is empty and related to the day_num from that I have to form the day and store in the Dbase.....

Thanks,
Harsimrat

What version or Oracle are you using?  Please include full version (i.e./ 9.2.0.4).

Now to help me clairify:  You are wanting all the populated days in the week (day_num not null) for each week in the month for each month of the year.  You then want to populate a database table with a structure similar to:

crew
year
week_num
day_num
id
code

The Version is : 9.2.0.1


Yes Correct....The thing is basically I'm looking for the days they are not working......and maybe ...As the day not working the code will be empty..I need to capture that and replace with (o) and store in Dbase.....

Crew :
Date: when they are not working
and code will be (o)

And this is all read from a file and this is only year by year...........its pretty complicated for me...

Thanks a ton...

Harsimrat

This isnt happy here

ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "TRAINING.XML_TEST1", line 27
ORA-06512: at line 7

I put the file in the proper location If I use this funtion it works.....

pt_inputFile                        UTL_FILE.file_type;
      pt_outputFile                       UTL_FILE.file_type;
     
BEGIN
     
       pt_inputFile := utl_file.fopen('/opt/bin', 'RPC_A_2005.XML', 'r');

But unfortunately I dont know the way to change your example and make it work with this......

Sorry for asking such questions....

Thanks,
Harsimrat

Give this a try.  replace the following lines in my previous example with:

create directory xml_dir as '/opt/bin';

     v_bfile                bfile := BFILENAME( 'XML_DIR' , 'RPC_A_2005.XML');



>>Sorry for asking such questions....

no need to apologize.  We all had to start somewhere.

I put some watches on the Cariables :

v_bfile: /opt/bin/RPC_A_2005.XML, esists=F, length=o, open=F
v_lob: open=T, temp=T, length=o, chunksize=8132, data=<>

Hopefully some of this info helps.....

>>Hopefully some of this info helps.....

sorry but not really.  I don't think I've ever done anything that produces that output.

Is it still failing after you made the changes from my last post?

Your last post gives me there errors

ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "TRAINING.XML_TEST1", line 27
ORA-06512: at line 7

I dont know how to make it work......
 
 

By chance, are you making this code a stored procedure?

I had all the code I posted in a single sql script file and executed the stand alone file.  Give that a try.

Same thing when I do that in a single sql script...

 ORA-22285: non-existent directory or file for FILEOPEN operation ORA-06512: at "SYS.DBMS_LOB", line 504 ORA-06512: at line 25

Do you know how can I open the file with (UTL_FILE) I have no clue how to change ur code to work with UTL......

Let me know...

Thanks,
Harsimrat

I've really never used UTL_FILE because of its limitations and don't have a DB available that I can set up to create a working test case.

You should be able to take the code from your original post that loops through GET_LINE and replace the 3 lines from my post below the comment  "-- read the file off disk".  You will probably also need to replace the CLOB variable with your varchar2(32767).

As long as you don't have an XML file greater than 32K you'll be fine.

For maximum flexibility I would probably keep using CLOB.  Using UTL_FILE you will need to read a line and use DBMS_LOD.WRITEAPPEND to keep appending to the lob.

If you're willing, I'd suggest working through the issues we're having with LOADCLOBFROMFILE.  I feel it is the cleanest method.

If you want to continue working with my script, read on:
I'm not on UNIX any longer so we'll have to do some troubleshooting.  Copy the file to /tmp and open the permissions up on it (777).  Then change the create directory statement to point to /tmp.  Also make sure the case of the file name matches exactly.  

If that fails, change the owner and group of the file to oracle and dba and try again.

Let me know.

Hello,

I got the FILEOPEN working using your method, I was not creating Directory.....now its going into the loop and just exiting.........

 for i in ( select extract(column_value,'//name/text()'

after this directly to end.......

Trying to figure out....Let me know if you have any suggestions.....

I really appreciate your help.....will give you points by the end of the day....hopefully this works.....

Thanks,
Harsimrat

Sorry, my mistake it works awesome....

Really appreciate for the help......

Thanks,
Harsimrat