如何在Oracle SQL中查询CLOB内存在重复XML值的产品ID

要从PRODUCT表中筛选出CLOB字段内XML包含重复产品的PR_ID（重复判定规则：productType、status、location三者完全相同），可以通过XML解析+分组统计的方式实现，以下是针对Oracle数据库的SQL实现方案：

核心SQL语句 #

SELECT DISTINCT pr_id
FROM PRODUCT,
     XMLTABLE(
         '/productInfo/product'
         PASSING XMLTYPE(your_clob_column) -- 替换为实际存储XML的CLOB字段名
         COLUMNS
             productType VARCHAR2(50) PATH 'productType',
             status VARCHAR2(20) PATH 'status',
             location VARCHAR2(100) PATH 'location'
     ) x
GROUP BY pr_id, productType, status, location
HAVING COUNT(*) > 1;

语句说明 #

1. XML解析：使用XMLTYPE()将CLOB字段转换为XML类型，再通过XMLTABLE()按照XPath路径/productInfo/product拆分每个<product>节点，提取出三个判定字段的值。
2. 分组统计：按pr_id和三个重复判定字段分组，统计每组的出现次数。
3. 筛选重复记录：通过HAVING COUNT(*) > 1筛选出同一PR_ID下存在重复产品组合的记录，最后用DISTINCT确保每个PR_ID只返回一次。

示例验证 #

针对你提供的XML示例，该SQL会返回对应的PR_ID，因为其中：

• productType=FRNC、status=ACTV、location=BASEMENT的组合出现3次
• productType=ABCD、status=ACTV、location=BASEMENT的组合出现2次
  两者均满足重复判定条件。

内容的提问来源于stack exchange，提问作者Ti J