Database Software

Hi,

I am trying to extract elements containing attributes that start with a given string.

sample xml

<company id="1">
<department id="1">
<employees>
<name last="Smith" first="David"/>
<name last="Doe" first="John" />
</employees>
</department>
<department id="2">
<employees>
--
</employees>
</department>
--
</company>

The name element is stored as a nested table and has an index built on the last attribute.

create index name_index on name_nt ("last");

queries for searching employees with a given last name use the index and are quite fast.

SELECT *
FROM testxmltable,
WHERE existsNode(OBJECT_VALUE,'/company/department/employees/name[@last="Smith"> 0;

However if I have to search for employees whose last name starts with as 'S', existsNode() with a starts-with() function works fine but is very slow as it does not use the name_index.

SELECT *
FROM testxmltable,
WHERE existsNode(OBJECT_VALUE,'/company/department/employees/name[starts-with(@last,"S")> 0;

Any suggestions to run similar queries with an optimum performance will be much appreciated.

Thanks,
Uma