SGosukonda
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Error Number: -22288
Error Message:ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
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>;
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>;
and have placed the file at c:\
ASKER
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('STAR T 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('CLOS E parse: ');
-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_ DATE_FORMA T','''DD-M ON-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_c lob);
dbms_xmlparser.freeParser( l_parser);
-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNo des(dbms_x mldom.make Node(l_doc ),'/EMPLOY EES/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).emp no);
dbms_xslprocessor.valueOf( l_n,'ENAME /text()',t _tab(t_tab .last).ena me);
dbms_xslprocessor.valueOf( l_n,'JOB/t ext()',t_t ab(t_tab.l ast).job);
dbms_xslprocessor.valueOf( l_n,'MGR/t ext()',t_t ab(t_tab.l ast).mgr);
dbms_xslprocessor.valueOf( l_n,'HIRED ATE/text() ',t_tab(t_ tab.last). hiredate);
dbms_xslprocessor.valueOf( l_n,'SAL/t ext()',t_t ab(t_tab.l ast).sal);
dbms_xslprocessor.valueOf( l_n,'COMM/ text()',t_ tab(t_tab. last).comm );
dbms_xslprocessor.valueOf( l_n,'DEPTN O/text()', t_tab(t_ta b.last).de ptno);
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('Erro r Number: '||SQLCODE || 'Error Message:' || SUBSTR(SQLERRM, 1, 100));
dbms_lob.freetemporary(l_c lob);
dbms_xmlparser.freeParser( l_parser);
dbms_xmldom.freeDocument(l _doc);
END;
/
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('STAR
l_bfile := BFileName('XML_DIR', 'emp.xml');
dbms_lob.createtemporary(l
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile
dbms_lob.close(l_bfile);
dbms_output.put_line('CLOS
-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_
-- Create a parser.
l_parser := dbms_xmlparser.newParser;
-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l
l_doc := dbms_xmlparser.getDocument
-- Free resources associated with the CLOB and Parser now they are no longer needed.
dbms_lob.freetemporary(l_c
dbms_xmlparser.freeParser(
-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNo
-- 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
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(
dbms_xslprocessor.valueOf(
dbms_xslprocessor.valueOf(
dbms_xslprocessor.valueOf(
dbms_xslprocessor.valueOf(
dbms_xslprocessor.valueOf(
dbms_xslprocessor.valueOf(
dbms_xslprocessor.valueOf(
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
EXCEPTION
WHEN OTHERS THEN
--err_num := SQLCODE;
--err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('Erro
dbms_lob.freetemporary(l_c
dbms_xmlparser.freeParser(
dbms_xmldom.freeDocument(l
END;
/
ASKER
Step 5: copied the emp.xml in c:\ location
can you post your xml document? It may be more efficient to extract the contents other ways.
ASKER
hi
i found the solution. it is working fine now. thanks for your help.
--Suresh
i found the solution. it is working fine now. thanks for your help.
--Suresh
ASKER
hi thanks for spending the time in giving the solution to my probelm. it is working fine.
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