Re: xml output - Mailing list pgsql-novice

On 25 August 2010 09:53, Florian Henge <florian.henge82@web.de> wrote:
> Hi!
>
> Thanks, that worked perfectly for me, except for one problem.
>
> I have like 20 times the name "Joe" in my table, but want to get it only once in the xml output file.
>
> How can is do this? I tried SELECT DISTINCT but it didn't work.

If you're using PostgreSQL 8.4 or greater you can do:

WITH people AS (
    SELECT DISTINCT first_name
    FROM person
    ORDER BY first_name
)
SELECT
xmlelement(name words,
    xmlagg(
        xmlforest(first_name)
    )
)
FROM people

If not, you can do:

SELECT
xmlelement(name words,
    xmlagg(
        xmlforest(first_name)
    )
)
FROM (
    SELECT DISTINCT first_name
    FROM person
    ORDER BY first_name
) AS people

Although maybe there's a nicer way of doing it.  Note that if you want
aggregates in your results, use GROUP BY instead of DISTINCT.
--
Thom Brown
Registered Linux user: #516935