OraFAQ Forum: SQL & PL/SQL » Need XML query for tree

Home » SQL & PL/SQL » SQL & PL/SQL » Need XML query for tree

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need XML query for tree [message #596304]

Fri, 20 September 2013 02:38

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>

Report message to a moderator

Re: Need XML query for tree [message #596305 is a reply to message #596304]

Fri, 20 September 2013 02:55

Michel Cadot
Messages: 68647
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 12:46:08 CDT 2024