Database Software

Hi,

I am using Oracle 10.2.0.4.

I have the following XML:

with sd as (select xmltype('<Fruits>
                              <Fruit>
                                <FruitType>Apple</FruitType>
                                <FruitSubtype>Granny Smith</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Pear</FruitType>
                                <FruitSubtype>Anjou</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Pear</FruitType>
                                <FruitSubtype>Comice</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Plum</FruitType>
                                <FruitSubtype>Victoria</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Apple</FruitType>
                                <FruitSubtype>Bramley</FruitSubtype>
                              </Fruit>
                            </Fruits>') fruit_xml from dual)
select *
from   sd;

and I want to select the last child nodes where the FruitType is either Apple or Pear.

So far, I've got:

with sd as (select xmltype('<Fruits>
                              <Fruit>
                                <FruitType>Apple</FruitType>
                                <FruitSubtype>Granny Smith</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Pear</FruitType>
                                <FruitSubtype>Anjou</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Pear</FruitType>
                                <FruitSubtype>Comice</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Plum</FruitType>
                                <FruitSubtype>Victoria</FruitSubtype>
                              </Fruit>
                              <Fruit>
                                <FruitType>Apple</FruitType>
                                <FruitSubtype>Bramley</FruitSubtype>
                              </Fruit>
                            </Fruits>') fruit_xml from dual)
select x.*
from   sd,
       xmltable('//Fruits/Fruit[FruitType=''Apple'' or FruitType=''Pear'']'
               passing sd.fruit_xml
               columns fruit_type VARCHAR2(25) path '//FruitType',
                       fruit_subtype VARCHAR2(25) path '//FruitSubtype') x;

FRUIT_TYPE                FRUIT_SUBTYPE
------------------------- -------------------------
Apple                     Granny Smith
Pear                      Anjou
Pear                      Comice
Apple                     Bramley

but I just want to end up with the last child node per FruitType, eg:

FRUIT_TYPE                FRUIT_SUBTYPE
------------------------- -------------------------
Pear                      Comice
Apple                     Bramley

Is it possible to do this via XMLTABLE, or do I have to number each of the resultant rows (which I know how to do) and then do a group by on the XMLTABLE resuilts? The latter seems clunky to me, so I'd like to avoid it if possible, but if that's the way to go, I can do that.

Thanks.