I have a table with an XMLTYPE which stores documents using non-singleton items. I.e. Note key/value type structure of document. (See example below) Since EXTRACTVALUE has been deprecated I've turned to using XMLQUERY to retrieve the values and would now like to index the lastname values of the document. However I'm getting a ORA-02070 error on the index creation statement. Any recommendations on how to index this value would be appreciated.
Thanks.
SQL> DROP TABLE xml_table_test
Table dropped.
SQL> CREATE TABLE xml_table_test
(
id NUMBER (10),
xml_request XMLTYPE
)
Table created.
SQL> ALTER TABLE xml_table_test ADD(
CONSTRAINT xml_table_test_pk PRIMARY KEY (id)
)
Table altered.
SQL> INSERT INTO xml_table_test(id,xml_request) values(
1,
'<?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
<invocation>
<request>
<argMap>
<entry>
<key>firstname</key>
<value>Larry</value>
</entry>
<entry>
<key>lastname</key>
<value>Ellison</value>
</entry>
<entry>
<key>position</key>
<value>HeadHoncho</value>
</entry>
</argMap>
</request>
</invocation>'
)
1 row created.
SQL> COMMIT
Commit complete.
SQL> SELECT id,
XMLQUERY ('for $i in /invocation/request/argMap/entry where $i/key eq "lastname" return $i/value/text()'
PASSING xml_request RETURNING CONTENT)
lastname
FROM xml_table_test
ID LASTNAME
---------- -----------
1 Ellison
1 row selected.
SQL> CREATE INDEX xml_idx ON xml_table_test
(
XMLCAST(
XMLQUERY ('for $i in /invocation/request/argMap/entry where $i/key eq "lastname" return $i/value/text()' PASSING xml_request RETURNING CONTENT) AS VARCHAR2(28)
)
)
CREATE INDEX xml_idx ON xml_table_test
(
XMLCAST(
XMLQUERY ('for $i in /invocation/request/argMap/entry where $i/key eq "lastname" return $i/value/text()' PASSING xml_request RETURNING CONTENT) AS VARCHAR2(28)
)
)
Error at line 46
ORA-02070: database does not support correlation in this context
Edited by: rnanton on May 20, 2011 3:53 PM
Edited by: rnanton on May 20, 2011 3:53 PM
Edited by: rnanton on May 20, 2011 3:54 PM
Edited by: rnanton on May 20, 2011 3:57 PM
Edited by: rnanton on May 20, 2011 3:57 PM