Hi,
I am trying to extract data from xml using xmltable.
But, when there is no records for the child record, I am not getting the parent details also. How can I get the parent records if the children don't exists.
sample query which I am using.
select header_row.class_name
,child_row.first_name
,child_row.last_name
from XMLTABLE('/classes/class'
PASSING xmltype(
'<classes>
<class>
<class_name>Research1</class_name>
<teachers>
<teacher>
<first_name>First_Name</first_name>
<last_name>Last_Name</last_name>
</teacher>
</teachers>
</class>
<class>
<class_name>Research2</class_name>
<teachers/>
</class>
</classes>')
COLUMNS
class_name VARCHAR2(10) PATH 'class_name',
child_rows XMLTYPE PATH 'teachers'
) header_row
,XMLTABLE('/teachers/teacher'
PASSING header_row.child_rows
COLUMNS
first_name VARCHAR2(30) PATH 'first_name',
last_name VARCHAR2(30) PATH 'last_name'
) child_row;