Hi
I am trying to read XML file (directly from a directory) into rows and columns using SQL query.
but I do not get any data or errors .I am new to XML, so please do not get frustrated.
would you help me figuring out where is the problem.
--create table employees_test as select * from employees where 1=0;
--CREATE OR REPLACE directory xmlstore AS 'C:\Temp';
DECLARE
xml_file BFILE;
xml_data CLOB;
BEGIN
xml_file := BFILENAME ('XMLSTORE', 'employees.xml');
DBMS_LOB.createtemporary (xml_data, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.fileopen (xml_file, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (xml_data, xml_file, DBMS_LOB.getlength(xml_file));
DBMS_LOB.fileclose (xml_file);
INSERT INTO employees_test (EMPLOYEE_ID,FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
SELECT xt.*
FROM XMLTABLE('EMPLOYEE/EMPLOYEE_ID'
PASSING (XMLType(bfilename('XMLSTORE', 'EMPLOYEES.xml'),nls_charset_id('AL32UTF8')))
COLUMNS
EMPLOYEE_ID number(6) PATH 'EMPLOYEE_ID',
FIRST_NAME varchar2(20) PATH 'FIRST_NAME',
LAST_NAME varchar2(25) PATH 'LAST_NAME',
EMAIL varchar2(25) PATH 'EMAIL' ,
PHONE_NUMBER varchar2(20) PATH 'PHONE_NUMBER' ,
HIRE_DATE date PATH 'HIRE_DATE',
JOB_ID varchar2(20)PATH 'JOB_ID',
salary number (8,2) PATH 'SALARY',
COMMISSION_PCT number (6) PATH 'COMMISSION_PCT',
MANAGER_ID NUMBER (6) PATH 'MANAGER_ID',
DEPARTMENT_ID number (4) PATH 'DEPARTMENT_ID' ) xt;
DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' rows inserted.' );
DBMS_LOB.freetemporary (xml_data);
COMMIT;
END;
/
the following xml is saved on an xml file named employees.xml on xmlstore directory :
<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
<EMPLOYEE_ID>100</EMPLOYEE_ID><FIRST_NAME>Steven</FIRST_NAME><LAST_NAME>King</LAST_NAME><EMAIL>SKING</EMAIL><PHONE_NUMBER>515.123.4567</PHONE_NUMBER><HIRE_DATE>2003-06-17</HIRE_DATE><JOB_ID>AD_PRES</JOB_ID><SALARY>24000</SALARY><DEPARTMENT_ID>90</DEPARTMENT_ID>
<EMPLOYEE_ID>100</EMPLOYEE_ID><FIRST_NAME>Steven</FIRST_NAME><LAST_NAME>King</LAST_NAME><EMAIL>SKING</EMAIL><PHONE_NUMBER>515.123.4567</PHONE_NUMBER><HIRE_DATE>2003-06-17</HIRE_DATE><JOB_ID>AD_PRES</JOB_ID><SALARY>24000</SALARY><DEPARTMENT_ID>90</DEPARTMENT_ID>
<EMPLOYEE_ID>100</EMPLOYEE_ID><FIRST_NAME>Steven</FIRST_NAME><LAST_NAME>King</LAST_NAME><EMAIL>SKING</EMAIL><PHONE_NUMBER>515.123.4567</PHONE_NUMBER><HIRE_DATE>2003-06-17</HIRE_DATE><JOB_ID>AD_PRES</JOB_ID><SALARY>24000</SALARY><DEPARTMENT_ID>90</DEPARTMENT_ID>
</EMPLOYEE>