Chacham's Journal: Oracle: Use XML to concatenate results.

So, Oracle is a database that is supposed to help you pull results together. XML is a standard to help keep things separate (within the same document).

But if i want to concatenate some records from a TABLE, SQL falls short. We do aggregation, not concatenation (of separate records). Well, ironically, XML comes to the rescue.

In order to add separation identifiers, XML concatenates the results into one field. Well, strip the field of the identifiers, and voila, we have a concatenated result.

An example:

WITH
        Some_Table
AS
        (
          SELECT 'Moo' The_Important_Column FROM Dual UNION ALL
          SELECT 'Cow' FROM Dual UNION ALL
          SELECT 'Moo' FROM Dual UNION ALL
          SELECT 'Cow' FROM Dual UNION ALL
          SELECT 'Moo' FROM Dual UNION ALL
          SELECT 'Cow' FROM Dual
        )
SELECT
        REPLACE
        (
          REPLACE
          (
            XMLAgg
            (
              XMLElement
              (
                "A",
                The_Important_Column
              )
            ),
            '<A>'
          ),
          '</A>'
        ) Result
FROM
        Some_Table;

The result is

RESULT
----------------------------------------------------------------------
MooCowMooCowMooCow

This is basic stuff.

Laurent Schneider came up with an interesting way to COUNT all records in all TABLEs via another XML option (Here):

SELECT
        Table_Name,
        TO_NUMBER
        (
          ExtractValue
          (
                    XMLType(DBMS_XMLGen.GetXML('SELECT COUNT(*) C FROM ' || Table_Name)),
            '/ROWSET/ROW/C'
            )
        ) Total
FROM
        All_Tables
WHERE
        Owner = 'EHPAS_OBJ';