2 Replies Latest reply: Jan 4, 2013 7:07 AM by odie_63 RSS

    xml and connect by

    Mettemusens2
      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
          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
            A few methods here :

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