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="4654">My document</title>
<body objid="6987">
<paragraph objid="87" style="para21"></paragraph>
<chapter objid="5764">
<title objid="646">Chapter 1</title>
<contents>
<paragraph objid="01" style="para01">aaaaaaaaaaa</paragraph>
<paragraph objid="02" style="para01">bbbbbbbbbb</paragraph>
<paragraph objid="03" style="para01">cccccccccccc</paragraph>
<paragraph objid="04" style="para01">dddddddddddd</paragraph>
<paragraph objid="05" style="para01">eeeeeeeeeeee</paragraph>
</contents>
</chapter>
<chapter objid="681">
<title objid="68746">Chapter 2</title>
<contents>
<paragraph objid="654" style="para01">gggggggggg</paragraph>
<paragraph objid="54" style="para02">hhhhhhhhhh</paragraph>
<paragraph objid="64" style="para01">iiiiiiiiii</paragraph>
<paragraph objid="98" style="para02">ttttttttttt</paragraph>
<paragraph objid="65" style="para02">eeeeeeeeeee</paragraph>
<paragraph objid="655" style="para01">kkkkkkkkkk</paragraph>
</contents>
</chapter>
</body>
</mydocument>
My questions:
1.What query should I write to get only
<paragraph objid="02" style="para01">bbbbbbbbbb</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="para02">hhhhhhhhhh</paragraph>
<paragraph objid="98" style="para02">ttttttttttt</paragraph>
<paragraph objid="65" style="para02">eeeeeeeeeee</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/paragraph[@objid
= "02"]|/mydocument/body/paragraph[@objid="87"]')
FROM xmltable
WHERE id = 3
will give me the desired paragraph
2.
SELECT
xpath_nodeset(xml_data, '/mydocument/body/chapter/contents/paragraph[@style
= "para02"]|/mydocument/body/paragraph[@style="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.
>
