Home » Developer & Programmer » JDeveloper, Java & XML » How to parse complex empty elements
How to parse complex empty elements [message #639769] Wed, 15 July 2015 23:24 Go to next message
Manav8901
Messages: 2
Registered: July 2015
Junior Member
How can I parse complex empty elements using DOM? I have a file with 200 elements to parse and insert in the table with multiple children nodes one into another.

For example if i have a complex Empty variable file as follow where DEPT is empty element. I tried different things but not reading the complex empty elements.

Can anyone correct my code and help me to extract complex empty elements?

<?xml version="1.0" encoding="UTF-8"?>

<EMPLOYEES>
<EMP>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPT DEPTNAME="Account" DEPTNO="1"/>
<DEPT DEPTNAME="IT" DEPTNO="2"/>
</EMP>
</EMPLOYEES>

I tried different ways but the last code i tried is as follow

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_nl1 dbms_xmldom.DOMNodeList;
l_n1 dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);

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

BEGIN

Delete from Test_Xml;
delete from emp;

commit;

INSERT INTO TEST_XML
VALUES (
XMLType (BFILENAME ('XML_DIR','emp.xml'),
NLS_CHARSET_ID ('ISO-8859-1'))
);

COMMIT;


SELECT e.getClobVal() INTO l_clob
FROM TEST_XML E;

-- 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');
l_nl1 := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP/DEPT');
-- 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);

FOR cur_emp1 IN 0 .. dbms_xmldom.getLength(l_nl1) - 1 LOOP
l_n1 := dbms_xmldom.item(l_nl1, cur_emp1);

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_n1,'DEPTN0'||'/text()',t_tab(t_tab.last).deptno);
dbms_xslprocessor.valueOf(l_n1,'DEPTNAME'||'/text()',t_tab(t_tab.last).deptname);

END LOOP;
END LOOP;

-- Insert data into the real EMP table from the table collection.
FORALL i IN t_tab.first .. t_tab.last
INSERT INTO emp VALUES t_tab(i);

COMMIT;

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

EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;
/

Output
DEPTNO DEPTNAME
7369 SMITH CLERK 7902 17-DEC-80 800 NULL NULL
7369 SMITH CLERK 7902 17-DEC-80 800 NULL NULL

[Updated on: Thu, 16 July 2015 00:59]

Report message to a moderator

Re: How to parse complex empty elements [message #639777 is a reply to message #639769] Thu, 16 July 2015 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select xmltype('<EMPLOYEES>
  4  <EMP>
  5  <EMPNO>7369</EMPNO>
  6  <ENAME>SMITH</ENAME>
  7  <JOB>CLERK</JOB>
  8  <MGR>7902</MGR>
  9  <HIREDATE>17-DEC-80</HIREDATE>
 10  <SAL>800</SAL>
 11  <DEPT DEPTNAME="Account" DEPTNO="1"/>
 12  <DEPT DEPTNAME="IT" DEPTNO="2"/>
 13  </EMP>
 14  </EMPLOYEES>') val
 15      from dual
 16    )
 17  select deptname, deptno, empno, ename, job, mgr, sal,
 18         to_char(to_date(hiredate,'DD-MON-YY','NLS_DATE_LANGUAGE=AMERICAN'),'DD/MM/YYYY') hiredate
 19  from data,
 20       xmltable('/EMPLOYEES/EMP' passing val
 21                columns
 22                empno    integer      path '/EMP/EMPNO',
 23                ename    varchar2(10) path '/EMP/ENAME',
 24                job      varchar2(10) path '/EMP/JOB',
 25                mgr      integer      path '/EMP/MGR',
 26                hiredate varchar2(10) path '/EMP/HIREDATE',
 27                sal      integer      path '/EMP/SAL'),
 28       xmltable('/EMPLOYEES/EMP/DEPT' passing val
 29                columns
 30                deptname varchar2(10) path '/DEPT/@DEPTNAME',
 31                deptno   integer      path '/DEPT/@DEPTNO')
 32  /
DEPTNAME       DEPTNO      EMPNO ENAME      JOB               MGR        SAL HIREDATE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Account             1       7369 SMITH      CLERK            7902        800 17/12/2080
IT                  2       7369 SMITH      CLERK            7902        800 17/12/2080

2 rows selected.


Note:

Quote:
EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
RAISE;

Re: How to parse complex empty elements [message #639811 is a reply to message #639777] Thu, 16 July 2015 10:25 Go to previous messageGo to next message
Manav8901
Messages: 2
Registered: July 2015
Junior Member
Thanks Michel! Appreciate your help.

This is just small example I gave. I am using DOM parser to read the data. Some how empty elements of DEPT is not reading by DOM parser.

In real world I have a 15MB file with multiple child nodes and 200 elements total to parse. I need to parse all 200 elements from file and load into the Oracle table for validation.

Process is as follow

1) I am going to get file on Unix server
2) I will put the file in CLOB column in staging table.
3) Now I have to read elements from CLOB using DOM parser or some other method.

Question is If I am 200 elements to read then what are the steps if i have to use XML table?

Thanks
Manav
Re: How to parse complex empty elements [message #639812 is a reply to message #639811] Thu, 16 July 2015 10:39 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you know the structure of your XML (that is all nodes and elemnts) then you can use XMLTABLE in the same way (and then name your 200 elements).
There are plenty of examples in this forum (including some loading directly a file containing XML into a relational table).
Search for XMLTABLE (and BFILENAME for direct load).

If you can't achieve it, post an example of your file (attach it) and where you are stuck.

Previous Topic: What are the options for getting data in XML format using SQL queries?
Next Topic: XMLTABLE
Goto Forum:
  


Current Time: Thu Mar 28 08:09:08 CDT 2024