Oracle: XML with multiple nodes with the same name to table

I have a XML file stored in a table with a rather strange structure: it can either not contain a certain node, it can contain it once or it can contain it multiple times. In the below example the node "quickFilterValues" is in all 3 situations. I want to build a report with two columns: "fieldId" and count of "quickFilterValues". Can you please help me with a SQL solution in Oracle?

<?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>

fieldId	count
1	0
2	1
3	3

I tried to use

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>');

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

but this only gets the overall count of "quickFilterValues" and I need it to be grouped by "fieldId".

Solution

XPATH expression can contain expressions like count():

xmltable(
    '/dataView/fieldOptions' passing d.xml
    columns
        fieldId path './fieldId/text()',
        nQuickFilterValues number path 'count(./quickFilterValues)'
        
) t