[GENERAL] xpath functionerror
Hi, My current database version is PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit. I am trying to use function xpath in my query and got following error. ERROR: function xpath(unknown, text) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. I could see the library pgxml.so libray in /usr/pgsql-9.1/lib . Could someone please advise me what more needs to be done in order to run xpath queris? Thanks in advance. -- View this message in context: http://postgresql.1045698.n5.nabble.com/xpath-functionerror-tp5793724.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath functionerror
Thanks David for your quick response. My original objective was to get the value of a particular node in xml which is stored as text datatype in the table. For example : ?xml version=1.0? -settings-F999amperage16/amperageamperagesteps//F/settings I want to extract the value of amperage as '16'. Like that I may have many nodes in xml want to get the report to show them in different columns. I used to do this with oracle using xml functions provided. Trying to figure out same in postgres. Thanks again for your help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/xpath-functionerror-tp5793724p5793740.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath functionerror
sparikh wrote Hi, My current database version is PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit. I am trying to use function xpath in my query and got following error. ERROR: function xpath(unknown, text) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. I could see the library pgxml.so libray in /usr/pgsql-9.1/lib . Could someone please advise me what more needs to be done in order to run xpath queris? Thanks in advance. The valid function signature is: xpath(xpath text, xmlcontent xml, [ nsarray text[][] ]) http://www.postgresql.org/docs/9.1/static/functions-xml.html The unknown above will be converted to text but the text above will never be converted to xml. You must perform this conversion yourself and pass the converted value - now of type xml - to the function. The documentation explains how to perform this conversion. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/xpath-functionerror-tp5793724p5793727.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath functionerror
sparikh wrote Thanks David for your quick response. My original objective was to get the value of a particular node in xml which is stored as text datatype in the table. For example : ?xml version=1.0? - settings - F999 amperage 16 /amperage amperagesteps/ /F /settings I want to extract the value of amperage as '16'. Like that I may have many nodes in xml want to get the report to show them in different columns. I used to do this with oracle using xml functions provided. Trying to figure out same in postgres. Thanks again for your help. And the same goes for PostgreSQL but you need to use the correct data types; xml functions operate on XML instead of TEXT because that way they don't really need to deal with bad text input - the type conversion function takes care of making sure the text is valid XML. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/xpath-functionerror-tp5793724p5793742.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xpath index not being used
Hello, I've searched the mailing list and I tried using defining a xpath index per the post, but my query is still not using it. Essentially, I'm storing a fragment of xml and I want to create xpath indexes on them. The two rows I'm going to insert look like this (alex bob are the only names repeated): namesnamefrank/namenamemason/namenamebob/namenamealex/name/names namesnamealex/namenamebob/namenamecola/namenamedoda/name/names create table test (data xml); CREATE TABLE CREATE INDEX name_test ON test (((xpath('//names/name/text()', data))[1]::text)); CREATE INDEX I can select with a where clause without issue: select * from test where ((xpath('//names/name[. =bob]/text()', data))[1]::text) = 'bob'; data -- namesnamealex/namenamebob/namenamecola/namenamedoda/name/names namesnamefrank/namenamemason/namenamebob/namenamealex/name/names (2 rows) However, when I check which index it's using, it's not using the xpath index: explain select * from test where ((xpath('//names/name[. =bob]/text()', data))[1]::text) = 'bob'; QUERY PLAN - Seq Scan on test (cost=0.00..1.03 rows=1 width=32) Filter: (((xpath('//names/name[. =bob]/text()'::text, data, '{}'::text[]))[1])::text = 'bob'::text) (2 rows) Any help on what I'm doing wrong would be appreciated.
Re: [GENERAL] xpath index not being used
On 19/07/10 05:41, Irooniam wrote: However, when I check which index it's using, it's not using the xpath index: explain select * from test where ((xpath('//names/name[. =bob]/text()', data))[1]::text) = 'bob'; QUERY PLAN - Seq Scan on test (cost=0.00..1.03 rows=1 width=32) Filter: (((xpath('//names/name[. =bob]/text()'::text, data, '{}'::text[]))[1])::text = 'bob'::text) (2 rows) Any help on what I'm doing wrong would be appreciated. Your data is too trivial. Pg thinks it'll be faster to do a seq scan and filter than use the index to do the work. It'd be more helpful if you can post EXPLAIN ANALYZE output from your real data. AFAIK the planner isn't very good at factoring in function execution costs and number of function executions required when choosing between index use and filtered seqscans. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Xpath Index in PostgreSQL
I am trying to setup an index on an xpath expression but the query never uses the index. Could someone enlighten me please ? Here is the setup : CREATE TABLE time_series ( id bigint NOT NULL, name character varying NOT NULL, timestamp timestamp with time zone NOT NULL, start_date timestamp with time zone, end_date timestamp with time zone, . external_attributes xml, .. ) WITH ( OIDS=FALSE ); CREATE INDEX xml_index ON time_series USING btree ((xpath('/AttributeList/Attributes/Attribute/Name/text()'::text, external_attributes)::text[])); And here is the query : select id, name from time_series where (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text='Attribute100'
Re: [GENERAL] Xpath Index in PostgreSQL
Chris Roffler croff...@earthlink.net writes: I am trying to setup an index on an xpath expression but the query never uses the index. Could someone enlighten me please ? Here is the setup : CREATE INDEX xml_index ON time_series USING btree ((xpath('/AttributeList/Attributes/Attribute/Name/text()'::text, external_attributes)::text[])); And here is the query : select id, name from time_series where (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text='Attribute100' Doesn't work that way --- subscripting isn't an indexable operation. To make that query fast with a standard index, you'd need the index to be on (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Xpath Index in PostgreSQL
Thanks for your fast response I have the following sql statement now : CREATE INDEX xml_index ON time_series USING btree (xpath('/AttributeList/Attributes/Attribute/Name/text()',external_attributes))[1]::text And I am getting the following error : ERROR: syntax error at or near [ LINE 6: ...butes/Attribute/Name/text()',external_attributes))[1]::text; Any idea ? Thanks Chris ^ On Fri, Mar 5, 2010 at 3:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Chris Roffler croff...@earthlink.net writes: I am trying to setup an index on an xpath expression but the query never uses the index. Could someone enlighten me please ? Here is the setup : CREATE INDEX xml_index ON time_series USING btree ((xpath('/AttributeList/Attributes/Attribute/Name/text()'::text, external_attributes)::text[])); And here is the query : select id, name from time_series where (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text='Attribute100' Doesn't work that way --- subscripting isn't an indexable operation. To make that query fast with a standard index, you'd need the index to be on (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text regards, tom lane
Re: [GENERAL] Xpath Index in PostgreSQL
Figured it out , thanks for all your help ( had missing brackets ) Here is the sql statement CREATE INDEX xml_index ON time_series USING btree (( (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text)); Thanks Chris On Fri, Mar 5, 2010 at 4:18 PM, Chris Roffler croff...@earthlink.netwrote: Thanks for your fast response I have the following sql statement now : CREATE INDEX xml_index ON time_series USING btree (xpath('/AttributeList/Attributes/Attribute/Name/text()',external_attributes))[1]::text And I am getting the following error : ERROR: syntax error at or near [ LINE 6: ...butes/Attribute/Name/text()',external_attributes))[1]::text; Any idea ? Thanks Chris ^ On Fri, Mar 5, 2010 at 3:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Chris Roffler croff...@earthlink.net writes: I am trying to setup an index on an xpath expression but the query never uses the index. Could someone enlighten me please ? Here is the setup : CREATE INDEX xml_index ON time_series USING btree ((xpath('/AttributeList/Attributes/Attribute/Name/text()'::text, external_attributes)::text[])); And here is the query : select id, name from time_series where (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text='Attribute100' Doesn't work that way --- subscripting isn't an indexable operation. To make that query fast with a standard index, you'd need the index to be on (xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text regards, tom lane
Re: [GENERAL] xpath
try to cast to xml xml_payload::xml El mié, 10-02-2010 a las 12:39 +0300, Allan Kamau escribió: As advised by Peter, Below is an example (including the ddl and dml statements), it _drops_ and creates a table called simple_table and a sequence called simple_table_seq both in the public schema. DROP SEQUENCE IF EXISTS simple_table_seq CASCADE; CREATE SEQUENCE simple_table_seq; DROP TABLE IF EXISTS simple_table CASCADE; CREATE TABLE simple_table (id INTEGER NOT NULL DEFAULT NEXTVAL('simple_table_seq') ,xml_payload TEXT ,PRIMARY KEY(id) ) ; INSERT INTO simple_table ( id ,xml_payload ) SELECT nextval('simple_table_seq')AS id ,'docname first=David last=Marston/some text/doc' AS xml_payload ; SELECT a.id,a.xml_payload FROM simple_table a LIMIT 1; SELECT xpath('/doc/name/@first',SELECT a.xml_payload FROM simple_table a LIMIT 1); SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM simple_table a LIMIT 1; SELECT xpath('/doc/name/@first','docname first=David last=Marston/some text/doc'); DROP SEQUENCE IF EXISTS simple_table_seq CASCADE; DROP TABLE IF EXISTS simple_table CASCADE; Allan. On Wed, Feb 10, 2010 at 11:34 AM, Otandeka Simon Peter sotand...@gmail.com wrote: Allan, Postgres is very strict on variable types and char conversion. I have a feeling you are trying to access data from a varchar feild using an integer... Can you paste here your schema for that table? P. On Wed, Feb 10, 2010 at 11:06 AM, Allan Kamau kamaual...@gmail.com wrote: Hi, I am running postgreSQL-8.4.2. I have a table that stores a single xml document per row in one of it's fields. I would like to use xpath to retrieve portions of these xml documents. Is there a way to do so. (I am running postgreSQL 8.4.2 configured (built) with --with-libxml and --with-libxslt options) I have looked at 'xpath' but I am unable to get it work for table fields. The command below works. SELECT xpath('/doc/name/@first','docname first=David last=Marston/.../doc'); The command below seems not to execute successfully SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM staging.simple_table a WHERE a.id=1; HINT: No function matches the given name and argument types. You might need to add explicit type casts. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xpath
Hi, I am running postgreSQL-8.4.2. I have a table that stores a single xml document per row in one of it's fields. I would like to use xpath to retrieve portions of these xml documents. Is there a way to do so. (I am running postgreSQL 8.4.2 configured (built) with --with-libxml and --with-libxslt options) I have looked at 'xpath' but I am unable to get it work for table fields. The command below works. SELECT xpath('/doc/name/@first','docname first=David last=Marston/.../doc'); The command below seems not to execute successfully SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM staging.simple_table a WHERE a.id=1; HINT: No function matches the given name and argument types. You might need to add explicit type casts. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath
Allan, Postgres is very strict on variable types and char conversion. I have a feeling you are trying to access data from a varchar feild using an integer... Can you paste here your schema for that table? P. On Wed, Feb 10, 2010 at 11:06 AM, Allan Kamau kamaual...@gmail.com wrote: Hi, I am running postgreSQL-8.4.2. I have a table that stores a single xml document per row in one of it's fields. I would like to use xpath to retrieve portions of these xml documents. Is there a way to do so. (I am running postgreSQL 8.4.2 configured (built) with --with-libxml and --with-libxslt options) I have looked at 'xpath' but I am unable to get it work for table fields. The command below works. SELECT xpath('/doc/name/@first','docname first=David last=Marston/.../doc'); The command below seems not to execute successfully SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM staging.simple_table a WHERE a.id=1; HINT: No function matches the given name and argument types. You might need to add explicit type casts. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath
On Wed, Feb 10, 2010 at 11:34 AM, Otandeka Simon Peter sotand...@gmail.com wrote: Allan, Postgres is very strict on variable types and char conversion. I have a feeling you are trying to access data from a varchar feild using an integer... Can you paste here your schema for that table? P. On Wed, Feb 10, 2010 at 11:06 AM, Allan Kamau kamaual...@gmail.com wrote: Hi, I am running postgreSQL-8.4.2. I have a table that stores a single xml document per row in one of it's fields. I would like to use xpath to retrieve portions of these xml documents. Is there a way to do so. (I am running postgreSQL 8.4.2 configured (built) with --with-libxml and --with-libxslt options) I have looked at 'xpath' but I am unable to get it work for table fields. The command below works. SELECT xpath('/doc/name/@first','docname first=David last=Marston/.../doc'); The command below seems not to execute successfully SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM staging.simple_table a WHERE a.id=1; HINT: No function matches the given name and argument types. You might need to add explicit type casts. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general As advised by Peter, Below is an example (including the ddl and dml statements), it drops and creates a table called simple_table and a sequence called simple_table_seq both in the public schema. Please ensure this objects if prexisting are not of importance to you. DROP SEQUENCE IF EXISTS simple_table_seq CASCADE; CREATE SEQUENCE simple_table_seq; DROP TABLE IF EXISTS simple_table CASCADE; CREATE TABLE simple_table (id INTEGER NOT NULL DEFAULT NEXTVAL('simple_table_seq') ,xml_payload TEXT ,PRIMARY KEY(id) ) ; INSERT INTO simple_table ( id ,xml_payload ) SELECT nextval('simple_table_seq')AS id ,'docname first=David last=Marston/some text/doc' AS xml_payload ; SELECT a.id,a.xml_payload FROM simple_table a LIMIT 1; SELECT xpath('/doc/name/@first',SELECT a.xml_payload FROM simple_table a LIMIT 1); SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM simple_table a LIMIT 1; SELECT xpath('/doc/name/@first','docname first=David last=Marston/some text/doc'); DROP SEQUENCE IF EXISTS simple_table_seq CASCADE; DROP TABLE IF EXISTS simple_table CASCADE; Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath
As advised by Peter, Below is an example (including the ddl and dml statements), it _drops_ and creates a table called simple_table and a sequence called simple_table_seq both in the public schema. DROP SEQUENCE IF EXISTS simple_table_seq CASCADE; CREATE SEQUENCE simple_table_seq; DROP TABLE IF EXISTS simple_table CASCADE; CREATE TABLE simple_table (id INTEGER NOT NULL DEFAULT NEXTVAL('simple_table_seq') ,xml_payload TEXT ,PRIMARY KEY(id) ) ; INSERT INTO simple_table ( id ,xml_payload ) SELECT nextval('simple_table_seq')AS id ,'docname first=David last=Marston/some text/doc' AS xml_payload ; SELECT a.id,a.xml_payload FROM simple_table a LIMIT 1; SELECT xpath('/doc/name/@first',SELECT a.xml_payload FROM simple_table a LIMIT 1); SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM simple_table a LIMIT 1; SELECT xpath('/doc/name/@first','docname first=David last=Marston/some text/doc'); DROP SEQUENCE IF EXISTS simple_table_seq CASCADE; DROP TABLE IF EXISTS simple_table CASCADE; Allan. On Wed, Feb 10, 2010 at 11:34 AM, Otandeka Simon Peter sotand...@gmail.com wrote: Allan, Postgres is very strict on variable types and char conversion. I have a feeling you are trying to access data from a varchar feild using an integer... Can you paste here your schema for that table? P. On Wed, Feb 10, 2010 at 11:06 AM, Allan Kamau kamaual...@gmail.com wrote: Hi, I am running postgreSQL-8.4.2. I have a table that stores a single xml document per row in one of it's fields. I would like to use xpath to retrieve portions of these xml documents. Is there a way to do so. (I am running postgreSQL 8.4.2 configured (built) with --with-libxml and --with-libxslt options) I have looked at 'xpath' but I am unable to get it work for table fields. The command below works. SELECT xpath('/doc/name/@first','docname first=David last=Marston/.../doc'); The command below seems not to execute successfully SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM staging.simple_table a WHERE a.id=1; HINT: No function matches the given name and argument types. You might need to add explicit type casts. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] XPath PostgreSQL 8.4
It looks like I have to abandon xml2 functions in PostgreSQL 8.4. The problem is I can't seem to find an incantation of xpath that will perform the same thing. I have tried the following snippet: select xpath('/trade/trade-info/id/text()', cast(xml as xml))[1] as id from risk.trade_table which, from the documentation should give me the first (and only in this case) xml node text value for the XPath expression. Instead I get the following error message from the SQL parser: ERROR: syntax error at or near [ LINE 1: ...h('/trade/trade-info/id/text()', cast(xml as xml))[1] as id ... ^ ** Error ** ERROR: syntax error at or near [ SQL state: 42601 Character: 62 When I run the select statement without an array index, it correctly returns a single column of arrays of length one (expected) for the XPath node text value. Can anyone shine a light on what I am doing wrong? The build of the 8.4 database I am using is 8.4.1.9250. Thanks, Karl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XPath PostgreSQL 8.4
Karl Koster klkos...@optonline.net wrote: It looks like I have to abandon xml2 functions in PostgreSQL 8.4. The problem is I can't seem to find an incantation of xpath that will perform the same thing. I have tried the following snippet: select xpath('/trade/trade-info/id/text()', cast(xml as xml))[1] as id from risk.trade_table which, from the documentation should give me the first (and only in this case) xml node text value for the XPath expression. Instead I get the following error message from the SQL parser: ERROR: syntax error at or near [ LINE 1: ...h('/trade/trade-info/id/text()', cast(xml as xml))[1] as id ... ^ ** Error ** ERROR: syntax error at or near [ SQL state: 42601 Character: 62 When I run the select statement without an array index, it correctly returns a single column of arrays of length one (expected) for the XPath node text value. Can anyone shine a light on what I am doing wrong? The build of the 8.4 database I am using is 8.4.1.9250. You have to put brackets around the function call: | select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id from risk.trade_table; Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XPath PostgreSQL 8.4
I wrote: [...] You have to put brackets around the function call: | select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id from risk.trade_table; ... or, after a look in the dictionary, whatever you call ( and ) :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xpath() subquery for empty array
In postgres 8.4 When running xpath() queries it seems that empty results are always returned. So if I query a table containing 1000 XML documents a 1000 rows will always be fetched even if the xpath() element of the query only matches 10 documents. The documentation states: The function |xpath| evaluates the XPath expression /xpath/ against the XML value /xml/. It returns an array of XML values corresponding to the node set produced by the XPath expression. Yet it seems that empty node sets (arrays) are also returned. So, my thought was to run a subquery to eliminate empty arrays, e.g.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x [test for empty array?] How do I test for an empty array in postgres? Thanks in advance, Roy
Re: [GENERAL] xpath() subquery for empty array
Roy Walter wrote: In postgres 8.4 When running xpath() queries it seems that empty results are always returned. So if I query a table containing 1000 XML documents a 1000 rows will always be fetched even if the xpath() element of the query only matches 10 documents. The documentation states: The function |xpath| evaluates the XPath expression /xpath/ against the XML value /xml/. It returns an array of XML values corresponding to the node set produced by the XPath expression. Yet it seems that empty node sets (arrays) are also returned. So, my thought was to run a subquery to eliminate empty arrays, e.g.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x [test for empty array?] How do I test for an empty array in postgres? Thanks in advance, Roy WHERE x != array[]::xml[] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath() subquery for empty array
Scott Bailey wrote: Roy Walter wrote: In postgres 8.4 When running xpath() queries it seems that empty results are always returned. So if I query a table containing 1000 XML documents a 1000 rows will always be fetched even if the xpath() element of the query only matches 10 documents. The documentation states: The function |xpath| evaluates the XPath expression /xpath/ against the XML value /xml/. It returns an array of XML values corresponding to the node set produced by the XPath expression. Yet it seems that empty node sets (arrays) are also returned. So, my thought was to run a subquery to eliminate empty arrays, e.g.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x [test for empty array?] How do I test for an empty array in postgres? Thanks in advance, Roy WHERE x != array[]::xml[] No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.387 / Virus Database: 270.13.12/2233 - Release Date: 07/12/09 08:20:00 Thanks Scott but that throws up a syntax error (at the closing bracket of array[]): ERROR: syntax error at or near ] LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[] ^ -- Roy
Re: [GENERAL] xpath() subquery for empty array
On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote: Scott Bailey wrote: Roy Walter wrote: How do I test for an empty array in postgres? WHERE x != array[]::xml[] Thanks Scott but that throws up a syntax error (at the closing bracket of array[]): ERROR: syntax error at or near ] LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[] Even if that syntax was correct it wouldn't work, xml values don't have an equality operator defined for them. I've normally tested the array size to figure out when they're empty, something like: array_upper($1,1) 0 However, I've just noticed that this returns NULL rather than zero as I was expecting for an empty array (i.e. the literal '{}'). It also doesn't seem to do useful things if you're using unusual bounds on your array. Bah, the semantics of arrays in PG always seem over-complicated to me! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath() subquery for empty array
Sam Mason wrote: On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote: Scott Bailey wrote: Roy Walter wrote: How do I test for an empty array in postgres? WHERE x != array[]::xml[] Thanks Scott but that throws up a syntax error (at the closing bracket of array[]): ERROR: syntax error at or near ] LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[] Even if that syntax was correct it wouldn't work, xml values don't have an equality operator defined for them. I've normally tested the array size to figure out when they're empty, something like: array_upper($1,1) 0 However, I've just noticed that this returns NULL rather than zero as I was expecting for an empty array (i.e. the literal '{}'). It also doesn't seem to do useful things if you're using unusual bounds on your array. Bah, the semantics of arrays in PG always seem over-complicated to me! This worked on both 8.3 and 8.4 SELECT * FROM ( VALUES( '{}'::_xml),('{root/}'::_xml) ) sub WHERE array_upper(column1, 1) 0Sam Mason s...@samason.me.uk And as Sam noted, array_upper needs to return null if the array is empty because 0 and -1 can be valid indexes for arrays in postgres. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath() subquery for empty array
Scott Bailey wrote: Sam Mason wrote: On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote: Scott Bailey wrote: Roy Walter wrote: How do I test for an empty array in postgres? WHERE x != array[]::xml[] Thanks Scott but that throws up a syntax error (at the closing bracket of array[]): ERROR: syntax error at or near ] LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[] Even if that syntax was correct it wouldn't work, xml values don't have an equality operator defined for them. I've normally tested the array size to figure out when they're empty, something like: array_upper($1,1) 0 However, I've just noticed that this returns NULL rather than zero as I was expecting for an empty array (i.e. the literal '{}'). It also doesn't seem to do useful things if you're using unusual bounds on your array. Bah, the semantics of arrays in PG always seem over-complicated to me! This worked on both 8.3 and 8.4 SELECT * FROM ( VALUES( '{}'::_xml),('{root/}'::_xml) ) sub WHERE array_upper(column1, 1) 0Sam Mason s...@samason.me.uk And as Sam noted, array_upper needs to return null if the array is empty because 0 and -1 can be valid indexes for arrays in postgres. Thanks, that's great, I'm delighted that there's a solution. Where exactly does that fit in terms of my original query, i.e.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x [test for empty array?] -- Roy
Re: [GENERAL] xpath() subquery for empty array
On Sun, Jul 12, 2009 at 09:49:15PM +0100, Roy Walter wrote: Where exactly does that fit in terms of my original query, i.e.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x [test for empty array?] Something like the following should do the right thing: SELECT x FROM ( SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE array_upper(x, 1) 0; -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath() subquery for empty array
Sam Mason wrote: On Sun, Jul 12, 2009 at 09:49:15PM +0100, Roy Walter wrote: Where exactly does that fit in terms of my original query, i.e.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x [test for empty array?] Something like the following should do the right thing: SELECT x FROM ( SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE array_upper(x, 1) 0; Thank you, works just fine. I thought it was more complicated than that :-) -- Roy
Re: [GENERAL] xpath functions
On Thursday 19 February 2009 16:46:42 Francisco wrote: Hi List, I have installed postgreSQL 8.3 in my Ubuntu Hardy Heron. I want to use xpath functions (like xpath_table), but I can't. An error appears xpath_table does not exists. How could I intregrate xpath funtions with my postgreSQL without reinstalling it? xpath_table is not the name of a function provided by PostgreSQL. Check the documentation for the real names and signatures. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath functions
I have executed pg_config and I have the libxml. I think the problem is that xml2 is not enabled, any idea how to enable it? 2009/2/19 Osvaldo Kussama osvaldo.kuss...@gmail.com 2009/2/19 Francisco ricke...@gmail.com: I saw it,but it says: Use of many of these functions requires the installation to have been built with configure --with-libxml When I installed postgreSQL I did not include this option, have I to reinstall postgresql? 2009/2/19 Osvaldo Kussama osvaldo.kuss...@gmail.com 2009/2/19 Francisco ricke...@gmail.com: Hi List, I have installed postgreSQL 8.3 in my Ubuntu Hardy Heron. I want to use xpath functions (like xpath_table), but I can't. An error appears xpath_table does not exists. How could I intregrate xpath funtions with my postgreSQL without reinstalling it? Any solution? Thanks ;) From manual: From PostgreSQL 8.3 on, there is XML-related functionality based on the SQL/XML standard in the core server. That functionality covers XML syntax checking and XPath queries, which is what this module does, and more, but the API is not at all compatible. http://www.postgresql.org/docs/current/interactive/xml2.html PostgreSQL 8.3 XML Functions: http://www.postgresql.org/docs/current/interactive/functions-xml.html Verify with pg_config: http://www.postgresql.org/docs/current/interactive/app-pgconfig.html Osvaldo -- Un día te encontré y al otro te perdí
[GENERAL] xpath functions
Hi List, I have installed postgreSQL 8.3 in my Ubuntu Hardy Heron. I want to use xpath functions (like xpath_table), but I can't. An error appears xpath_table does not exists. How could I intregrate xpath funtions with my postgreSQL without reinstalling it? Any solution? Thanks ;)
Re: [GENERAL] xpath functions
I saw it,but it says: Use of many of these functions requires the installation to have been built with configure --with-libxml When I installed postgreSQL I did not include this option, have I to reinstall postgresql? 2009/2/19 Osvaldo Kussama osvaldo.kuss...@gmail.com 2009/2/19 Francisco ricke...@gmail.com: Hi List, I have installed postgreSQL 8.3 in my Ubuntu Hardy Heron. I want to use xpath functions (like xpath_table), but I can't. An error appears xpath_table does not exists. How could I intregrate xpath funtions with my postgreSQL without reinstalling it? Any solution? Thanks ;) From manual: From PostgreSQL 8.3 on, there is XML-related functionality based on the SQL/XML standard in the core server. That functionality covers XML syntax checking and XPath queries, which is what this module does, and more, but the API is not at all compatible. http://www.postgresql.org/docs/current/interactive/xml2.html PostgreSQL 8.3 XML Functions: http://www.postgresql.org/docs/current/interactive/functions-xml.html Osvaldo -- Un día te encontré y al otro te perdí
[GENERAL] XPath to search for elements in a sequence
With XML similar to: a b c 1 / c c 2 / c c 3 / c / b I'm trying to create an xpath expression (for a postgresql query) that will return if is a particular value and not that is all three values. What I currently have (which does not work) is: select * from someTable where xpath ('//uim:a/text()', job, ARRAY[ ARRAY['uim',' http://www.cmpy.com/uim '] ])::text[] IN (ARRAY['1','3']); If I try with ARRAY['1'] this will not return any values but with ARRAY['1','2','3'] it will return all three. How can I select based on a single element in a sequence? Thanks.
[GENERAL] xpath and xml namespaces
Hi, i'm having trouble getting xpath in Postgres 8.2 to recognise xml namespaces...could someone please point me to a good reference for the xml2 lib? Cheers Pete
Re: [GENERAL] xpath and xml namespaces
On 3/4/07, Peter Schonefeld [EMAIL PROTECTED] wrote: Hi, i'm having trouble getting xpath in Postgres 8.2 to recognise xml namespaces...could someone please point me to a good reference for the xml2 lib? This is FAQ-like question. The thing is that XPath spec doesn't define any default mappings for namespaces. It's considered as entirely user's problem -- he should manually define namespaces mappings to use namespace prefixes in XPath expressions. If you use contrib/xml2 I'm afraid there is no way to use namespaces in XPath w/o changing contib's code itself... To enable namespaces using libxml2 one should use xmlXPathRegisterNs(): http://xmlsoft.org/html/libxml-xpathInternals.html#xmlXPathRegisterNs I hope 8.3 will have XPath function with namespaces support in core. -- Best regards, Nikolay ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] XPath and XML support
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Gray Sent: 29 August 2006 22:49 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] XPath and XML support On Thu, 24 Aug 2006 13:56:12 -0700, Frank Cohen wrote: Thanks Nikolay: Seeing as xml2 hasn't been ported to Windows yet makes me wonder if this is going to be the best way to use XML in PostgreSQL in the long-term? Is there anything else on the boards? - Frank There's a lot of scope to do better than the current support, certainly. However, the not ported to Windows is slightly misleading - the code (like a lot of contrib code) compiles fine under Windows. Because of the dependency on libxml2, it might not often be distributed as a binary, but people are definitely using on Win32 (I'm not really a Win32 user but I have tried it and it did work). It was only ported relatively recently (I know, I did it). It will ship with the Windows PostgreSQL 8.2 binary installer. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] XPath and XML support
On Thu, 24 Aug 2006 13:56:12 -0700, Frank Cohen wrote: Thanks Nikolay: Seeing as xml2 hasn't been ported to Windows yet makes me wonder if this is going to be the best way to use XML in PostgreSQL in the long-term? Is there anything else on the boards? - Frank There's a lot of scope to do better than the current support, certainly. However, the not ported to Windows is slightly misleading - the code (like a lot of contrib code) compiles fine under Windows. Because of the dependency on libxml2, it might not often be distributed as a binary, but people are definitely using on Win32 (I'm not really a Win32 user but I have tried it and it did work). John ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] XPath and XML support
On 8/24/06, Jeff Davis [EMAIL PROTECTED] wrote: On Wed, 2006-08-23 at 16:04 -0700, Frank Cohen wrote: These look like good XPath functions. Are they actually in PSQL 8.1.4? I did not find them in the Windows installed version. If not, are they recommended? Look at contrib/xml2. The function xpath_table is in that module. I'm not familiar enough with windows to tell you how to install it, but hopefully this gives you a good starting point. AFAIK, contrib/xml2 hasn't been ported to Windows yet (at least, this is in the current TODO list). -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] XPath and XML support
Thanks Nikolay: Seeing as xml2 hasn't been ported to Windows yet makes me wonder if this is going to be the best way to use XML in PostgreSQL in the long-term? Is there anything else on the boards? - Frank On Aug 24, 2006, at 4:17 AM, Nikolay Samokhvalov wrote: On 8/24/06, Jeff Davis [EMAIL PROTECTED] wrote: On Wed, 2006-08-23 at 16:04 -0700, Frank Cohen wrote: These look like good XPath functions. Are they actually in PSQL 8.1.4? I did not find them in the Windows installed version. If not, are they recommended? Look at contrib/xml2. The function xpath_table is in that module. I'm not familiar enough with windows to tell you how to install it, but hopefully this gives you a good starting point. AFAIK, contrib/xml2 hasn't been ported to Windows yet (at least, this is in the current TODO list). -- Best regards, Nikolay ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] XPath and XML support
I'm doing a performance and scalability test project for a PostgreSQL user who is working with geospatial data. The data is in GML form. For instance: watch version=9 id=flight time=2006-08-16T10:16:36.125-07:00 point latitude=7 longitude=55/ detail gml:Point xmlns:gml=http://www.opengis.net/gml; gml:coordinates7.0,9.0/gml:coordinates /gml:Point /detail /watch I installed PostGIS and it supports Point and coordinates very well. I an not sure what I should do with the XML content using PostgreSQL? For example, 1) Tom Dyson at http://www.throwingbeans.org/ postgresql_and_xml_updated.html says PostgreSQL 8 ships with xpath_table to evaluate a set of XPath queries and returns the results as a virtual table. These look like good XPath functions. Are they actually in PSQL 8.1.4? I did not find them in the Windows installed version. If not, are they recommended? 2) What is the relationship of the XPath functions to the PostGIS functions? For example, will I be able to use an index to a PostGIS field? 3) If the XPath operators are not allowed, should I shred the GML into two tables (point and detail)? Any help is much appreciated. Thanks. -Frank --- Frank Cohen, Raining Data, http://www.RainingData.com, phone: 408 236 7604 http://www.xquerynow.com for free XML, XQuery and native XML database tips, techniques and solutions. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] XPath and XML support
On Wed, 2006-08-23 at 16:04 -0700, Frank Cohen wrote: I'm doing a performance and scalability test project for a PostgreSQL user who is working with geospatial data. The data is in GML form. For instance: watch version=9 id=flight time=2006-08-16T10:16:36.125-07:00 point latitude=7 longitude=55/ detail gml:Point xmlns:gml=http://www.opengis.net/gml; gml:coordinates7.0,9.0/gml:coordinates /gml:Point /detail /watch I installed PostGIS and it supports Point and coordinates very well. I an not sure what I should do with the XML content using PostgreSQL? For example, 1) Tom Dyson at http://www.throwingbeans.org/ postgresql_and_xml_updated.html says PostgreSQL 8 ships with xpath_table to evaluate a set of XPath queries and returns the results as a virtual table. These look like good XPath functions. Are they actually in PSQL 8.1.4? I did not find them in the Windows installed version. If not, are they recommended? Look at contrib/xml2. The function xpath_table is in that module. I'm not familiar enough with windows to tell you how to install it, but hopefully this gives you a good starting point. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] XPath question - big trouble
Since you brought up the subject, will this //[EMAIL PROTECTED] = 02] be slower than /mydocument/body/chapter/contents/[EMAIL PROTECTED] = 02] when handling a big document? I mean from the query time point of view ... I have no idea about the postgres XML implementation internals, I never used it, but for other XML processors I know the speed would be the same, and I would expect the same for postgres too. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] XPath question - big trouble
Hello everyone, First of all, thank you for integrating XPath in Postgresql. But, as you will see, this is a desperate cry for help: 1. the actual context: Postgresql 8.1.4, Fedora Core 5 DATABASE = CREATE TABLE xmldocuments ( id int8 NOT NULL, rawdata text, title varchar(255), CONSTRAINT pk_xmldocs PRIMARY KEY (id) ) WITHOUT OIDS; RAWDATA contents model = mydocument title/title body paragraph id=87 style=para21/paragraph chapter title/title contents paragraph id=01 style=para01/paragraph paragraph id=02 style=para01/paragraph paragraph id=03 style=para01/paragraph paragraph id=04 style=para01/paragraph paragraph id=05 style=para01/paragraph /contents /chapter chapter title/title contents paragraph id=654 style=para01/paragraph paragraph id=54 style=para02/paragraph paragraph id=64 style=para01/paragraph paragraph id=98 style=para02/paragraph paragraph id=65 style=para02/paragraph paragraph id=655 style=para01/paragraph /contents /chapter /body /mydocument I have 4 or 5 lines in the table xmldocuments; on every record, rawdata has data similar to the above model. 2. the problem: how can I select and return only this: a. a single paragraph paragraph id=02/paragraph b. a collection of paragraphs that have in common a specific criteria (let's say style=para02) paragraph id=54 style=para02/paragraph paragraph id=98 style=para02/paragraph paragraph id=65 style=para02/paragraph For now, the following query SELECT xpath_nodeset(rawdata, '/mydocument/body/chapter/contents/paragraph') FROM public.xmldocuments WHERE id=4 will return all paragraphs inside document body. If I add the following clause AND xpath_bool(rawdata,'/mydocument/body/chapter/contents/paragraph[objid=2_1]'); the result set will be empty !!! I even tried: SELECT t.idxml, t.rawxml, t.xmlid FROM xpath_table('id', 'rawdata','xmldocuments', '/mydocument/body/paragraph|/mydocument/body/chapter/content/paragraph|/mydocument/body/chapter/content/paragraph/@objid', --'xpath_string(''rawdata'',''@objid'') = ''2_1'' ' --'xpath_bool(''rawdata'',''/mydocument/body/chapter/content/[EMAIL PROTECTED]2_1]'')' 'true' ) AS t(idxml integer, rawxml text, xmlid text), xmldocuments as x WHERE t.idxml = x.id AND x.id = 4 Please, help! Thank you for your time, Marian -- ~~~ - S o f t E x p e r t - ~~~ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] XPath question - big trouble
[snip] paragraph id=01 style=para01/paragraph [snip] xpath_bool(rawdata,'/mydocument/body/chapter/contents/paragraph[objid=2_1]'); ^ To me it looks like attribute name mismatch, not to mention you forgot the '@' (i.e. I guess you wanted the xpath: '/mydocument/body/chapter/contents/[EMAIL PROTECTED]2_1]' If you were just sloppy writing the email, maybe you should also check your actual code for such sloppiness ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] XPath question - big trouble
Thanks for replying. It was a typo ... Also id fom example should be objid ... Anyway, without the typo, the result set is empty. There is also a weird thing: SELECT xpath_nodeset(rawdata, '/mydocument/body/chapter/contents/paragraph') FROM xmldocuments will output xpath_nodeset (empty line) (empty line) (empty line) (empty line) 4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms] [Executed: 8/7/06 5:42:54 PM CEST ] [Execution: 3/ms] - (empty line) is what I wrote to say that there was nothing on output - which makes me think there is something wrong with XPath implementation. Anyway, is there a solution to my problems? Marian Csaba Nagy wrote: [snip] paragraph id=01 style=para01/paragraph [snip] xpath_bool(rawdata,'/mydocument/body/chapter/contents/paragraph[objid=2_1]'); ^ To me it looks like attribute name mismatch, not to mention you forgot the '@' (i.e. I guess you wanted the xpath: '/mydocument/body/chapter/contents/[EMAIL PROTECTED]2_1]' If you were just sloppy writing the email, maybe you should also check your actual code for such sloppiness ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] XPath question - big trouble
Marian POPESCU wrote: There is also a weird thing: SELECT xpath_nodeset(rawdata, '/mydocument/body/chapter/contents/paragraph') FROM xmldocuments will output xpath_nodeset (empty line) (empty line) (empty line) (empty line) 4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms] [Executed: 8/7/06 5:42:54 PM CEST ] [Execution: 3/ms] - (empty line) is what I wrote to say that there was nothing on output - which makes me think there is something wrong with XPath implementation. Makes me think you have null values in the resultset. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] XPath question - big trouble
Marian, On Mon, 2006-08-07 at 17:47, Marian POPESCU wrote: SELECT xpath_nodeset(rawdata, '/mydocument/body/chapter/contents/paragraph') FROM xmldocuments will output xpath_nodeset (empty line) (empty line) (empty line) (empty line) 4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms] [Executed: 8/7/06 5:42:54 PM CEST ] [Execution: 3/ms] - (empty line) is what I wrote to say that there was nothing on output - which makes me think there is something wrong with XPath implementation. In the first email you said this works for you... the only difference I find in the query from there and here is that you schema-qualified the xmldocuments table in your last mail. Are you sure there's no weirdness in your schemas, with some other table with the same name in another schema coming in your way ? Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] XPath question - big trouble
It looks that I shoot myself in the foot :( CREATE TABLE xmltable ( id int8 NOT NULL, xml_data text, CONSTRAINT pk_xmltable PRIMARY KEY (id) ) WITHOUT OIDS; The id field contains values from 1 to 3. The field xml_data contains something like this: mydocument title objid=4654My document/title body objid=6987 paragraph objid=87 style=para21/paragraph chapter objid=5764 title objid=646Chapter 1/title contents paragraph objid=01 style=para01aaa/paragraph paragraph objid=02 style=para01bb/paragraph paragraph objid=03 style=para01/paragraph paragraph objid=04 style=para01/paragraph paragraph objid=05 style=para01/paragraph /contents /chapter chapter objid=681 title objid=68746Chapter 2/title contents paragraph objid=654 style=para01gg/paragraph paragraph objid=54 style=para02hh/paragraph paragraph objid=64 style=para01ii/paragraph paragraph objid=98 style=para02ttt/paragraph paragraph objid=65 style=para02eee/paragraph paragraph objid=655 style=para01kk/paragraph /contents /chapter /body /mydocument My questions: 1.What query should I write to get only paragraph objid=02 style=para01bb/paragraph knowing that I can pass as a parameter the objid attribute value; 2.What query should I write to get paragraph objid=54 style=para02hh/paragraph paragraph objid=98 style=para02ttt/paragraph paragraph objid=65 style=para02eee/paragraph knowing that I can pass as a parameter the style attribute value. My new found answers: 1. SELECT xpath_nodeset(xml_data, '/mydocument/body/chapter/contents/[EMAIL PROTECTED] = 02]|/mydocument/body/[EMAIL PROTECTED]87]') FROM xmltable WHERE id = 3 will give me the desired paragraph 2. SELECT xpath_nodeset(xml_data, '/mydocument/body/chapter/contents/[EMAIL PROTECTED] = para02]|/mydocument/body/[EMAIL PROTECTED]para02]') FROM xmltable WHERE id = 3 will give me the collection of paragraphs that correspond to my criteria. Apparently I am more carefull once I make a fool of myself ;) Thank you for the replies and sorry for wasting your time! Csaba Nagy wrote: Marian, On Mon, 2006-08-07 at 17:47, Marian POPESCU wrote: SELECT xpath_nodeset(rawdata, '/mydocument/body/chapter/contents/paragraph') FROM xmldocuments will output xpath_nodeset (empty line) (empty line) (empty line) (empty line) 4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms] [Executed: 8/7/06 5:42:54 PM CEST ] [Execution: 3/ms] - (empty line) is what I wrote to say that there was nothing on output - which makes me think there is something wrong with XPath implementation. In the first email you said this works for you... the only difference I find in the query from there and here is that you schema-qualified the xmldocuments table in your last mail. Are you sure there's no weirdness in your schemas, with some other table with the same name in another schema coming in your way ? Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] XPath question - big trouble
You are right; I had a typo : contents is content Thanks for replying ... Guy Rouillier wrote: Marian POPESCU wrote: There is also a weird thing: SELECT xpath_nodeset(rawdata, '/mydocument/body/chapter/contents/paragraph') FROM xmldocuments will output xpath_nodeset (empty line) (empty line) (empty line) (empty line) 4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms] [Executed: 8/7/06 5:42:54 PM CEST ] [Execution: 3/ms] - (empty line) is what I wrote to say that there was nothing on output - which makes me think there is something wrong with XPath implementation. Makes me think you have null values in the resultset. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] XPath question - big trouble
Marian, 1. SELECT xpath_nodeset(xml_data, '/mydocument/body/chapter/contents/[EMAIL PROTECTED] = 02]|/mydocument/body/[EMAIL PROTECTED]87]') FROM xmltable WHERE id = 3 You could maybe use the xpath: '//[EMAIL PROTECTED] = 02]' if you want all paragraphs at all document levels. A nice xslt reference is: file:///home/cnagy/offline/XSLTreference/Output/index.html There you can also exercise your xpath skills... Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] XPath question - big trouble
Thank you! Since you brought up the subject, will this //[EMAIL PROTECTED] = 02] be slower than /mydocument/body/chapter/contents/[EMAIL PROTECTED] = 02] when handling a big document? I mean from the query time point of view ... Csaba Nagy wrote: Marian, 1. SELECT xpath_nodeset(xml_data, '/mydocument/body/chapter/contents/[EMAIL PROTECTED] = 02]|/mydocument/body/[EMAIL PROTECTED]87]') FROM xmltable WHERE id = 3 You could maybe use the xpath: '//[EMAIL PROTECTED] = 02]' if you want all paragraphs at all document levels. A nice xslt reference is: file:///home/cnagy/offline/XSLTreference/Output/index.html There you can also exercise your xpath skills... Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- ~~~ - S o f t E x p e r t - ~~~ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match