--company
--companycount (sum(deptcnt))
--        deptno
--        deptcnt sum(empcount)
--            employees
--                empdetails



SELECT
    XMLSERIALIZE(DOCUMENT XMLELEMENT(
        "company", XMLAGG(XMLFOREST(compcnt, deptdet))
    ))
FROM
    (
        SELECT
            SUM(empcnt) compcnt,
            XMLELEMENT(
                "department", XMLAGG(XMLFOREST(deptno, empcnt, empdet))
            ) deptdet
        FROM
            (
                SELECT
                    dept.deptno,
                    COUNT(emp.deptno) empcnt,
                    XMLELEMENT(
                        "employees", XMLAGG(XMLFOREST(ename, sal))
                    ) empdet
                FROM
                    emp,
                    dept
                WHERE
                    emp.deptno (+) = dept.deptno
                GROUP BY
                    dept.deptno
            )
    );

WITH emp_det AS (
    SELECT
        ename,
        sal,
        dept.deptno   dept_dept,
        emp.deptno    emp_dept
    FROM
        emp,
        dept
    WHERE
        emp.deptno (+) = dept.deptno
), emp_group AS (
    SELECT
        dept_dept,
        empcnt,
        SUM(empcnt) OVER() emp_sum
    FROM
        (
            SELECT
                dept_dept,
                COUNT(emp_dept) empcnt
            FROM
                emp_det
            GROUP BY
                dept_dept
        )
)
SELECT
    XMLSERIALIZE(DOCUMENT XMLELEMENT(
        "company",(XMLELEMENT(
            "companyDetails", XMLELEMENT(
                "COMPCNT", MAX(emp_sum)
            ), XMLAGG(XMLELEMENT(
                "department", XMLELEMENT(
                    "DEPTNO", dept_dept
                ), XMLELEMENT(
                    "EMPCNT", empcnt
                ),(
                    SELECT
                        XMLAGG(XMLELEMENT(
                            "employees", XMLAGG(XMLFOREST(ename, sal))
                        )) empdet
                    FROM
                        emp_det
                    WHERE
                        emp_group.dept_dept = emp_det.dept_dept
                    GROUP BY
                        dept_dept
                )
            )
                ORDER BY
                    dept_dept
            )
        ))
    ))
FROM
    emp_group;