SQL & PL/SQL

Good morning everyone,

I need your assistance once again. I hope it is ok.

I have a table with a lot of records. It a table with a few million records. These records to simplify the question look like this:

WITH sample_data AS (
 --
 SELECT TO_DATE('01/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'crash' AS cause, 10 AS errors, 50 AS success FROM dual UNION ALL
 SELECT TO_DATE('02/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'crash' AS cause, 08 AS errors, 32 AS success FROM dual UNION ALL
 --
 SELECT TO_DATE('01/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'fine' AS cause, 11 AS errors, 5 AS success FROM dual UNION ALL
 SELECT TO_DATE('02/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'fine' AS cause, 12 AS errors, 7 AS success FROM dual UNION ALL
 SELECT TO_DATE('03/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'fine' AS cause, 13 AS errors, 9 AS success FROM dual UNION ALL
 --
 SELECT TO_DATE('09/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'missing' AS cause, 10 AS errors, 33 AS success FROM dual UNION ALL
 SELECT TO_DATE('15/01/2025', 'DD/MM/YYYY') AS day, 'foo' AS logger, 'missing' AS cause, 12 AS errors, 44 AS success FROM dual
)
SELECT *
 FROM sample_data
;

I would like if possible to generate an XML that look like this. I will have a filter on the days and will probably have more or less between 100 and 200 records. So I hope it is not a big XML.

<Root>
 <Loggers>
   <Logger>
     <LoggerName>foo</LoggerName>
     <Causes>
       <Cause>
         <name>crash</name>
         <Metrics>
           <Metric>
             <day>01/01/2025</day>
             <errors>10</errors>
             <success>50</success>
           </Metric>
           <Metric>
             <day>02/01/2025</day>
             <errors>09</errors>
             <success>32</success>
           </Metric>
         </Metrics>
       <Cause>
       <Cause>
         <name>fine</name>
         <metrics>
           <Metric>
             <day>01/01/2025</day>
             <errors>11</errors>
             <success>5</success>
           </Metric>
           <Metric>
             <day>02/01/2025</day>
             <errors>12</errors>
             <success>7</success>
           </Metric>
           <Metric>
             <day>03/01/2025</day>
             <errors>13</errors>
             <success>9</success>
           </Metric>
         </metrics>
       </Cause>
       <Cause>
         <name>missing</name>
         <Metrics>
           <Metric>
             <day>09/01/2025</day>
             <errors>10</errors>
             <success>33</success>
           </Metric>
           <Metric>
             <day>15/01/2025</day>
             <errors>12</errors>
             <success>44</success>
           </Metric>
         </Metrics>
       <Cause>
     </Causes>
   </Logger>
 </Loggers>
</Root>

I tried so many options and I'm getting nuts. I already got

ORA-22813: operand value exceeds system limits

ORA-00935: group function is nested too deeply

Here since I have two level of aggregations, I am lost. I want all the nodes for a specific cause to be groupped together and all the metrics grouped together also.

I hope somebody could guide on what is the best to proceed. I wanted to use sub queries. Since the table is huge, I thought maybe there is another option (if it works for oracle 19c it will be better)? Any recommendation is welcome.

Thank you all!