# SAMPLE XML File content is shown below.
XMLFILE_AA_11.xml
<?xml version="1.0" encoding="UTF-8"?>
<EMPDATA TIME_STAMP="2006-08-07T15:00:42"
TRANSACTION_ID="2006-08-07T15:00:42" VERSION="1.0">
<HEADER>
<SENDER>
<SENDER_NAME>XYZ Corp</SENDER_NAME>
</SENDER>
<RECEIVER>
<RECEIVER_NAME>ABC Bank</RECEIVER_NAME>
</RECEIVER>
</HEADER>
<BODY>
<EMP>
<EMPNUM>12345678</EMPNUM>
<DEPT>07I7</DEPT>
<LOCATION>22</LOCATION>
<HIRE_DATE>2006-08-07 00:12:00.0</HIRE_DATE>
<FIRST_NAME>JOHN</FIRST_NAME>
<LAST_NAME>SMITH</LAST_NAME>
<PHONES>
<PHONE type="Office">5676739</PHONE>
<PHONE type="Office">9123412432</PHONE>
</PHONES>
<AC_NO>12313-123123-1233</AC_NO>
<AMOUNT>2500.00</AMOUNT>
</EMP>
<EMP>
<EMPNUM>12345679</EMPNUM>
<DEPT>07I2</DEPT>
<LOCATION>22</LOCATION>
<HIRE_DATE>2006-08-17 00:12:00.0</HIRE_DATE>
<FIRST_NAME>GREG</FIRST_NAME>
<LAST_NAME>WINTER</LAST_NAME>
<PHONES>
<PHONE type="Office">5676733</PHONE>
<PHONE type="Office">9123434231</PHONE>
</PHONES>
<AC_NO>12313-123144_4354</AC_NO>
<AMOUNT>2800.00</AMOUNT>
</EMP>
</BODY>
</EMPDATA>
step 1
# create directory (location of xml file)
sqlplus / as sysdba
create directory TEST_DIR as '/ora01/oracle/admin/demo/temp/';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO scott;
step 2
# Check contents of file
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual;
step 3
# select few columns only - empnum, ac_no and amount. Remaining tags are not selected
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/
step 4
# insert selected few columns only - empnum, ac_no and amount in table named employee. Remaining tags are not selected
create table employee (
emp_num varchar2(10),
acct_num varchar2(10),
amount number
)
/
insert into employee (emp_num, acct_num, amount)
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/
commit;
XMLFILE_AA_11.xml
<?xml version="1.0" encoding="UTF-8"?>
<EMPDATA TIME_STAMP="2006-08-07T15:00:42"
TRANSACTION_ID="2006-08-07T15:00:42" VERSION="1.0">
<HEADER>
<SENDER>
<SENDER_NAME>XYZ Corp</SENDER_NAME>
</SENDER>
<RECEIVER>
<RECEIVER_NAME>ABC Bank</RECEIVER_NAME>
</RECEIVER>
</HEADER>
<BODY>
<EMP>
<EMPNUM>12345678</EMPNUM>
<DEPT>07I7</DEPT>
<LOCATION>22</LOCATION>
<HIRE_DATE>2006-08-07 00:12:00.0</HIRE_DATE>
<FIRST_NAME>JOHN</FIRST_NAME>
<LAST_NAME>SMITH</LAST_NAME>
<PHONES>
<PHONE type="Office">5676739</PHONE>
<PHONE type="Office">9123412432</PHONE>
</PHONES>
<AC_NO>12313-123123-1233</AC_NO>
<AMOUNT>2500.00</AMOUNT>
</EMP>
<EMP>
<EMPNUM>12345679</EMPNUM>
<DEPT>07I2</DEPT>
<LOCATION>22</LOCATION>
<HIRE_DATE>2006-08-17 00:12:00.0</HIRE_DATE>
<FIRST_NAME>GREG</FIRST_NAME>
<LAST_NAME>WINTER</LAST_NAME>
<PHONES>
<PHONE type="Office">5676733</PHONE>
<PHONE type="Office">9123434231</PHONE>
</PHONES>
<AC_NO>12313-123144_4354</AC_NO>
<AMOUNT>2800.00</AMOUNT>
</EMP>
</BODY>
</EMPDATA>
step 1
# create directory (location of xml file)
sqlplus / as sysdba
create directory TEST_DIR as '/ora01/oracle/admin/demo/temp/';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO scott;
step 2
# Check contents of file
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual;
# select few columns only - empnum, ac_no and amount. Remaining tags are not selected
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/
step 4
# insert selected few columns only - empnum, ac_no and amount in table named employee. Remaining tags are not selected
create table employee (
emp_num varchar2(10),
acct_num varchar2(10),
amount number
)
/
insert into employee (emp_num, acct_num, amount)
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/
commit;
