XPath question - big trouble

From: Marian POPESCU <softexpert(at)libertysurf(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: XPath question - big trouble
Date: 2006-08-07 15:19:59
Message-ID: eb7lml$egc$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/paragraph[(at)objid="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 -
~~~~~~~~~~~~~~~~~~~~~~~

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Csaba Nagy 2006-08-07 15:33:19 Re: XPath question - big trouble
Previous Message Harald Armin Massa 2006-08-07 14:47:00 Re: could not open relation - why?