Oracle hierarchical query to build xml output

Question

I have a lookup table with parent and child relation.It has a list of tables with parent and child relationship along with the keys to join.I need to create a function to pull the data recursively from the parent and child tables using a dynamic query.In the given example data has to be pulled from all three tables and displayed hierarchically starting from HR.There could be any number of child records.Please help.

Table      Parent       PK     Parent_PK
HR                      Id
Department HR           Deptid Id
Emp        Department   Depid  depid   


<?ml version="1.0"?>                                                            
<HR>                                                                            
  <Department>                                                                                                                                                     
   <Emp> 
   </Emp>    
   <Department>                                                                         
</Hr> 

Answer 1

Create a recursive function:

CREATE FUNCTION generate_xml(
  i_name IN VARCHAR2,
  i_pk   IN VARCHAR2
) RETURN XMLTYPE
IS
  v_xml XMLTYPE;
BEGIN
  SELECT XMLELEMENT(
           EVALNAME i_name,
           XMLAGG(
             generate_xml(table_name, pk)
           )
         )
  INTO   v_xml
  FROM   table_name
  WHERE  parent_pk = i_pk;
  RETURN v_xml;
END;
/

Then you can use:

SELECT XMLSERIALIZE(
         DOCUMENT generate_xml(table_name, pk)
         AS CLOB
         VERSION '1.0'
       ) AS xml
FROM   table_name
WHERE  parent_pk IS NULL;

Which, for the sample data:

CREATE TABLE table_name (Table_name, Parent, PK, Parent_PK) AS
SELECT 'HR',         NULL,         'Id',     NULL     FROM DUAL UNION ALL
SELECT 'Department', 'HR',         'Deptid', 'Id'     FROM DUAL UNION ALL
SELECT 'Emp',        'Department', 'Depid',  'Deptid' FROM DUAL;

Outputs:

XML
<?xml version="1.0"?> <HR>   <Department>     <Emp/>   </Department> </HR>

fiddle