Thread: xml

xml

From

"gary.wolfe"

Date:

19 January 2000, 22:30:46

When will postgres give XML output?

Gary

Re: xml

From

Pavel Stehule

Date:

23 March 2015, 15:31:38

2015-03-23 12:40 GMT+01:00 Ramesh T <rameshparnanditech@gmail.com>:

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 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.

Attachment

image.png

Re: xml

From

Tom Lane

Date:

23 March 2015, 17:09:57

Pavel Stehule <pavel.stehule@gmail.com> writes:
> result of xmlagg is not valid xml.

Really?  Either that's a bug, or it's declared wrong.

            regards, tom lane

Re: xml

From

Pavel Stehule

Date:

23 March 2015, 17:42:56

2015-03-23 15:09 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:
> result of xmlagg is not valid xml.

Really? Either that's a bug, or it's declared wrong.

I was not accurate. "<tag /><tag />" is not valid xml document - and xpath function doens't like it.

postgres=# select xpath('//tag/@x','<tag x="x"/><tag />'::xml);
ERROR: could not parse XML document
DETAIL: line 1: Extra content at the end of the document
<tag x="x"/><tag />
^
CONTEXT: SQL function "xpath" statement 1
postgres=# select xpath('//tag/@x','<x><tag x="x"/><tag /></x>'::xml);
xpath
-------
{x}
(1 row)

postgres=# select '<tag x="x"/><tag />'::xml;
xml
---------------------
<tag x="x"/><tag />
(1 row)

Regards

Pavel

regards, tom lane