Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t(c clob);
insert into t values('<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>');
1 rows affected

select *
from t
cross apply xmltable(
'/dataView/fieldOptions/quickFilterValues'
passing xmltype(t.c)
columns
fo  varchar2(250) path '.'
) x;


C
FO


<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>
100

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>
10

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>
20

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>
30


-- works when QFs present, but doesn't get fieldID 1
SELECT x.*
FROM t
CROSS APPLY XMLTable(
         '/dataView/fieldOptions/quickFilterValues'
         PASSING xmltype(t.c)
         COLUMNS
           fieldid NUMBER PATH './../fieldId',
           qf  VARCHAR2(250) PATH '.'
) x


FIELDID
QF


2
100

3
10

3
20

3
30


SELECT x.*
FROM t
CROSS APPLY XMLTable(
         '/dataView/fieldOptions'
         PASSING xmltype(t.c)
         COLUMNS
           fieldid NUMBER PATH 'fieldId',
           qf  VARCHAR2(250) PATH 'quickFilterValues'
) x
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

SELECT x1.fieldId, x2.qf
FROM t
CROSS APPLY XMLTable(
         '/dataView/fieldOptions'
         PASSING xmltype(t.c)
         COLUMNS
           fieldId NUMBER PATH 'fieldId',
           qfs XMLTYPE PATH 'quickFilterValues'
) x1
OUTER APPLY XMLTable(
         '/quickFilterValues'
         PASSING qfs
         COLUMNS
           qf  NUMBER PATH '.'
) x2


FIELDID
QF


1
null

2
100

3
10

3
20

3
30


SELECT x1.fieldId, count(x2.qf)
FROM t
CROSS APPLY XMLTable(
         '/dataView/fieldOptions'
         PASSING xmltype(t.c)
         COLUMNS
           fieldId NUMBER PATH 'fieldId',
           qfs XMLTYPE PATH 'quickFilterValues'
) x1
OUTER APPLY XMLTable(
         '/quickFilterValues'
         PASSING qfs
         COLUMNS
           qf  NUMBER PATH '.'
) x2
GROUP BY x1.fieldId
ORDER BY x1.fieldId


FIELDID
COUNT(X2.QF)


1
0

2
1

3
3


select x.*
from t,
xmltable(
    '/dataView/fieldOptions' passing xmltype(t.c)
    columns
        fieldId path './fieldId/text()',
        nQuickFilterValues number path 'count(./quickFilterValues)'
        
) x


FIELDID
NQUICKFILTERVALUES


1
0

2
1

3
3

C	FO
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>	100
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>	10
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>	20
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>	30