SQL & PL/SQL

Hi,

I have table contains BLOB column and it contains data into XML format(BUFFER_BUF).

COLUMN contains data in following format:

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

<invoice>

<EMPINFO elem="0">

<EMPID>7000243440</EMPID>

<EMPFNAME>Smith</EMPFNAME>

<EMPLNAME>John</EMPLNAME>

<SAL>690987</SAL>

<MGRID>7000243550</MGRID>

<CONTACT>9856325471</CONTACT>

<EMAIL>smith.j</EMAIL>

<ADDR>Orlando</ADDR>

</EMPINFO>

<ADDRESS_INFO elem="0">

<COMPANY></COMPANY>

<CITY>Orlando</CITY>

<COUNTRY>US</COUNTRY>

<STATE>FL</STATE>

<ZIP>B1 1BA</ZIP>

<STREET_NAME>Holloway Circus Queensway</STREET_NAME>

<DISTRICT></DISTRICT>

<STREET_NUMBER>10</STREET_NUMBER>

<FLAT_NUMBER></FLAT_NUMBER>

<HOUSE_NAME></HOUSE_NAME>

</ADDRESS_INFO>

<DEPART_INFO elem="0">

<DEPTID>40</DEPTID>

<DNAME>Orlando</DNAME>

<COUNTRY>US</COUNTRY>

<STATE>FL</STATE>

<LOCATION></LOCATION>

</DEPART_INFO>

.

.

.

.

</invoice>

now , each row has different xml.

My requirement is I have to read data from each row of BLOB column and transform it into a RDBMS table.

I used following SQL statement to read data from each tab from xml within BLOB and now I m successfully read data from BLOL column:

select OBJ_ID0, REC_ID,

xmltype(BUFFER_BUF,871).extract('invoice/EMPINFO/EMPID/text()').getstringval() as EMPID,

xmltype(BUFFER_BUF,871).extract('invoice/EMPINFO/EMPFNAME/text()').getstringval() as EMPFNAME,

xmltype(BUFFER_BUF,871).extract('invoice/EMPINFO/EMPLNAME/text()').getstringval() as EMPLNAME,

xmltype(BUFFER_BUF,871).extract('invoice/EMPINFO/sal/text()').getstringval() as sal from INVOICE_FORMATS_BUF_T;

problem is, In my BLOB column contains 3000 xml tags and to read data from each tag it requires to right 3000 SELECT statements.

So is there any other approach through that I can read all xml data at once and directly insert into tables instead of writing 3000 SQL.

Thanks