Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Group function nested too deeply using XMLagg

Avatar of Reeds
Reeds asked on
Oracle Database
9 Comments1 Solution3626 ViewsLast Modified:
I have a query which returns XML, when I run the below I get "group function nested too deeply" because of XMLAgg being used 3 times. But I need it since I want to aggregate the results. Please help


select xmlelement("units",
       xmlagg(
            xmlelement("unit",
                xmlattributes(
                    u.unitid as "unitid",
                    u.code as "unitcode",
                    u.name as "unitname"
                ),
              xmlagg(
               xmlelement("datasets",
                     xmlelement("dataset",
                        xmlattributes(
                           d.datasetid as "id",
                           d.name as "name",
                           u.unitid || d.datasetid as "unitdatasetid"
                       ),
                      xmlagg(
                        xmlelement("journal",
                            xmlattributes(
                                 u.code as "unitcode",
                                 j.id as "journalid",
                                 j.je_code as "journalcode",
                                 d.datasetid as "datasetid",
                                 d.name as "datasetname",
                                 j.id ||  u.unitid || d.datasetid as "jrlunitdatasetid"
                              )
                            )
                        )
                    )
                )
            )
        )
    )
    
  
  )
    --into v_units
    from unitdatasets uds
    inner join units u on u.unitid = uds.unitid
    inner join unitsubcategories us on us.unitid = u.unitid
    inner join reportingdatasetmembers rdsm on rdsm.datasetid = uds.datasetid
    inner join datasets d on d.datasetid = rdsm.datasetid
    inner join journals j on j.unitid = us.unitid and j.datasetid = uds.datasetid
    where       and us.subcategoryid = 1057
        and rdsm.reportingdatasetid = 1608438
    group by  d.datasetid, d.name,u.unitid, u.code, u.name
    order by u.code;