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>

This looks very non-trivial. Probably a recursive query to trace the tree, then build the nested xml with something like this stackoverflow.com/questions/71413139/… would be the pure Oracle way. Could you bring python or some other more general language into the fold? This would be pretty easy if you query the table via pyodbc and build your xml with a language more well suited for it. — Error_2646
– Error_2646, Commented Dec 21, 2023 at 18:07
Unfortunately we don't have the privilege to use Python.Has to be done only in Oracle — Naren
– Naren, Commented Dec 21, 2023 at 22:18
Moot point I suppose, got egg on my face. MT0's answer is very clean in pure Oracle. — Error_2646
– Error_2646, Commented Dec 21, 2023 at 23:38

MT0 · Accepted Answer · 2023-12-21 22:47:02Z

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

Collectives™ on Stack Overflow

Oracle hierarchical query to build xml output

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related