I need your help to frame a SQL query in Oracle . I am creating a XML type Table in Oracle Database and making one column of it a Multi Value Field. The data given to the Multi Value column for the records are of different cases with Multi Value and Sub Value. (Ex : ( “ ” : @VM: “ 10 ”) , (“ 12” : @VM: “ ”) , (“ ” : @SM: “ 8 ”) , (“ 6” : @SM: “ ”)) . When a SQL query is executed to select the records for which the Multi value column is equated to Null, the record where the first value of @SM is NULL is not getting selected ie. (“ ” : @SM: “ 8 ”).
Table Creation :
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 Statement :
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>'));
View Definition:
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 Query :
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") )
Current Result : From the eight records in the table, The above SQL query selects 5 records, with RECID 11000,12000,15000,16000,17000. It does not select RECID 18000 even when the first part of Sub Value is NULL in the record.
EXPECTED RESULT The Query should select 6 Records . The records with RECID 11000,12000,15000,16000,17000 and 18000 should be selected, as RECID 18000 also has first part of Sub Value as NULL. Kindly provide your Suggestion on the SQL Query required that will fetch the above records when the name of the Column with Multi Value field is Equated to NULL. Thanks