Home » SQL & PL/SQL » SQL & PL/SQL » Need XML query for tree
Need XML query for tree [message #596304] Fri, 20 September 2013 02:38 Go to next message
Anishkrithik
Messages: 23
Registered: June 2011
Location: TN
Junior Member
Hi,

I need to form XML for hirarchy data, please suggest.

CREATE TABLE TESTING (
EMPID VARCHAR2(1),
ENAME VARCHAR2(10),
CHILD_ID NUMBER,
PARENT_ID NUMBER);

INSERT INTO TESTING VALUES ('A', 'AText', 1001,0);
INSERT INTO TESTING VALUES ('B', 'BText', 1002,1001);
INSERT INTO TESTING VALUES ('C', 'CText', 1003,1002);
insert into testing values ('D', 'DText', 1004,1003);

SELECT LEVEL, EMPID,ENAME,CHILD_ID, PARENT_ID
FROM TESTING
START WITH PARENT_ID = 0
CONNECT BY NOCYCLE PRIOR CHILD_ID = PARENT_ID;

Need select query to return output like below

<ITEM ID="A" TEXT="AText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="B" TEXT="BText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="C" TEXT="CText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="D" TEXT="DText" IM0="folderClosed.gif" CHILD="1">
</ITEM>
</ITEM>
</ITEM>
</ITEM>
icon14.gif  Re: Need XML query for tree [message #596305 is a reply to message #596304] Fri, 20 September 2013 02:55 Go to previous message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> SELECT replace(
  2           sys_connect_by_path(
  3             '<ITEM ID="'||empid||'" TEXT="'||ename||'" IM0="folderClosed.gif" CHILD="1">',
  4             ',')
  5           ||sys_connect_by_path('</ITEM>',','),
  6           ',','
  7  ') val
  8  FROM TESTING
  9  where connect_by_isleaf=1
 10  START WITH PARENT_ID = 0
 11  CONNECT BY NOCYCLE PRIOR CHILD_ID = PARENT_ID;
VAL
------------------------------------------------------------------------------------------------------

<ITEM ID="A" TEXT="AText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="B" TEXT="BText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="C" TEXT="CText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="D" TEXT="DText" IM0="folderClosed.gif" CHILD="1">
</ITEM>
</ITEM>
</ITEM>
</ITEM>

1 row selected.

Previous Topic: Grouping Data
Next Topic: retrieve list of tables with same name from two schemas
Goto Forum:
  


Current Time: Tue Jul 29 10:06:47 CDT 2014

Total time taken to generate the page: 0.16955 seconds