基于Oracle XML类型数据，SQL结合XPath查询双重身份人员姓名

首先得提一下，你提供的XML片段存在语法错误，这会导致Oracle无法正确解析它：

• 标签名不能包含空格，<swimmer name>是无效的XML标签，应该改成类似<swimmer>John Goldrick</swimmer>或者<swimmer name="John Goldrick"/>这种合法格式
• 标签嵌套错误，<teacher>标签里错误闭合了</swimmer>，导致XML结构不匹配

我先假设修正后的合法XML结构（最贴近你原意的版本），比如：

<swim_school>
  <swimmers>
    <swimmer>John Goldrick</swimmer>
    <swimmer>James John</swimmer>
  </swimmers>
  <teachers>
    <teacher>John Goldrick</teacher>
  </teachers>
</swim_school>

接下来，你可以通过Oracle的XMLTable结合XPath或者SQL join来实现查询，这里提供两种常用方法：

方法1：直接用XPath筛选交集 #

利用XPath的条件判断，直接找到同时存在于<teacher>节点中的<swimmer>节点：

SELECT DISTINCT person_name
FROM xml_data, -- 假设你的表名为xml_data
     XMLTable(
       '//swimmer[. = //teacher]' 
       PASSING xml_col -- 假设XMLType列名为xml_col
       COLUMNS person_name VARCHAR2(100) PATH '.'
     );

这个XPath表达式//swimmer[. = //teacher]的意思是：找到所有文本内容等于任意一个<teacher>节点文本内容的<swimmer>节点。

方法2：拆分XML节点为关系表后做Join #

先把<swimmer>和<teacher>节点分别转换成关系型的行，再通过SQL的Join找到交集：

SELECT s.person_name
FROM xml_data,
     XMLTable(
       '//swimmer' 
       PASSING xml_col 
       COLUMNS person_name VARCHAR2(100) PATH '.'
     ) s
JOIN XMLTable(
       '//teacher' 
       PASSING xml_col 
       COLUMNS person_name VARCHAR2(100) PATH '.'
     ) t
ON s.person_name = t.person_name;

这种方法更灵活，如果后续需要扩展筛选条件（比如加上人员ID、年龄等），修改起来更方便。

两种方法最终都会返回John Goldrick，也就是同时是游泳者和教师的人员姓名。

内容的提问来源于stack exchange，提问作者Colin Jack