如何使用REGEX_SUBSTR从XML格式CLOB存储的FTP日志提取未知数量文件名

方案说明 #

1. 完全可以借助<FilesProcessed>字段辅助解析，更推荐优先用该字段：它是日志明确上报的实际处理文件数量，以它作为提取上限，能避免提取到后续复制操作日志里重复出现的同个文件路径，比单纯统计正则匹配数量准确性更高。
2. 更优的实现方案无需硬编码待提取的文件数量，通过动态生成匹配序号的方式兼容任意数量的文件提取，示例代码如下（以PostgreSQL为例，其他数据库仅需替换序列生成函数即可）：

WITH PAYLOAD_DATA(LOGS) AS (
VALUES
('<?xml version="1.0"?>
<InboundMFTEventDetailsDocument>
  <MFTEventExecutionDetails>
    <Status>Successful</Status>
    <FilesProcessed>3</FilesProcessed>
    <MFTEventLogID>5dn39m00fgmdefo80002g7ki</MFTEventLogID>
    <ExecutionLogs>
      <Logs>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
      </Logs>
      <Logs>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
</Logs>
      <Logs>...</Logs>
      <Logs>...</Logs>
      <Logs>...</Logs>
    </ExecutionLogs>
  </MFTEventExecutionDetails>
</InboundMFTEventDetailsDocument>')
),
-- 提取元数据：解析日志上报的已处理文件数
log_meta AS (
    SELECT 
        LOGS,
        -- 从XML节点直接提取文件数，和日志上报结果严格对齐
        XMLCAST(XMLQUERY('//FilesProcessed/text()' PASSING XMLPARSE(DOCUMENT LOGS) RETURNING CONTENT) AS INTEGER) AS file_count
    FROM PAYLOAD_DATA
)
-- 动态生成1到file_count的序列，逐个提取对应序号的文件名
SELECT 
    TRIM(REGEXP_SUBSTR(LOGS, 'SFTP://[^<>\s]+', 1, seq.num)) AS FILENAME
FROM log_meta
CROSS JOIN GENERATE_SERIES(1, log_meta.file_count) AS seq(num)
WHERE REGEXP_SUBSTR(LOGS, 'SFTP://[^<>\s]+', 1, seq.num) IS NOT NULL;

如果你的数据库不支持XML解析，也可以直接用正则统计匹配到的SFTP路径数量代替文件数，同样可以实现动态提取，无需硬编码：

WITH PAYLOAD_DATA(LOGS) AS (
-- 此处替换为你实际的表查询逻辑
),
log_meta AS (
    SELECT 
        LOGS,
        REGEXP_COUNT(LOGS, 'SFTP://[^<>\s]+') AS file_count
    FROM PAYLOAD_DATA
)
SELECT 
    TRIM(REGEXP_SUBSTR(LOGS, 'SFTP://[^<>\s]+', 1, seq.num)) AS FILENAME
FROM log_meta
CROSS JOIN GENERATE_SERIES(1, log_meta.file_count) AS seq(num);

适配说明 #

如果使用Db2等不支持GENERATE_SERIES的数据库，替换为对应数据库的序列生成语法即可，比如Db2可以用以下方式生成序列：

CROSS JOIN TABLE(SELECT 1 + SEQ4() AS num FROM SYSIBM.SYSDUMMY1 CONNECT BY SEQ4() < log_meta.file_count) AS seq(num)

内容的提问来源于stack exchange，提问作者Lloyd Prendergast