Example 9-7: Multi-level query using nested CURSOR expressions

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="DevBugList.xsl"?>
<xsql:query connection="xmlbook" row-element="team" xmlns:xsql="urn:oracle-xsql"
            tag-case="lower" fixby="3.0">

  SELECT t.name, 
         count(b.id) TEAMBUGS, 
         /* Detail Set of Team Members With Open Bugs for Current Team */
         CURSOR( SELECT b1.owner, 
                        COUNT(b1.id) AS bugs, 
                        /* Detail Set of Open Bugs for Current Developer */
                        CURSOR( SELECT b2.id bug, 
                                       b2.priority, 
                                       InitCap(b2.abstract) abstract
                                  FROM bug b2
                                 WHERE b2.owner = b1.owner
                                   AND b2.status = 11
                                   AND b2.fixby = '{@fixby}'
                                 ORDER BY b2.priority, b2.id DESC
                              ) as BUGLIST
                   FROM team t1, person p1, bug b1
                  WHERE p1.teamid = t1.id
                    AND t1.name = t.name
                    AND b1.owner  = p1.name
                    AND status = 11
                    AND fixby = '{@fixby}'
                  GROUP BY b1.owner, p1.teamid
                  ORDER BY 2 DESC
               ) AS members
    FROM team t, person p, bug b
   WHERE p.teamid = t.id
     AND b.owner  = p.name
     AND b.status = 11
     AND b.fixby = '{@fixby}'
   GROUP BY t.name
   ORDER BY 2 DESC

</xsql:query>