2015-03-23 12:40 GMT+01:00 Ramesh T <rameshparnanditech@xxxxxxxxx>:
Hi all,SELECT xmlagg(xmlelement(name actor, xmlattributes(first_name))ORDER BY actor_id,',')from actor;the above code return following result,Question :i want retrieve result from above XML result likepenelope,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)
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.
