Thread: XML path function

Hi everyone,
 
   I´m trying to undestand how to do select with xml path.

I have this xml example:

create table temp(id integer, xml text, Primary Key(id));

<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>
                <element1>
                    <element2>
                        <element3 id="1" name="2">
                             <name></name>
                             <element4>
                                 <element5></element5>
                                 <element5></element5>
                                 <element5></element5>
                             </element4>
                        </element3>
                    </element2>
                </element1>

Here go the questions:
1) How can i select all name elements from the xml?
2) How can i select all element3 id´s?
3) How can i select all  element5 that have element3 with attribute name="x"?


Thanks very much.  :)

On Jan 8, 2008 11:21 PM, x asasaxax <xanaruto@gmail.com> wrote:
> Hi everyone,
>
>    I´m trying to undestand how to do select with xml path.

First of all, what is your Postgres version? 8.3 with xml support or
older one with conrib/xml2?
In case of 8.3, you can find some examples in current docs:
http://www.postgresql.org/docs/8.3/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
If needed, XPath spec can be found here: http://www.w3.org/TR/xpath.
I can explain if something is difficult to understand.

--
Nikolay Samokhvalov  <nikolay@samokhvalov.com>
http://nikolay.samokhvalov.com

Postgresmen http://postgresmen.ru
OpenWebTechnologies http://openwebtech.ru

My Postgre version its the 8.2. I´ve reached to do the path i wanted, but when i do a explain analyze on the select it return 500 miliseconds. Is this a good search? Is there a way to slow down this time with postgre 8.3? What is a good time for xml xpath´s?

Thanks

On Jan 9, 2008 6:00 PM, x asasaxax <xanaruto@gmail.com> wrote:
> My Postgre version its the 8.2. I´ve reached to do the path i wanted, but
> when i do a explain analyze on the select it return 500 miliseconds. Is this
> a good search? Is there a way to slow down this time with postgre 8.3? What
> is a good time for xml xpath´s?

Unfortunately, this is the normal speed. The reason is that Postgres
does full XML parsing and XPath evaluation at runtume.

If you use

SELECT <columns list>
FROM <table>
WHERE <xpath function> = <constant>,

then functional indexes over xpath function may help. Also, you could
use tsearch2 + functional XPath indexes to index text data from your
XML, if you have corresponding needs.

Unfortunately, that is almost all you can do speeding up your XPath
queries at the moment, and in terms of performance there is no any
major improvements in 8.3 either.

--
Nikolay Samokhvalov  <nikolay@samokhvalov.com>
http://nikolay.samokhvalov.com

Postgresmen http://postgresmen.ru
OpenWebTechnologies http://openwebtech.ru

Can you tell me, in how much time did the query will take with indexes + tsearch2?
How much time take a satisfactory query?
Can you show me some examples with tsearch2 and xml indexes?

Thanks