ORA-19025错误排查：XMLTABLE查询多节点数据异常解决请求

你碰到的ORA-19025错误，本质原因是原查询里的fileID path 'document/fileID'路径会返回多个节点（比如第一个job下有2个document，第二个job下有5个），但同一行的directoryid是单节点值，Oracle无法将多值节点和单值节点直接匹配，因此触发了错误。

要实现你期望的输出（每个fileID对应一行，无fileID的job返回null），需要拆分两层遍历逻辑：先遍历每个job节点，再针对每个job单独遍历它下面的document/fileID节点（没有则生成null）。

正确的查询语句 #

SELECT 
  t.id,
  x.fileID,
  j.directoryid
FROM xml_tab t
CROSS JOIN XMLTABLE(
  '/Jobsdata/jobList/jobData/job'
  PASSING t.xml_data
  COLUMNS 
    directoryid VARCHAR2(100) PATH 'directoryid',
    job_xml XMLTYPE PATH '.'  -- 提取当前job的完整XML片段，用于后续遍历document
) j
LEFT JOIN XMLTABLE(
  '/job/document/fileID'
  PASSING j.job_xml
  COLUMNS 
    fileID VARCHAR2(100) PATH '.'
) x ON 1=1  -- 左连接保证没有document的job也会返回一行，fileID为null
ORDER BY t.id, x.fileID NULLS LAST;

逻辑说明 #

1. 第一层XMLTABLE先遍历所有job节点，获取每个job的directoryid以及整个job的XML内容（job_xml）
2. 第二层XMLTABLE针对每个job的XML片段，遍历其中的document/fileID节点
3. 使用LEFT JOIN确保当job没有任何document节点时（比如第三个job），依然会返回一行，fileID为NULL
4. ORDER BY用来对齐你期望的输出顺序

执行结果 #

ID FILEID DIRECTORYID
-- ------ -----------
4  100    D100
4  200    D200
4  201    D200
4  202    D200
4  203    D200
4  <null> D300

如果你的Oracle版本是12c及以上，也可以用OUTER APPLY简化写法，效果完全一致：

SELECT 
  t.id,
  x.fileID,
  j.directoryid
FROM xml_tab t,
XMLTABLE(
  '/Jobsdata/jobList/jobData/job'
  PASSING t.xml_data
  COLUMNS 
    directoryid VARCHAR2(100) PATH 'directoryid',
    job_xml XMLTYPE PATH '.'
) j
OUTER APPLY XMLTABLE(
  '/job/document/fileID'
  PASSING j.job_xml
  COLUMNS 
    fileID VARCHAR2(100) PATH '.'
) x
ORDER BY t.id, x.fileID NULLS LAST;

内容的提问来源于stack exchange，提问作者Asit Kumar