Count(*)/Group by - how to with XMLAgg, XMLForest..
559013Feb 20 2009 — edited Feb 22 2009Hi', I'm new at using SQLX and need some help. I would like to generate the following XML output (that one has been done using Oracle*Reports) :
<_TREND>
<G_COUNTRY>
<COUNTRY>France</COUNTRY>
<G_SALES_DATE>
<SALES_DATE>10/02/2009</SALES_DATE>
<CAR>594</CAR>
<BIKE>7489</BIKE>
</G_SALES_DATE>
<G_SALES_DATE>
<SALES_DATE>11/02/2009</SALES_DATE>
<CAR>608</CAR>
<BIKE>7583</BIKE>
</G_SALES_DATE>
</G_COUNTRY>
</_TREND>
My table has the following basic structure :
country (varchar), sales_date (date), type (varchar). Type can be CAR or BIKE.
Using this query and OracleReports, I've managed to generate the above XML (which can then be used within an RTF template to generate a BI graph showing per sales_date 3 vertical bar - CAR,BIKE and TOTAL, ) :
select country,sales_date,type,count(*) from my_table group by country,sales_date,type.
I've tried to get the same XML using XMLAgg, XMLForest..but did not succeed. I tried with DBMS_XMLGEN which sounds to give interresting perspectives. Anyway, it should be feasible to do it with SQLX ? Thanks for your help.