Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, JOSEPH.

Asked: July 26, 2016 - 1:27 am UTC

Last updated: July 26, 2016 - 7:57 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Team,

I have the below data in a table:-

id parent_id
1
2
3 1
4 1
5 2
6 2
7 3
8 3

I have tried hierarchical functions and I have got the below data

SELECT id,
parent_id,
LEVEL,
CONNECT_BY_ROOT id AS root_id,
SYS_CONNECT_BY_PATH (id, '-') AS PATH,
CONNECT_BY_ISLEAF AS leaf,
CONNECT_BY_ISCYCLE AS cycle
FROM menu
START WITH parent_id IS NULL
CONNECT BY NOCYCLE parent_id = PRIOR id
ORDER BY id;

id parent_id level root_id path leaf cycle
1 1 1 -1 0 0
2 1 2 -2 0 0
3 1 2 1 -1-3 0 0
4 1 2 1 -1-4 1 0
5 2 2 2 -2-5 1 0
6 2 2 2 -2-6 1 0
7 3 3 1 -1-3-7 1 0
8 3 3 1 -1-3-8 1 0


What I need to accomplish is convert the above data into a xml format as below

<ns0:menu>
<ns0:menulist>
<ns0:index>1</ns0:index>
<ns0:menulist>
<ns0:index>3</ns0:index>
<ns0:menulist>
<ns0:index>7</ns0:index>
<ns0:index>8</ns0:index>
</ns0:menulist>
</ns0:menulist>
<ns0:menulist>
<ns0:index>4</ns0:index>
</ns0:menulist>
</ns0:menulist>
<ns0:menulist>
<ns0:index>2</ns0:index>
<ns0:menulist>
<ns0:index>5</ns0:index>
<ns0:index>6</ns0:index>
</ns0:menulist>
</ns0:menulist>
</ns0:menu>

The hierarchy can go up to n levels. Is there some built in xml functions that i can use here.

and Chris said...

Sounds like what you need is dbms_xmlgen.newcontextFromHierarchy. This takes a hierarchical query and generates the corresponding XML:

CREATE TABLE t
    (id int, parent_id int)
;
    
INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (2, NULL);
INSERT INTO t VALUES (3, 1);
INSERT INTO t VALUES (4, 1);
INSERT INTO t VALUES (5, 2);
INSERT INTO t VALUES (6, 2);
INSERT INTO t VALUES (7, 3);
INSERT INTO t VALUES (8, 3);

select xmlelement("menu",
  (select dbms_xmlgen.getxmltype(dbms_xmlgen.newcontextFromHierarchy('
select level 
       , xmlelement("menulist" 
       , xmlelement("index", id)
       ) 
from t
START WITH parent_id IS NULL
CONNECT BY NOCYCLE parent_id = PRIOR id
ORDER siblings BY id
  ')) from dual) ) xmldoc
from dual;

XMLDOC
----------------------
<menu><menulist>
  <index>1</index>
  <menulist>
    <index>3</index>
    <menulist>
      <index>7</index>
    </menulist>
    <menulist>
      <index>8</index>
    </menulist>
  </menulist>
  <menulist>
    <index>4</index>
  </menulist>
</menulist>
<menulist>
  <index>2</index>
  <menulist>
    <index>5</index>
  </menulist>
  <menulist>
    <index>6</index>
  </menulist>
</menulist>
</menu>


See also:

http://awads.net/wp/2007/02/20/an-easy-way-to-convert-a-hierarchical-query-result-to-xml/

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

DBMS_XMLGEN

More on PL/SQL routine DBMS_XMLGEN here