Follow Slashdot blog updates by subscribing to our blog RSS feed

 



Forgot your password?
typodupeerror
Get HideMyAss! VPN, PC Mag's Top 10 VPNs of 2016 for 55% off for a Limited Time ×
Oracle

Journal 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';

This discussion has been archived. No new comments can be posted.

Oracle: Use XML to concatenate results.

Comments Filter:

Top Ten Things Overheard At The ANSI C Draft Committee Meetings: (5) All right, who's the wiseguy who stuck this trigraph stuff in here?

Working...