Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 



Forgot your password?
typodupeerror
DEAL: For $25 - Add A Second Phone Number To Your Smartphone for life! Use promo code SLASHDOT25. Also, Slashdot's Facebook page has a chat bot now. Message it for stories and more. Check out the new SourceForge HTML5 Internet speed test! ×
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:

"There is no statute of limitations on stupidity." -- Randomly produced by a computer program called Markov3.

Working...