OraFAQ Forum: JDeveloper, Java & XML » recursive hierachy query to xml

Home » Developer & Programmer » JDeveloper, Java & XML » recursive hierachy query to xml (Oracle 10g, AIX)

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

recursive hierachy query to xml [message #578858]

Tue, 05 March 2013 22:32

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

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/

Report message to a moderator

Re: recursive hierachy query to xml [message #578884 is a reply to message #578858]

Wed, 06 March 2013 01:50

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: recursive hierachy query to xml [message #578894 is a reply to message #578884]

Wed, 06 March 2013 03:15

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

Report message to a moderator

Re: recursive hierachy query to xml [message #578896 is a reply to message #578894]

Wed, 06 March 2013 03:18

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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