Relational data from XML (part 1)

Query:

with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                                                  ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                                                 ,xmlelement("NAME",dep.dname)
                                                                                 ,xmlelement("LOCATION",dep.loc)
                                                                                 ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                                           ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                                             ,xmlelement("NAME",emp.ename)
                                                                                                                                             ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                                             ,xmlelement("JOB",emp.job)
                                                                                                                                             ,xmlelement("SALARY",emp.sal)
                                                                                                                                             ) order by emp.ename asc
                                                                                                                                  )
                                                                                                          from emp emp
                                                                                                          where dep.deptno=emp.deptno)
                                                                                            )
                                                                    ) order by dep.dname asc
                                                         )) depemp
                   from dept dep)
select xmlquery('declare default element namespace "http://http://dataflows.blogspot.com/SCOTT/depemp/1"; (: :)
                 declare namespace qres="http://http://dataflows.blogspot.com/SCOTT/tmp_query_result"; (: :)
                 for $all in /DEPARTMENTS
                 return {
                   for $dep in $all/DEPARTMENT
                     for $emp in $dep/EMPLOYEES/EMPLOYEE
                     where $emp/HIRE_DATE[. > xs:date("1981-12-31")]
                     return 
                              {$emp/NAME/text()}
                              {$dep/NAME/text()}
                            
                 }'
                passing xdem.depemp
                returning content) emps
from xmldepemp xdem
;

Result:

  
    MILLER
    ACCOUNTING
  
  
    ADAMS
    RESEARCH
  
  
    SCOTT
    RESEARCH