I used the XMLAGG
aggregate function in a single row and I keep getting the error code ORA-00937. Why?
The structure for the report is:
<Forms>
<Year>2015</Year>
<Month>September</Month>
<Date>2015-09-22</Date>
<Form>A1</Form>
<Form>B4</Form>
…
</Forms>
My select statement is:
SELECT XMLSERIALIZE(
DOCUMENT XMLROOT(
XMLELEMENT("Forms",XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema- instance' AS "xmlns:xsi"),
XMLELEMENT("Year", b.Year),
XMLELEMENT("Month", b.Month),
XMLELEMENT("Date", b.r_modify_date),
XMLAGG(XMLELEMENT"Form",b.docnum))
),
)
)
FROM xml_bill b
where trunc(b.R_MODIFY_DATE) =trunc(sysdate);
So I added GROUP BY in the WHERE clause and modified XMLELEMENT(“Date”, trunc(b.r_modify_date) in the SELECT statement
Here is the new query:
SELECT XMLSERIALIZE(
DOCUMENT XMLROOT(
XMLELEMENT("Forms",XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"),
XMLELEMENT("Year", b.Year),
XMLELEMENT("Month", b.Month),
XMLELEMENT("Date", trunc(b.R_MODIFY_DATE)),
XMLAGG(
XMLELEMENT("Form", b.DOCNUM)
)
),
)
)
FROM xml_bill b
where
trunc(b.R_MODIFY_DATE) =trunc(sysdate)
GROUP BY b.Year, b.Month,trunc(b.R_MODIFY_DATE);