Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Oracle: Use XML to concatenate results.

Chacham (981) writes | more than 6 years ago

Oracle 0

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.

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

cancel ×

0 comments

Check for New Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...