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.

What error are you getting? Please add that to the question – Dijkgraaf Nov 12 '14 at 01:21 — Dijkgraaf, Nov 12 '14 at 01:21
Added the errors to the question. – Tina Nov 12 '14 at 01:39 — Tina, Nov 12 '14 at 01:39

score 1 · Accepted Answer · answered Nov 12 '14 at 05:29

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;

answered Nov 12 '14 at 05:29

Shawn Mire

306
2
5

What does the output of that look like? – Dijkgraaf Nov 12 '14 at 05:49
1

@Dijkgraaf [Here's](http://sqlfiddle.com/#!4/9dbd37/1) a SQLFiddle comparing the two. – Shawn Mire Nov 12 '14 at 07:10
Glad to know, @Tina! If this was useful to you, please consider marking this as the accepted answer. – Shawn Mire Nov 14 '14 at 22:02

score 0 · Answer 2 · answered Nov 12 '14 at 05:06

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 ;

XML format in oracle

2 Answers2