This discussion is archived
2 Replies Latest reply: Jan 4, 2013 5:07 AM by odie_63 RSS

xml and connect by

Mettemusens2 Newbie
Currently Being Moderated
Hi

I'm on 11g and I want to make a hierachical query in plain XML (I know I can do it in PL/SQL):

Example (that does not work as I want to):
SELECT   xmlelement("Emps", xmlagg(XMLElement("emp", 
                         xmlelement("level", level),
                         XMLElement("id", employee_id),
                         XMLElement("name", last_name)
))) xml
         FROM hr.employees
         START WITH employee_id=100 CONNECT BY PRIOR employee_id=manager_id
         ORDER SIBLINGS BY hire_date

This gives me:

<Emps>
  <emp>
    <level>1</level>
    <id>100</id>
    <name>King</name>
  </emp>
  <emp>
    <level>2</level>
    <id>101</id>
    <name>Kochhar</name>
  </emp>
  <emp>
    <level>3</level>
    <id>108</id>
    <name>Greenberg</name>
  </emp>
  <emp>
    <level>4</level>
    <id>109</id>
    <name>Faviet</name>
  </emp>
  <emp>
    <level>4</level>
    <id>110</id>
    <name>Chen</name>
  </emp>
  <emp>
    <level>4</level>
    <id>111</id>
    <name>Sciarra</name>
  </emp>
  <emp>
    <level>4</level>
    <id>112</id>
    <name>Urman</name>
  </emp>
  <emp>
    <level>4</level>
    <id>113</id>
    <name>Popp</name>
  </emp>
  <emp>
    <level>3</level>
    <id>200</id>
    <name>Whalen</name>
  </emp>

I would like to have had:

<Emps>
  <emp>
    <level>1</level>
    <id>100</id>
    <name>King</name>
     <emp>
          <level>2</level>
          <id>101</id>
          <name>Kochhar</name>
          <emp>
               <level>3</level>
               <id>108</id>
               <name>Greenberg</name>
               <emp>
                    <level>4</level>
                    <id>109</id>
                    <name>Faviet</name>
               </emp>
               <emp>
                    <level>4</level>
                    <id>110</id>
                    <name>Chen</name>
               </emp>
               <emp>
                    <level>4</level>
                    <id>111</id>
                    <name>Sciarra</name>
               </emp>
               <emp>
                    <level>4</level>
                    <id>112</id>
                    <name>Urman</name>
               </emp>
               <emp>
                    <level>4</level>
                    <id>113</id>
                    <name>Popp</name>
               </emp>
          </emp>
          <emp>
               <level>3</level>
               <id>200</id>
               <name>Whalen</name>
          </emp>
etc
Can anyone give me hint ?

/Mette
  • 1. Re: xml and connect by
    Mettemusens2 Newbie
    Currently Being Moderated
    I got it ... found an answer in the SQL forum ....
    select xmlelement("employees",
      (select dbms_xmlgen.getxmltype(dbms_xmlgen.newcontextFromHierarchy('
        select level 
               , xmlelement("emp" 
               , xmlelement("number", employee_id)
               , xmlelement("name", last_name)
               , xmlelement("salary", salary)
               , xmlelement("hiredate", hire_date)
               , xmlelement("level", level)
               ) 
        from hr.employees
        start with manager_id is null connect by prior employee_id=manager_id
        order siblings by hire_date
      '))
      from dual)
      ) xmldoc
    from dual
    /Mette
  • 2. Re: xml and connect by
    odie_63 Guru
    Currently Being Moderated
    A few methods here :

    http://odieweblog.wordpress.com/2011/11/24/how-to-generate-a-recursive-xml-structure/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points