Querying incrementing XML element names in Oracle

I am trying to query a XMLtype field in a table which has element names that increment by 1 on each occurrence. There could be 1 to many levels of these elements and I was wondering if there is a way to wildcard the element names so it would return all the elements values based on the name search instead of me unioning all possible levels of a name together? I'm looking to listagg or concatenate the results after I figure out this step.

select extractvalue(myXMLfield,'/doc/name1') from myTable
union
select extractvalue(myXMLfield,'/doc/name2') from myTable
union
select extractvalue(myXMLfield,'/doc/name3') from myTable

vs some sort of wildcard search on the element names

select extractvalue(myXMLfield,'/doc/name%') from myTable

XML sample

<doc><name1>NAME_1</name1><name2>NAME_2</name2><name3>NAME_3</name3></doc>

Desired SQL query varchar concatenated output

"NAME_1,NAME_2,NAME_3"

Solution

You could use XMLTable to extract the multiple node values, and a substring pattern in the XPath to wildcard the node names (setting the column size suitably for your actual data):

select x.name
from myTable t
cross join XMLTable(
  '/doc/*[substring(name(), 1, 4) = "name"]'
  passing myXMLfield
  columns name varchar2(20) path '.'
) x;

NAME                
--------------------
NAME_1
NAME_2
NAME_3

And you could then just use listagg() for your final output:

select listagg(x.name, ',') within group (order by null) as names
from myTable t
cross join XMLTable(
  '/doc/*[substring(name(), 1, 4) = "name"]'
  passing myXMLfield
  columns name varchar2(20) path '.'
) x;

NAMES                         
------------------------------
NAME_1,NAME_2,NAME_3

Ordering by null ins't ideal; if your real data had other attributes or values that you could extract and use for the ordering that might be better. If not you could use the part after 'name', assuming it is actually always numeric, via another substring:

select listagg(x.name, ',') within group (order by x.id) as names
from myTable t
cross join XMLTable(
  '/doc/*[substring(name(), 1, 4) = "name"]'
  passing myXMLfield
  columns name varchar2(20) path '.',
    id number path 'substring(name(.), 5)'
) x;