Advanced Oracle SQL Aggregation Oracle Tips by Laurent Schneider

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced Oracle SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Aggregation

XMLAGGis used to aggregate multiple rows in a single XML document:

SELECT
   XMLELEMENT
   (
      EMP,
      XMLAGG
      (
         XMLELEMENT
         (
            DEPT,
            XMLATTRIBUTES(DEPTNO),
            XMLAGG
            (
               XMLELEMENT
               (
                  NAME,
                  ENAME
               )
               ORDER BY
                  ENAME
            )
         )
         ORDER BY
            DEPTNO
      )
  )
FROM
   EMP
GROUP BY
   DEPTNO;

EMP
----------------------------
<EMP>
   <DEPT DEPTNO="10">
      <NAME>CLARK</NAME>
      <NAME>KING</NAME>
      <NAME>MILLER</NAME>
   </DEPT>
   <DEPT DEPTNO="20">
      <NAME>ADAMS</NAME>
      <NAME>FORD</NAME>
      <NAME>JONES</NAME>
      <NAME>SCOTT</NAME>
      <NAME>SMITH</NAME>
   </DEPT>
   <DEPT DEPTNO="30">
      <NAME>ALLEN</NAME>
      <NAME>BLAKE</NAME>
      <NAME>JAMES</NAME>
      <NAME>MARTIN</NAME>
      <NAME>TURNER</NAME>
      <NAME>WARD</NAME>
   </DEPT>
</EMP>

The inner XMLAGG function aggregates the employees in each department and the outer XMLAGG aggregates the department in the whole table. The result is a well structured document that displays all the employees.

XMLSERIALIZE

XMLSERIALIZE converts an XMLTYPE to a CLOB, a VARCHAR2 or a BLOB. In 11g, the INDENT clause provides indented results for better readability. XMLSERIALIZEaccepts both well-formed content and a well-formed document.

 SELECT
   XMLSERIALIZE
   (
      DOCUMENT
      EXTRACT(OBJECT_VALUE, '//CANTON[@ID="VS"]')
      INDENT SIZE=3
   ) VS
FROM
   WORLD;

VS
--------------------------------------
<CANTON ID="VS">
   <NAME>Valais</NAME>
   <DETAILS>
      <ENTRY>1815-01-01</ENTRY>
      <LANGUAGE_LIST>
         <LANGUAGE>French</LANGUAGE>
         <LANGUAGE>German</LANGUAGE>
      </LANGUAGE_LIST>
   </DETAILS>
</CANTON>
XMLCAST

XMLCASTin 11g casts to various datatypes like NUMBER or DATE.

SELECT
   *
FROM
(
   SELECT
      XMLCAST
      (
        EXTRACT
        (
           S.COLUMN_VALUE,
           '/CANTON/@ID'
        )
        AS
        VARCHAR2(2)
      ) ID,
      XMLCAST
      (
         EXTRACT
         (
             S.COLUMN_VALUE,
             '/CANTON/NAME'
         )
         AS
         VARCHAR2(20)
      ) NAME,
      XMLCAST
      (
         EXTRACT
         (
            S.COLUMN_VALUE,
            '/CANTON/DETAILS/ENTRY'
         )
         AS
         DATE
      ) ENTRY
   FROM
      WORLD,
      TABLE(XMLSEQUENCE(EXTRACT(WORLD.OBJECT_VALUE, '//CANTON'))) S
   ORDER BY
      ENTRY
)
WHERE
   ROWNUM<5;
 

ID NAME                 ENTRY
-- -------------------- -----------
UR Uri                  01-JAN-1291
SZ Schwyz               01-JAN-1291
OW Obwald               01-JAN-1291
NW Nidwald              01-JAN-1291

ID and NAME are cast to strings and ENTRY is cast to a date.