时间戳

首页 > 解决方案 > 从 PL/SQL 过程中的目录中获取所有 XML 文件名

plsql - 从 PL/SQL 过程中的目录中获取所有 XML 文件名

问题描述

我有一个过程，它采用本地服务器目录上的 XML 文件名，并将所需的数据提取到 oracle 表中。新的 XML 文件不断被添加到该目录中。我想自动化该过程并将所有文件名输入我的程序，以便数据自动填充到表中。

下面是我的代码，任何人都可以建议我如何自动化这个过程。


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;

标签： plsql

解决方案

我现在不想为你编写整个过程。但这是我的建议

我希望我没有忘记任何事情

推荐阅读