Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to index non-singleton XQuery expression

256287May 20 2011 — edited May 27 2011
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 24 2011
Added on May 20 2011
2 comments
343 views