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[@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 -
~~~~~~~~~~~~~~~~~~~~~~~
