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  |
 |
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  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #639812 is a reply to message #639811] |
Thu, 16 July 2015 10:39 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:04:24 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
