I've got some problems dealing with loading a nested tags XML file.
Let's suppose I have such a very simple myxml.xml file:
<ROWDATA>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
</ROW>
<ROW>
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
</ROW>
</ROWDATA>
I can create the following table:
create table EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
);
and then inserting the XML file in this way:
insert into EMP
(empno, ename)
select extractvalue (column_value, '/ROW/EMPNO'),
extractvalue (column_value, '/ROW/ENAME'),
from table
(xmlsequence
(extract
(xmltype
(bfilename ('MY_DIR', 'myxml.xml'),
nls_charset_id ('AL32UTF8')),
'/ROWDATA/ROW')))
;
so as to get inserted two rows into my table:
EMPNO ENAME
7369 SMITH
7902 FORD
Now, and this is my question, let's suppose I have such a “more difficult” XML:
<ROWDATA>
<ROW>
<COMPANY>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<EMPNO>1111</EMPNO>
<ENAME>TAYLOR</ENAME>
</COMPANY>
</ROW>
<ROW>
<COMPANY>
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
</COMPANY>
</ROW>
<ROW>
<COMPANY>
...
…
...
</COMPANY>
</ROW>
</ROWDATA>
In this case it seems to me things look harder 'cause for every row that I should insert into my table, I don't know how many “empno” and “ename” I'll find for each /ROW/COMPANY and so how could I define a table since the number of empno and ename columns are “unknown”?
According to you, in that case should I load the whole XML file in an unique XMLType column and than “managing” its content by using EXTRACT and EXTRACTVALUE built-in funcions? But this looks a very difficult job.
My Oracle version is 10gR2 Express Edition
Thanks in advance!