Re: xml

2015-03-23 12:40 GMT+01:00 Ramesh T <rameshparnanditech(at)gmail(dot)com>:

> Hi all,
> SELECT xmlagg(xmlelement(
> name actor, xmlattributes(first_name)
> )ORDER BY actor_id,',')from actor;
>
> the above code return following result,
>
> [image: Inline image 1]
> Question :
>
> i want retrieve result from above XML result like
> penelope,nick,jennifer,jhony,,,,,
>
>
result of xmlagg is not valid xml.

if you use xmlelement, then you can get valid xml

ostgres=# select * from x;
a
---------------------------------------------------------------
<d><actor first_name="Pavel"/><actor first_name="Tomas"/></d>
(1 row)

postgres=# select unnest(xpath('//actor/@first_name',a)::text[]) from x;
unnest
--------
Pavel
Tomas
(2 rows)

Regards

Pavel

>
> xpath is their but unable to fix this query.
>
> any help how to get only names from query not xml.
>
>
>