Thread: xml output

hi there!i wanted to ask how to get an xml output from a table like
this:<words><first_name>joe</first_name><first_name>ian</first_name><first_name>carl</first_name><first_name>ben</first_name></words>what
ihave is the following: 
SELECT xmlelement(name words,


xmlforest(nachname))
FROM person
SELECT xmlelement(name words,xmlforest(nachname))FROM personbut that gives me
this:<words><first_name>joe</first_name></words><words><first_name>ian</first_name></words><words><first_name>carl</first_name></words><words><first_name>ben</first_name></words>thanks
inadvance!bye, flo   Neu: WEB.DE De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
   JetztDe-Mail-Adresse reservieren: https://produkte.web.de/go/demail02 

---------------------
Von: Thom Brown
Gesendet: 23.08.2010 14:09:05
An: Florian Henge
Betreff: Re: [NOVICE] xml output


On 23 August 2010 12:33, Florian Henge <[florian.henge82@web.de]> wrote:



hi there!

i wanted to ask how to get an xml output from a table like this:


joe
ian
carl
 ben


what i have is the following:


SELECT xmlelement(name words,


xmlforest(nachname))
FROM person
SELECT
xmlelement(name words,
xmlforest(nachname))
FROM person

but that gives me this:

joe
ian
 carl
ben


thanks in advance!

bye, flo






You need to use xmlagg:

SELECT
xmlelement(name words,
    xmlagg(
        xmlforest(first_name)
    )
)
FROM person

Regards
--
Thom Brown
Registered Linux user: #516935


_________________________________

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.

Thanks in advance!
___________________________________________________________
Neu: WEB.DE De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
Jetzt De-Mail-Adresse reservieren: https://produkte.web.de/go/demail02

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