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
CodePudding user response:
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