Home » Developer & Programmer » JDeveloper, Java & XML » recursive hierachy query to xml (Oracle 10g, AIX)
recursive hierachy query to xml [message #578858] Tue, 05 March 2013 22:32 Go to next message
kang
Messages: 89
Registered: November 2007
Member
To use dynatree(https://code.google.com/p/dynatree/) I want the result to be in the xml form.
but the result is not what I want.

can anybody help me?

SELECT 
  XMLELEMENT("div",xmlattributes('tree' AS "id"),
       (SELECT DBMS_XMLGEN.getXMLType(
         DBMS_XMLGEN.newContextFromHierarchy('
            SELECT LEVEL,
                   case
                     when CONNECT_BY_ISLEAF = 0 then
                      XMLELEMENT("ul", XMLELEMENT("li", xmlattributes(empno AS "id"), ename))
                     when CONNECT_BY_ISLEAF = 1 then
                      XMLELEMENT("li", xmlattributes(empno AS "id"), ename)
                   end
              FROM emp
             START WITH mgr is null
            CONNECT BY PRIOR empno = mgr '
            )
       ) FROM DUAL
      )) XMLDOC  
 FROM DUAL;


CURRENT RESULT
<div id="tree"><ul>
  <li id="7839">KING</li>
  <ul>
    <li id="7566">JONES</li>
    <ul>
      <li id="7788">SCOTT</li>
      <li id="7876">ADAMS</li>
    </ul>
    <ul>
      <li id="7902">FORD</li>
      <li id="7369">SMITH</li>
    </ul>
  </ul>
  <ul>
    <li id="7698">BLAKE</li>
    <li id="7499">ALLEN</li>
    <li id="7521">WARD</li>
    <li id="7654">MARTIN</li>
    <li id="7844">TURNER</li>
    <li id="7900">JAMES</li>
  </ul>
  <ul>
    <li id="7782">CLARK</li>
    <li id="7934">MILLER</li>
  </ul>
</ul>
</div>

DESIRED RESULT
<ul>
  <li id="7839">KING
   <ul>
    <li id="7566">JONES
     <ul>
      <li id="7788">SCOTT
       <ul>
        <li id="7876">ADAMS</li>
       </ul>
      </li>
      <li id="7902">FORD
       <ul>
        <li id="7369">SMITH</li>
       </ul>
      </li>
     </ul>
    </li>
    <li id="7698">BLAKE
     <ul>
      <li id="7499">ALLEN</li>
      <li id="7521">WARD</li>
      <li id="7654">MARTIN</li>
      <li id="7844">TURNER</li>
      <li id="7900">JAMES</li>
     </ul>
    </li>
    <li id="7782">CLARK
     <ul>
      <li id="7934">MILLER</li>
     </ul>
    </li>
   </ul>
  </li>
 </ul>

[Updated on: Tue, 05 March 2013 22:55]

Report message to a moderator

Re: recursive hierachy query to xml [message #578859 is a reply to message #578858] Tue, 05 March 2013 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: recursive hierachy query to xml [message #578884 is a reply to message #578858] Wed, 06 March 2013 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your version with 4 decimals?
Post the result of:
SELECT LEVEL,
       case
         when CONNECT_BY_ISLEAF = 0 then
          XMLELEMENT("ul", XMLELEMENT("li", xmlattributes(empno AS "id"), ename))
         when CONNECT_BY_ISLEAF = 1 then
          XMLELEMENT("li", xmlattributes(empno AS "id"), ename)
       end v
  FROM emp
 START WITH mgr is null
CONNECT BY PRIOR empno = mgr 
/

and
SELECT DBMS_XMLGEN.getXMLType(
         DBMS_XMLGEN.newContextFromHierarchy('
            SELECT LEVEL,
                   case
                     when CONNECT_BY_ISLEAF = 0 then
                      XMLELEMENT("ul", XMLELEMENT("li", xmlattributes(empno AS "id"), ename))
                     when CONNECT_BY_ISLEAF = 1 then
                      XMLELEMENT("li", xmlattributes(empno AS "id"), ename)
                   end
              FROM emp
             START WITH mgr is null
            CONNECT BY PRIOR empno = mgr '
            )
       ) FROM DUAL
/

Regards
Michel
Re: recursive hierachy query to xml [message #578894 is a reply to message #578884] Wed, 06 March 2013 03:15 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
ORACLE VERSION :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

FIRST QUERY :
1	<ul><li id="7839">KING</li></ul>
2	<ul><li id="7566">JONES</li></ul>
3	<ul><li id="7788">SCOTT</li></ul>
4	<li id="7876">ADAMS</li>
3	<ul><li id="7902">FORD</li></ul>
4	<li id="7369">SMITH</li>
2	{blank}
3	<li id="7499">ALLEN</li>
3	<li id="7521">WARD</li>
3	<li id="7654">MARTIN</li>
3	<li id="7844">TURNER</li>
3	<li id="7900">JAMES</li>
2	<ul><li id="7782">CLARK</li></ul>
3	<li id="7934">MILLER</li>


SECOND QUERY :
ORA-21500 ERROR
Re: recursive hierachy query to xml [message #578896 is a reply to message #578894] Wed, 06 March 2013 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this is the problem.
I don't know if there is a fix or if 10.2.0.5 fixes it; have a look at MOS, and possibly open a SR to Oracle.

ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
 *Cause:  This is the generic error number for the OCI
          environment (client-side) internal errors.  This indicates
          that the OCI environment has encountered an exceptional
          condition.
 *Action: Report as a bug - the first argument is the internal error number.


Regards
Michel

[Edit: add error meaning]

[Updated on: Wed, 06 March 2013 03:24]

Report message to a moderator

Re: recursive hierachy query to xml [message #578898 is a reply to message #578896] Wed, 06 March 2013 03:26 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
what's MOS?
what's SR?
Re: recursive hierachy query to xml [message #578900 is a reply to message #578898] Wed, 06 March 2013 03:34 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MOS: My Oracle Support (was Metalink)
SR: Service Request (was TAR, Technical Assistance Request)

Regards
Michel

[Updated on: Wed, 06 March 2013 03:38]

Report message to a moderator

Previous Topic: Query help to generate XML
Next Topic: Null argument
Goto Forum:
  


Current Time: Thu Mar 28 07:35:43 CDT 2024