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