with this query I can select the content of tag

select xmltype(
  '<?xml version="1.0"?> 
  <ROWSET> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
    </ROW> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
    </ROW> 
  </ROWSET>'
).extract( '/ROWSET/ROW' ) .getstringval() p#
from dual;

<PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1><PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1>

but what I want is to have a list of the sub-tag.

the expected result for this example would be:

PERSON_NUMBER, LOAN_1

code

>Solution :

Similar to an answer to your previous question, you can get the name() XPATH value:

SELECT DISTINCT name
from XMLTABLE(
       '/ROWSET/ROW/*'
       PASSING xmltype(
         '<?xml version="1.0"?> 
         <ROWSET> 
           <ROW>
             <PERSON_NUMBER>1000142</PERSON_NUMBER>
             <LOAN_1>25000</LOAN_1>
             <LOAN_2>26000</LOAN_2>
           </ROW> 
           <ROW>
             <PERSON_NUMBER>1000142</PERSON_NUMBER>
             <LOAN_1>25000</LOAN_1>
             <LOAN_2>26000</LOAN_2>
           </ROW> 
         </ROWSET>'
       )
       COLUMNS
         name VARCHAR2(200) PATH './name()'
     );

Outputs:

NAME
PERSON_NUMBER
LOAN_1
LOAN_2

db<>fiddle here