Can you suggest how can I handle special characters in XML file like below line is throwing error while executing the procedure
<MIDDLE_NAME>kiÑgsuk</MIDDLE_NAME>
Error Message :
LPX-00217: invalid character 53607 (U+D167)
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at "INTEGMGR.WORKER_XML_UPLOAD2018_P", line 3
ORA-06512: at line 2
Process exited.
Disconnecting from the database
Here is my procedure:
CREATE OR REPLACE PROCEDURE WORKER_XML_UPLOAD2018_P AS
acct_doc xmltype := xmltype( bfilename('UPLOAD_DIR','file_km_issue.xml'), nls_charset_id('utf-8') );
BEGIN
insert into WORKER_XML_UPLOAD2018 (WORKER_WID, WORKER_ID,LAST_NAME ,LEGAL_FIRST_NAME ,PREF_FIRST_NAME ,MIDDLE_NAME ,SUFFIX ,TITLE ,DATE_OF_BIRTH ,EXT_EMAIL_ADDRESS ,START_DATE,
TERMINATION_DATE , FULL_PART_TIME, NETID_ELIGIBLE ,DIVISION, ACAD_DEPT, FIN_DEPT, HR_DEPT, PHONE, MOBILE_PHONE, OFFICE_BUILDING, OFFICE_BUILDING_ID,
OFFICE_BUILDING_CODE , OFFICE_ROOM ,FAX , SSN , CWID , GENDER ,STATUS, FACULTY_INDICATOR)
select *
from xmltable(
'/HRS-INT063B-OUTPUT/WORKER'
passing acct_doc
columns WORKER_WID VARCHAR2(50) path 'WORKER_WID',
WORKER_ID NUMBER path 'WORKER_ID',
LAST_NAME VARCHAR2(50) path 'LAST_NAME',
LEGAL_FIRST_NAME VARCHAR2(50) path 'LEGAL_FIRST_NAME',
PREF_FIRST_NAME VARCHAR2(50) path 'PREF_FIRST_NAME',
MIDDLE_NAME VARCHAR2(50) path 'MIDDLE_NAME',
SUFFIX VARCHAR2(50) path 'SUFFIX',
TITLE VARCHAR2(50) path 'TITLE',
DATE_OF_BIRTH DATE path 'DATE_OF_BIRTH',
EXT_EMAIL_ADDRESS VARCHAR2(50) path 'EXT_EMAIL_ADDRESS',
START_DATE DATE path 'START_DATE',
TERMINATION_DATE DATE path 'TERMINATION_DATE',
FULL_PART_TIME VARCHAR2(50) path 'FULL_PART_TIME',
NETID_ELIGIBLE VARCHAR2(50) path 'NETID_ELIGIBLE',
DIVISION VARCHAR2(50) path 'DIVISION',
ACAD_DEPT VARCHAR2(50) path 'ACAD_DEPT',
FIN_DEPT VARCHAR2(50) path 'FIN_DEPT',
HR_DEPT VARCHAR2(50) path 'HR_DEPT',
PHONE VARCHAR2(50) path 'PHONE',
MOBILE_PHONE VARCHAR2(50) path 'MOBILE_PHONE',
OFFICE_BUILDING VARCHAR2(50) path 'OFFICE_BUILDING',
OFFICE_BUILDING_ID VARCHAR2(50) path 'OFFICE_BUILDING_ID',
OFFICE_BUILDING_CODE VARCHAR2(50) path 'OFFICE_BUILDING_CODE',
OFFICE_ROOM VARCHAR2(50) path 'OFFICE_ROOM',
FAX VARCHAR2(50) path 'FAX',
SSN VARCHAR2(50) path 'SSN',
CWID NUMBER path 'CWID',
GENDER VARCHAR2(50) path 'GENDER',
STATUS VARCHAR2(50) path 'STATUS',
FACULTY_INDICATOR VARCHAR2(10) path 'FACULTY_INDICATOR'
);
insert into POSITION_XML_UPLOAD2018 (WORKER_ID , POS_TITLE, POS_ID , POS_FAMILY_GROUP , IS_PRIMARY , POS_MANAGER , POS_HR_DEPT , POS_DIVISION )
select t1.WORKER_ID, t2.POS_TITLE, t2.POS_ID , t2.POS_FAMILY_GROUP , t2.IS_PRIMARY , t2.POS_MANAGER , t2.POS_HR_DEPT , t2.POS_DIVISION
from xmltable (
'/HRS-INT063B-OUTPUT/WORKER'
passing acct_doc
columns WORKER_ID NUMBER path 'WORKER_ID',
positions xmltype path 'JOB_POSITIONS'
) t1,
xmltable(
'/JOB_POSITIONS/POSITION'
passing t1.positions
columns POS_TITLE VARCHAR2(50) path 'POS_TITLE',
POS_ID VARCHAR2(50) path 'POS_ID',
POS_FAMILY_GROUP VARCHAR2(50) path 'POS_FAMILY_GROUP',
IS_PRIMARY VARCHAR2(50) path 'IS_PRIMARY',
POS_MANAGER VARCHAR2(50) path 'POS_MANAGER',
POS_HR_DEPT VARCHAR2(50) path 'POS_HR_DEPT',
POS_DIVISION VARCHAR2(50) path 'POS_DIVISION'
) t2;
commit;
END WORKER_XML_UPLOAD2018_P;