How to parse the XML document using PL/SQL and insert the data into Table

I want to parse the XML document do the validation in PL/SQL and insert the data into the table. Can any one give a example how i can do this?
LVL 2
SGosukondaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sonicefuCommented:
DBMS_XMLPARSER
Using DBMS_XMLPARSER, you can access the contents and structure of XML documents.
------------------------

Functions and Procedures of DBMS_XMLPARSER
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_xmlpa2.htm#1003539
0
sonicefuCommented:
To see a detailed example, check the following link

http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SGosukondaAuthor Commented:
Hi thanks for ur quick help. when i started using the code in the above link i am getting the following error. can you help in resloving this???
Error Number: -22288
Error Message:ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sonicefuCommented:
have you created directory
CREATE DIRECTORY xml_dir AS 'c:\';

have you granted access to user where you want perform this task, for example scott
GRANT READ ON DIRECTORY xml_dir TO <user-name>;
0
sonicefuCommented:
and have placed the file at c:\
0
SGosukondaAuthor Commented:
I did that one also..
i created directory using SYS and i gave access to my current user.
after that i created the table and copied the procedure from the link which u have mentioned.
then also same issue.

the following are the step which i did
Step 1: lgged into SYS user and executed the following scripts
drop directory xml_dir;
create directory XML_DIR as 'c:\';
GRANT READ ON DIRECTORY xml_dir TO eval2;

Step2: Looged of from SYS and logged into my user i.e. eval2

Step 3: Created emp table
CREATE TABLE EMP (
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10),
  JOB       VARCHAR2(9),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7, 2),
  COMM      NUMBER(7, 2),
  DEPTNO   NUMBER(2));

Step4: Copied the procedure and executed. The following is the proc FYI

DECLARE
  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(1000);

  TYPE tab_type IS TABLE OF emp%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN

  dbms_output.put_line('START parse: ');

  l_bfile := BFileName('XML_DIR', 'emp.xml');
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => l_clob,
                        src_lob  => l_bfile,
                        amount   => dbms_lob.getLength(l_bfile));
  dbms_lob.close(l_bfile);
  dbms_output.put_line('CLOSE parse: ');

  -- make sure implicit date conversions are performed correctly
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;

  -- Parse the document and create a new DOM document.
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);

  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);

  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');

  -- Loop through the list and create a new record in a tble collection
  -- for each EMP record.
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_emp);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
    dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
    dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
    dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
    dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
    dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
    dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
    dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
  END LOOP;

  -- Insert data into the real EMP table from the table collection.
  -- Form better performance multiple collections should be used to allow
  -- bulk binding using the FORALL construct but this would make the code
  -- too long-winded for this example.
  FOR cur_emp IN t_tab.first .. t_tab.last LOOP
    INSERT INTO emp
    (empno,
     ename,
     job,
     mgr,
     hiredate,
     sal,
     comm,
     deptno)
    VALUES
    (t_tab(cur_emp).empno,
     t_tab(cur_emp).ename,
     t_tab(cur_emp).job,
     t_tab(cur_emp).mgr,
     t_tab(cur_emp).hiredate,
     t_tab(cur_emp).sal,
     t_tab(cur_emp).comm,
     t_tab(cur_emp).deptno);
  END LOOP;

  COMMIT;

  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc);

EXCEPTION
  WHEN OTHERS THEN
        --err_num := SQLCODE;
    --err_msg := SUBSTR(SQLERRM, 1, 100);
        dbms_output.put_line('Error Number: '||SQLCODE || 'Error Message:' || SUBSTR(SQLERRM, 1, 100));
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);

END;
/

 
0
SGosukondaAuthor Commented:
Step 5: copied the emp.xml in c:\ location
0
sdstuberCommented:
can you post your xml document?  It may be more efficient to extract the contents other ways.
0
SGosukondaAuthor Commented:
hi
i found the solution. it is working fine now. thanks for your help.
--Suresh
0
SGosukondaAuthor Commented:
hi thanks for spending the time in giving the solution to my probelm. it is working fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.