My table looks like this:
CREATE TABLE "Content" ( "id" VARCHAR(36) NOT NULL, "xml" XML );
I have an index defined
create index "idx_creation_date" on "Content"("xml") generate keys using xmlpattern '/*/dateCreated' as sql timestamp;
This index works fine when using it in comparisons (XMLEXISTS), but now I want to use that index to sort my table according with the date my content was created and select the first 20 items.
Sadly, this didn't work
SELECT "Content".* FROM "Content" ORDER BY XMLCAST(XMLQUERY('$xml/*/dateCreated' PASSING "xml" as "xml") as TIMESTAMP) DESC FETCH FIRST 20 ROWS ONLY
because XMLQUERY doesn't use indexes. The same goes for
xquery let $items := for $i in db2-fn:xmlcolumn("Content.xml") order by $i/*/dateCreated return $i for $i at $count in subsequence($items, 1, 20) return $i
because let and return can't use indexes.
Anyone has an idea how I can use the existing (and working) index on dateCreated in a ORDER BY query?