[GENERAL] xpath functionerror

[sparikh]

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

[sparikh]

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

[David Johnston]

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

[David Johnston]

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

[Irooniam]

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

[Craig Ringer]

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

[Chris Roffler]

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

[Tom Lane]

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

[Chris Roffler]

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

[Chris Roffler]

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

[MOLINA BRAVO FELIPE DE JESUS]

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

[Allan Kamau]

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

[Otandeka Simon Peter]

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

[Allan Kamau]

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

[Allan Kamau]

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

[Karl Koster]

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

[Tim Landscheidt]

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

[Tim Landscheidt]

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

[Roy Walter]

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

[Scott Bailey]

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

[Roy Walter]

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

[Sam Mason]

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

[Scott Bailey]

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

[Roy Walter]

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

[Sam Mason]

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

[Roy Walter]

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

[Peter Eisentraut]

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

[Francisco]

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

[Francisco]

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

[Francisco]

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

[Brad Balmer]

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

[Peter Schonefeld]

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

[Nikolay Samokhvalov]

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

[Dave Page]

 

 -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

[John Gray]

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

[Nikolay Samokhvalov]

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

[Frank Cohen]

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

[Frank Cohen]

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

[Jeff Davis]

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

[Csaba Nagy]

 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

[Marian POPESCU]

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

[Csaba Nagy]

[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

[Marian POPESCU]

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

[Guy Rouillier]

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

[Csaba Nagy]

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

[Marian POPESCU]

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

[Marian POPESCU]

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

[Csaba Nagy]

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

[Marian POPESCU]

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