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/[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
