Home » SQL & PL/SQL » SQL & PL/SQL » xml generation from db (Oracle 12c 2)
xml generation from db [message #677061] Wed, 14 August 2019 03:40 Go to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to generate the xml this way as follows. I am generating this using following code and output is also coming as expected. Is it right approach. Please help.

--company
--companycount (sum(deptcnt))
--        deptno
--        deptcnt sum(empcount)
--            employees
--                empdetails



SELECT
    XMLSERIALIZE(DOCUMENT XMLELEMENT(
        "company", XMLAGG(XMLFOREST(compcnt, deptdet))
    ))
FROM
    (
        SELECT
            SUM(empcnt) compcnt,
            XMLELEMENT(
                "department", XMLAGG(XMLFOREST(deptno, empcnt, empdet))
            ) deptdet
        FROM
            (
                SELECT
                    dept.deptno,
                    COUNT(emp.deptno) empcnt,
                    XMLELEMENT(
                        "employees", XMLAGG(XMLFOREST(ename, sal))
                    ) empdet
                FROM
                    emp,
                    dept
                WHERE
                    emp.deptno (+) = dept.deptno
                GROUP BY
                    dept.deptno
            )
    );



Thanks
Re: xml generation from db [message #677104 is a reply to message #677061] Mon, 19 August 2019 03:47 Go to previous message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

We have faced maximum length error with the above method,however with the below method it worked nicely as follows,

WITH emp_det AS (
    SELECT
        ename,
        sal,
        dept.deptno   dept_dept,
        emp.deptno    emp_dept
    FROM
        emp,
        dept
    WHERE
        emp.deptno (+) = dept.deptno
), emp_group AS (
    SELECT
        dept_dept,
        empcnt,
        SUM(empcnt) OVER() emp_sum
    FROM
        (
            SELECT
                dept_dept,
                COUNT(emp_dept) empcnt
            FROM
                emp_det
            GROUP BY
                dept_dept
        )
)
SELECT
    XMLSERIALIZE(DOCUMENT XMLELEMENT(
        "company",(XMLELEMENT(
            "companyDetails", XMLELEMENT(
                "COMPCNT", MAX(emp_sum)
            ), XMLAGG(XMLELEMENT(
                "department", XMLELEMENT(
                    "DEPTNO", dept_dept
                ), XMLELEMENT(
                    "EMPCNT", empcnt
                ),(
                    SELECT
                        XMLAGG(XMLELEMENT(
                            "employees", XMLAGG(XMLFOREST(ename, sal))
                        )) empdet
                    FROM
                        emp_det
                    WHERE
                        emp_group.dept_dept = emp_det.dept_dept
                    GROUP BY
                        dept_dept
                )
            )
                ORDER BY
                    dept_dept
            )
        ))
    ))
FROM
    emp_group;

Thanks
Previous Topic: plsql logic
Next Topic: regexp_replace
Goto Forum:
  


Current Time: Mon Sep 16 17:14:57 CDT 2019