Блог

У меня есть журнал FTP в формате XML, хранящийся в столбце CLOB, и мне нужно получить имена файлов, которые он нашел и извлек. Используя Table/Lateral вместе с REGEX_SUBSTR, я могу получить то, что мне нужно для файлов с ИЗВЕСТНЫМ количеством, но я не могу понять, как обрабатывать неизвестное количество файлов. Существует поле, которое возвращает количество найденных файлов lt;FilesProcessedgt;3lt;/FilesProcessedgt; . Есть ли способ использовать это поле для анализа имен файлов? или , что более важно, есть ли просто лучший способ сделать это?

(Я использую CTE здесь, но я вытащу это из таблицы БД (с неприличным количеством столбцов, но без имен файлов!).

 With PAYLOAD_DATA(LOGS) as( VALUES ('lt;?xml version="1.0"?gt; lt;InboundMFTEventDetailsDocumentgt;  lt;MFTEventExecutionDetailsgt;  lt;Statusgt;Successfullt;/Statusgt;  lt;FilesProcessedgt;3lt;/FilesProcessedgt;  lt;MFTEventLogIDgt;5dn39m00fgmdefo80002g7kilt;/MFTEventLogIDgt;  lt;ExecutionLogsgt;  lt;Logsgt;Finding file(s) in VFS Path:/Wholesale/CS/Inbound/SFTP/PROD-OUT-Shipment/, URL:SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/.   Filename Filter = INVPTH*.txt  Found following 3 file(s).  SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH033020210320006396.txt  SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH033020210320009986.txt  SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH092720210320009986.txt  lt;/Logsgt;  lt;Logsgt;Starting copy of file(s) from VFS Path:/Wholesale/CS/Inbound/SFTP/PROD-OUT-Shipment/, URL:SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH033020210320006396.txt to VFS Path:/Wholesale/RLM/Outbound/SFTP/SERVERSERVICES-INBOUND-SHIPH/, URL:SFTP://MKWHLDV.kors.local:22/SERVERSERVICES/INBOUND/SHIPH/INVPTH033020210320006396.txt Copy finished:VFS Path:/Wholesale/CS/Inbound/SFTP/PROD-OUT-Shipment/, URL:SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH033020210320006396.txt lt;/Logsgt;  lt;Logsgt;...lt;/Logsgt;  lt;Logsgt;...lt;/Logsgt;  lt;Logsgt;...lt;/Logsgt;  lt;/ExecutionLogsgt;  lt;/MFTEventExecutionDetailsgt; lt;/InboundMFTEventDetailsDocumentgt;'))  SELECT FILENAME FROM PAYLOAD_DATA A, TABLE (VALUES (REGEXP_SUBSTR(LOGS, ' SFTP://.*[rn][ |lt;]', 1, REGEXP_COUNT(LOGS, ' SFTP://.*[rn][ |lt;]') - 0)),  (REGEXP_SUBSTR(LOGS, ' SFTP://.*[rn][ |lt;]', 1, REGEXP_COUNT(LOGS, ' SFTP://.*[rn][ |lt;]') - 1)),  (REGEXP_SUBSTR(LOGS, ' SFTP://.*[rn][ |lt;]', 1, REGEXP_COUNT(LOGS, ' SFTP://.*[rn][ |lt;]') - 2)),  (REGEXP_SUBSTR(LOGS, ' SFTP://.*[rn][ |lt;]', 1, REGEXP_COUNT(LOGS, ' SFTP://.*[rn][ |lt;]') - 3))) as G(FILENAME)  

 SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH092720210320009986.txt  SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH033020210320009986.txt  SFTP://ftp.some_server.com:22/UAT/OUT/Shipment/INVPTH033020210320006396.txt  

Может быть, что-то вроде

 With PAYLOAD_DATA(LOGS) as( ... ) occurrences (occ) as (values 1 union all select occ   1 from occurrences where occ lt; (SELECT REGEXP_COUNT(LOGS, ' SFTP://.*[rn][ |lt;]') FROM PAYLOAD_DATA)) select REGEXP_SUBSTR(LOGS, ' (SFTP://.*)[rn][ |lt;]', 1, occ, '', 1) filename from PAYLOAD_DATA, occurrences