I have a XML file in below format.
Now I would like to store this XML file into Oracle database table.
Please let me know how to implement the logic in PLSQL.
My test scripts are below.
CREATE TABLE FileDetails
(
file_Id INTEGER,
xml_data CLOB,
filename VARCHAR2(200),
Insert_date DATE,
file_location VARCHAR2(200),
valid_flag VARCHAR2(1),
CONSTRAINT FileDetails_id PRIMARY KEY (file_Id)
);
CREATE SEQUENCE FileDetails_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE DIRECTORY xml_file_directory AS 'G:\xml_directory\';
CREATE TABLE Error_log
( errmsg VARCHAR2(2000), Ins_DATE DATE
);
CREATE TABLE Employees
(
EMPNO NUMBER(10),
ENAME VARCHAR2(400),
DEPTNO NUMBER(10)
);
create or replace PACKAGE myPackage
IS
FUNCTION readXML(
FileNameIn IN VARCHAR2)
RETURN PLS_INTEGER;
PROCEDURE writeLog(
MessageIN IN VARCHAR2);
FUNCTION parseData(
IdIn IN INTEGER)
RETURN PLS_INTEGER;
END;
---My package body implementation as below
create or replace PACKAGE BODY myPackage
IS
STD_DEF_DATE VARCHAR2(10):='dd/mm/yyyy';
FUNCTION readXML(
FileNameIn IN VARCHAR2)
RETURN PLS_INTEGER
IS
dest_clob CLOB;
src_clob BFILE := BFILENAME('G:\xml_dir\XML_FILE_DIRECTORY', 'EMPLOYEE.xml'); --from the LOCAL (Oracle instance) filesystem
dst_offset NUMBER := 1 ;
src_offset NUMBER := 1 ;
lang_ctx NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
warning NUMBER;
l_id FileDetails.file_Id%type;
l_parseDataRes PLS_INTEGER;
BEGIN
SAVEPOINT svp_ini;
--get a new id from our sequence
SELECT FileDetails_seq.NEXTVAL
INTO l_id
FROM DUAL;
--put some data in the table along an empty CLOB which we'll fill later
INSERT
INTO FileDetails
(
file_Id,
xml_data,
filename,
Insert_date,
file_location
)
VALUES
(
l_id,
empty_clob(),
FileNameIn,
SYSDATE,
'G:\xml_directory\XML_FILE_DIRECTORY'
)
RETURNING xml_data
INTO dest_clob;
--now open our file in read mode
DBMS_LOB.OPEN(src_clob, DBMS_LOB.LOB_READONLY);
--and copy its contents in our CLOB
DBMS_LOB.LOADCLOBFROMFILE(dest_lob => dest_clob, src_bfile => src_clob, amount => DBMS_LOB.GETLENGTH(src_clob), DEST_OFFSET => dst_offset , SRC_OFFSET => src_offset, BFILE_CSID => DBMS_LOB.DEFAULT_CSID, LANG_CONTEXT => lang_ctx, WARNING => warning);
--remember to close the file!
DBMS_LOB.CLOSE(src_clob);
COMMIT;
l_parseDataRes:=parseData(l_id);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_LOB.ISOPEN(src_clob) = 1 THEN--if the file is still open close it!
DBMS_LOB.CLOSE(src_clob);
END IF;
--log the event
writelog('Error: '||SQLERRM||' while reading '||FileNameIn);
ROLLBACK TO svp_ini;
RETURN 0;
END readXML;
PROCEDURE writeLog
(
MessageIN IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION; --to prevent unwanted commits!!!
BEGIN
INSERT INTO Error_log VALUES
(MessageIn, SYSDATE
);
COMMIT;
END writeLog;
FUNCTION parseData
(
IdIn IN INTEGER
)
RETURN PLS_INTEGER
IS
l_XML XMLTYPE;
l_XML_content CLOB;
l_empno employees.empno%type;
l_ename employees.ename%type;
--l_otherData employees.JOB%type;
l_deptno employees.DEPTNO%type;
l_employeesId PLS_INTEGER;
-- l_countSimpleFilter PLS_INTEGER;
BEGIN
--get the XML
SELECT xml_data
INTO l_XML_content
FROM FileDetails
WHERE file_Id = IdIn;
l_XML := XMLType.CreateXML (l_XML_content);
--loop on our XML
FOR myCur IN
(SELECT ROWSET."EMPNO",
ROWSET."ENAME",
ROWSET."DEPTNO" --note the "" around the column names!
FROM XMLTABLE('//ROWSET' --creates a temporary table from a given XML with the structure specified
PASSING l_XML COLUMNS "empno" INTEGER PATH '//ROW/empno',
/*path defines the full (starting with a single /) or relative
(starting with a double //) path where the data is located inside the XML. It would be equivalent to '/myRoot/ROWSET/ROW/empno' */
"ename" VARCHAR2(200) PATH '//ROW/ename/text()', --we use text() since it's a CDATA value
"deptno" INTEGER PATH '//ROW/deptno' ) ROWSET
)
LOOP --we are now looping on each ROWSET element inside our XML
BEGIN
SAVEPOINT svp_parsedata;
l_empno:=myCur."empno";
l_ename:=myCur."ename";
INSERT
INTO employees
(
EMPNO,
ENAME,
DEPTNO
)
VALUES
(
l_empno,
l_ename,
l_deptno
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
writeLog('Error: '||SQLERRM||' while parsing data');
ROLLBACK TO svp_parsedata;
END;
END LOOP;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
writeLog('Error: '||SQLERRM||' while reading from XML');
RETURN 0;
END parseData;
END myPackage;
Im getting below error messages while compile my package body.
Please help.