Get all the XML file names from a directory in a PL/SQL procedure

Question

I have a procedure that takes a XML filename which is on local server directory and extract the desired data into oracle table. New XML files are constantly being added to that directory. I would like to automate the process and get all the filenames to feed into my procedure so the data gets populated into the tables automatically.

Below is my code, could anyone please suggest on how can I automate the process.


DECLARE

acct_doc xmltype := xmltype( bfilename('AUTOACCEPT_XMLDIR','HighSchoolTranscript 20191202173006-1.xml'), nls_charset_id('AL32UTF8') );  -- Change the file name to get different students

BEGIN

 insert into s7303786.syvhscs (syvhscs_code, syvhscs_crse_title, syvhscs_hsct_code, syvhscs_credit_hours, syvhscs_hspt_code, syvhscs_level, syvhscs_end_date, syvhscs_smu_accept, syvhscs_hsag_code, syvhscs_user_id, syvhscs_activity_date)
 select z.syvhscs_code
     , z.syvhscs_crse_title
     , SUBSTR(z.syvhscs_hsct_code, -2,1) AS syvhscs_hsct_code   -- Extracted from AgencyCourseID
     , z.syvhscs_credit_hours                                   
     , SUBSTR(z.syvhscs_hspt_code, -1) AS syvhscs_hspt_code     -- Etracted from AgencyCourseID
     , REGEXP_SUBSTR(z.syvhscs_level, '(\d)(\d)')               --Extracted from CourseTitle (two digit number)
     , z.syvhscs_end_date
     , z.syvhscs_smu_accept
     , x.syvhscs_hsag_code
     , y.syvhscs_user_id
     , x.syvhscs_activity_date
 from XMLTable(
       xmlnamespaces('urn:org:pesc:message:HighSchoolTranscript:v1.5.0' as "HSTrn"), -- remove if you don't have XMLNAMESPACE in the root node
       '/HSTrn:HighSchoolTranscript'  --  root node name
       passing acct_doc

       columns
           syvhscs_hsag_code     varchar2(100)              path  'TransmissionData/Source/Organization/PSIS'
         , syvhscs_activity_date timestamp with time zone   path  'TransmissionData/CreatedDateTime'
         -- other columns
         , student               xmltype                    path  'Student'

   ) x
   , xmltable(
       '/Student'
       passing x.student
       columns
          syvhscs_user_id       varchar2(100) path  'Person/RecipientAssignedID'
          -- other columns
          , course                xmltype       path  'AcademicRecord/AcademicSession/Course'
   ) y
   , XMLTable(
       '/Course'
       passing y.course
       columns
         syvhscs_code         varchar2(100) path  'AgencyCourseID',
         syvhscs_crse_title   varchar2(100) path  'CourseTitle',
         syvhscs_hsct_code    varchar2(100) path  'AgencyCourseID',
         syvhscs_credit_hours number        path  'CourseCreditValue',
         syvhscs_hspt_code    varchar2(100) path  'AgencyCourseID',
         syvhscs_level        varchar2(100) path  'CourseTitle',
         syvhscs_end_date     timestamp     path  'CourseEndDate',
         syvhscs_smu_accept   varchar2(100) path  'AgencyCourseID'
   ) z;

END;

score 0 · Answer 1 · answered Feb 20 '20 at 20:26

I don't want to program the whole process for you now. but here is my suggestion how you can do it

first you need a functionality to read all files in directory check the article on asktom
next you can implement a procedure that call that java function and iterate through the result.
in the loop you can call for each file your procedure that parse your xml files
If you what to automate the process, you can create a scheduler job in the database. just check the question an answers here on SO:
- Create oracle scheduler job
- Create oracle scheduler job which runs daily

I hope I did not forget anything

Wouldn't using EXTERNAl TABLE be a way easier approach? Could you suggest on how to use external table to get the file names? Thank you — shubham khulbe, Feb 24 '20 at 17:43

Get all the XML file names from a directory in a PL/SQL procedure

1 Answers1