Database Software

Hi there,

I was tasked with populating an Oracle 11Gr2 database with data from 800,000 XML files. I used the Oracle documentation, and in about 2 days I had the data loaded into a simple table.

Queries of this table were SLOW, so this morning I read about using XMLIndex to index the table... I did this, and it works great -- the queries got a LOT faster.

However -- a "column" from my XML data is supposed to return values of 2000-4000 characters. It worked fine, until I created my index -- after I created the XMLIndex, the data is being truncated to 80 chars in my query.

CREATE TABLE TEST_XMLTABLE OF XMLType; /* XML data has a "node" called TEXT which can be up to 4000 chars */

SELECT to_number( extractValue(OBJECT_VALUE, 'newsitem/@itemid')) as ITEM_ID, Text.text <----- value here will contain full text body up to 4k chars
FROM TEST_XMLTABLE KTX,
XMLTABLE('/newsitem' PASSING ktx.Object_Value columns "TEXT" clob PATH 'text') TEXT


/* now create the index */

CREATE INDEX IDX_xmlindex ON TEST_XMLTABLE (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex;

Now, run my query again, and TEXT.TEXT is truncated to only 80 chars!?!?!

I'm not sure what to do next... my users need the full text, so for now I have dropped the XMLIndex... the queries are slow, but they are getting all the data.

Thanks in advance for any help or ideas.

Keith