Create XML from Oracle Hierarchical Tables

Question

I'm trying to generate a XML file representing a hierarchical relationship between records of a TABLE1 on Oracle 12c Database. The relationship between records of this TABLE1 are stored in another TABLE2 using child_id and parent_id columns.The example below represents a typical XML hierarchy that I need to generate with some basic info from TABLE1.

Example XML: X has 2 children (Y and Z) and Z has 2 children (W and K)

<TABLE1>
    <NAME>X</NAME>
    <TABLE1>
        <NAME>Y</NAME>
    </TABLE1>
    <TABLE1>
        <NAME>Z</NAME>
        <TABLE1>
            <NAME>W</NAME>
        </TABLE1>
        <TABLE1>
            <NAME>K</NAME>
        </TABLE1>
    </TABLE1>
</TABLE1>

The XML generated uses TABLE1 name for each rowtag representing a node on the hierarchy. Any other tag at the XML (e.g. NAME) is a column at TABLE1.

I've tried to use CONNECT BY with xmlement / xmlconcat / SYS_CONNECT_BY_PATH without success.

Is there a better angle to attack this problem?

mkuligowski · Accepted Answer · 2018-05-04 20:04:51Z

How about dbms_xmlgen.newContextFromHierarchy ?

Assuming that ddls from your question looks like:

create table table1 ( table1_id number, table1_name varchar2(30));
create table table2 (child_id number, parent_id number);

With sample data:

insert into table1 values( 1, 'X');
insert into table1 values( 2, 'Y');
insert into table1 values( 3, 'Z');
insert into table1 values( 4, 'W');
insert into table1 values( 5, 'K');
insert into table2 values (1, null);
insert into table2 values (2, 1);
insert into table2 values (3, 1);
insert into table2 values (4, 3);
insert into table2 values (5, 3);

Following query:

select dbms_xmlgen.newContextFromHierarchy('
  select
    level,
    xmlelement("TABLE1",
      xmlelement("NAME",t1.table1_name)
    )
  from table1 t1
  join table2 t2 on t2.child_id = t1.table1_id
  start with t2.parent_id is null
  connect by prior  t2.child_id = t2.parent_id
  ')
from dual;

Should return:

<?xml version="1.0"?>
<TABLE1>
    <NAME>X</NAME>
    <TABLE1>
        <NAME>Y</NAME>
    </TABLE1>
    <TABLE1>
        <NAME>Z</NAME>
        <TABLE1>
            <NAME>W</NAME>
        </TABLE1>
        <TABLE1>
            <NAME>K</NAME>
        </TABLE1>
    </TABLE1>
</TABLE1>

Collectives™ on Stack Overflow

Create XML from Oracle Hierarchical Tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related