oracle - PLSQL Loop on parentid -

i'm using function , receive string this: can see 'n-times' nested.

<project id="123">     <project id="12345" parentid="123"></project>     <project id="12333" parentid="123>         <project id="123345" parentid="12333"></project>     </project> </project> <project id="1234">      ... </project> 

plsql far:

for in (            select prj.id project_id, prj.parent_id parent_id         prj_project prj          parent_id null          ) loop     sendxml := sendxml || '<project id="' ||  i.project_id || '"' || ' parentid="' || i.parent_id || '">' || '</project>'; end loop; 

this returns me first "level". in loop want have loop, each entry in database, can receive xml-string above.

the database looks like:

| id     | parent_id  ---------------------- | 123    | null  | 12345  | 123  | 12333  | 123 | 123345 | 12333  | 1234   | null  

how can loop through until there no more data in table , go on next level?

thanks matt

use hierarchical query using connect by

select prj.id project_id,         prj.parent_id parent_id prj_project prj  connect prior prj.id = prj.parent_id start prj.parent_id null; 

to ensure root nodes come first, , children, extend statement this:

select prj.id project_id,         prj.parent_id parent_id,        connect_by_root id root_project_id prj_project prj  connect prior prj.id = prj.parent_id start prj.parent_id null order connect_by_root id, level; 

here sqlfiddle: http://sqlfiddle.com/#!4/606a7/1