XML format in oracle

Question

I am looking for an XML output like this:

<EmployeeCounts>
  <Count DESIGNATION="CLERK">200</Count>
  <Count DESIGNATION="MANAGER">1</Count>
  <Count DESIGNATION="PROFESSOR">4</Count>
</EmployeeCounts>

I am struggling to get the same format as mentioned above.

I used this query but it is erroring out

   SELECT  XMLELEMENT ("EmployeeCounts"
   ,XMLELEMENT("COUNT",
   XMLATTRIBUTES (e.DESIGNATION as "DESIGNATION", count(*))
                  )
   ) AS "emp"
   FROM employees e
   GROUP BY e.DESIGNATION ;

The error I'm getting is:

ORA-19208: parameter 2 of function XMLELEMENT must be aliased 19208.
00000 - "parameter %s of function %s must be aliased"
*Cause: The indicated parameter of the XML generation function has not 
been aliased, although it is an expression. *Action: Specify an alias 
for the expression using the AS clause. 
Error at Line: 6 Column: 14 

But I don't want an alias for the count as per the format mentioned above.

Any help regarding this will be highly appreciated.

Answer 1

Dijkgraaf's solution will resolve the error, but will not output the desired XML format. Try the following which removes the COUNT from the XMLATTRIBUTES function, and utilizes XMLAGG so you get one set of EmployeeCounts tags:

SELECT
    XMLELEMENT("EmployeeCounts"
        , XMLAGG(
            XMLELEMENT("Count"
                , XMLATTRIBUTES(e.designation AS "DESIGNATION")
                , COUNT(1)
            )
        )
    )
FROM
    employees e
GROUP BY
    e.designation;

Answer 2

I don't think you have a choice about not aliasing it.

SELECT  XMLELEMENT ("EmployeeCounts"
,XMLELEMENT("COUNT",
XMLATTRIBUTES (e.DESIGNATION as "DESIGNATION", count(*) as DESCOUNT)
              )
) AS "emp"
FROM employees e
GROUP BY e.DESIGNATION ;

You could try the following, but aliasing things to reserved words can be problematic sometimes.

SELECT  XMLELEMENT ("EmployeeCounts"
,XMLELEMENT("COUNT",
XMLATTRIBUTES (e.DESIGNATION as "DESIGNATION", count(*) as Count)
               )
) AS "emp"

FROM employees e GROUP BY e.DESIGNATION ;