Oracle XML多值字段为空的SQL查询修正需求

在Oracle数据库中创建了XML类型表，其中c1是包含多值（@m标记）和子值（@s标记）的字段，存在多种空值场景。执行空值筛选时，仅含子值、主值为空的记录（RECID=18000）未被选中，需要修正查询逻辑以包含该类记录。

表结构 #

CREATE TABLE "F_TESTMV" (RECID VARCHAR2(255) NOT NULL PRIMARY KEY, XMLRECORD XMLTYPE) XMLTYPE COLUMN XMLRECORD STORE AS CLOB;
CREATE TABLE D_F_TESTMV (RECID VARCHAR2(255) NOT NULL PRIMARY KEY, XMLRECORD CLOB);

测试数据插入语句 #

INSERT INTO TAFJ24.F_TESTMV (RECID,XMLRECORD) VALUES
              ('11000',TO_CLOB(' <row id=''11000''><c1></c1><c1 m=''2''>US</c1></row>')),
              ('12000',TO_CLOB(' <row id=''12000''><c1>US</c1><c1 m=''2'></c1></row>')),
              ('13000',TO_CLOB(' <row id=''13000''><c1>GB</c1><c1 m=''2''>US</c1></row>')),
              ('14000',TO_CLOB(' <row id=''14000''><c1>US</c1><c1 m=''2''>GB</c1></row>')),
              ('15000',TO_CLOB('<row id=''15000''><c1>OF</c1><c1 m=''2'></c1></row>')),
              ('16000',TO_CLOB('<row id=''16000''><c2>gb</c2></row>')),
              ('17000',TO_CLOB('<row id=''17000''><c1>US</c1><c1 m=''1'' s=''2'></c1></row>')),
              ('18000',TO_CLOB('<row id=''18000''><c1 m=''1'' s=''2''>GB</c1></row>'));

注：原语句中存在引号未转义问题，已修正以保证可执行性

视图定义 #

CREATE OR REPLACE VIEW "V_F_TESTMV" AS
SELECT a.RECID, 
       a.XMLRECORD "THE_RECORD",
       extractValue(a.XMLRECORD,'/row/c1[position()=1]') "COUNTRY",
       extract(a.XMLRECORD,'/row/c1') "COUNTRY_1" 
FROM  "F_TESTMV";

当前查询及问题 #

当前使用以下语句筛选空值记录：

SELECT RECID 
FROM "V_F_TESTMV" 
WHERE XMLEXISTS('$t[/row/c1[not(text())][not(*)] or /row/c1/text()= ""  or fn:not(/row/c1/text()) ] ' PASSING "THE_RECORD" as "t");

• 当前结果：仅选中RECID为11000、12000、15000、16000、17000的5条记录
• 问题：RECID=18000的记录（仅存在带@m=1和@s=2的子值c1元素，主值c1为空）未被匹配

解决方案 #

调整XMLEXISTS中的XPath逻辑，覆盖无主值c1元素的场景（即仅存在带子值标记的c1元素）：

SELECT RECID 
FROM "V_F_TESTMV" 
WHERE XMLEXISTS('$t[
    -- 场景1：存在空的c1元素（无文本、无子元素）
    /row/c1[not(text()) and not(*)] 
    -- 场景2：c1元素文本为空字符串
    or /row/c1/text() = "" 
    -- 场景3：无主c1元素（仅存在带m属性的子值c1）
    or not(/row/c1[not(@m)])
]' PASSING "THE_RECORD" as "t");

逻辑说明 #

1. /row/c1[not(text()) and not(*)]：匹配完全空的c1元素（如RECID11000的第一个c1）
2. /row/c1/text() = ""：匹配文本为空字符串的c1元素（如RECID12000的第二个c1）
3. not(/row/c1[not(@m)])：匹配不存在无@m属性的主c1元素的情况（即RECID18000，仅存在带@m的子值c1，主值为空）

执行该查询后，将选中RECID为11000、12000、15000、16000、17000、18000的6条记录，符合预期结果。

内容的提问来源于stack exchange，提问作者Tamilselvan Perumal