Обсуждение: 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','<doc><name 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.

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:34 AM, Otandeka Simon Peter
<sotandeka@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 <kamauallan@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','<doc><name 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
,'<doc><name 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','<doc><name first="David"
last="Marston"/>some text</doc>');

DROP SEQUENCE IF EXISTS simple_table_seq CASCADE;
DROP TABLE IF EXISTS simple_table CASCADE;


Allan.

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
,'<doc><name 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','<doc><name 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
<sotandeka@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 <kamauallan@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','<doc><name 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
>
>

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
> ,'<doc><name 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','<doc><name 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
> <sotandeka@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 <kamauallan@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','<doc><name 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
> >
> >
>