HOWTO: Query via Xpath position function

Based upon: http://forums.oracle.com/forums/thread.jspa?threadID=498374

 
SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS FOR 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
 
5 ROWS selected.

 
SQL> CREATE TABLE temp
  2  (lclob xmltype)
  3  ;
 
TABLE created.
 
SQL> INSERT INTO temp
  2  VALUES('<itemmaster>
  3  <itemnumber>001000222</itemnumber>
  4  <itemdesc>HGR REUSE BX FOR HANGERRTNS </itemdesc>
  5  <paltsize>1</paltsize>
  6  <stduom>1</stduom>
  7  <shflfuom>1</shflfuom>
  8  <aliases>
  9  <upccode>123456</upccode>
 10  <upccode>490010002222</upccode>
 11  </aliases>
 12  </itemmaster> 
 13  ')
 14  ;
 
1 ROW created.
 
SQL> commit;
 
Commit complete.
 
SQL> SELECT extractvalue(lclob,'/itemMaster/aliases/upcCode[position()=2]') FROM temp;
 
EXTRACTVALUE(LCLOB,'/ITEMMASTER/ALIASES/UPCCODE[POSITION()=2]')
--------------------------------------------------------------------------------------------------
490010002222
 
1 ROW selected.
 
SQL> SET autotrace ON
SQL> SELECT extractvalue(lclob,'/itemMaster/aliases/upcCode[position()=2]') FROM temp;
 
EXTRACTVALUE(LCLOB,'/ITEMMASTER/ALIASES/UPCCODE[POSITION()=2]')
--------------------------------------------------------------------------------------------------
490010002222
 
1 ROW selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1896031711
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  2002 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEMP |     1 |  2002 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used FOR this statement
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo SIZE
        476  bytes sent via SQL*Net TO client
        385  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> CREATE INDEX pos_idx ON temp
  2  (extractvalue(lclob,'/itemMaster/aliases/upcCode[position()]'));
 
INDEX created.
 
SQL> SELECT extractvalue(lclob,'/itemMaster/aliases/upcCode[position()=2]') FROM temp;
 
EXTRACTVALUE(LCLOB,'/ITEMMASTER/ALIASES/UPCCODE[POSITION()=2]')
--------------------------------------------------------------------------------------------------
490010002222
 
1 ROW selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1896031711
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  2002 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEMP |     1 |  2002 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used FOR this statement
 
 
Statistics
----------------------------------------------------------
         19  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo SIZE
        476  bytes sent via SQL*Net TO client
        385  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> BEGIN  dbms_stats.set_table_stats
  2  ( ownname => 'MARCO'
  3  , tabname => 'TEMP'
  4  , numrows => 50000000  , numblks => 1200000  );
  5  END;
  6  / 
 
PL/SQL PROCEDURE successfully completed.
 
 
SQL> SELECT extractvalue(lclob,'/itemMaster/aliases/upcCode[position()=1]') FROM temp;
 
EXTRACTVALUE(LCLOB,'/ITEMMASTER/ALIASES/UPCCODE[POSITION()=1]')
------------------------------------------------------------------------------------------------------
123456
 
1 ROW selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1896031711
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    50M|  4768M|   265K  (2)| 00:53:04 |
|   1 |  TABLE ACCESS FULL| TEMP |    50M|  4768M|   265K  (2)| 00:53:04 |
--------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo SIZE
        470  bytes sent via SQL*Net TO client
        385  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

Apparently position() is currently not supported. See XMLDB Forum here: http://forums.oracle.com/forums/thread.jspa?threadID=498374&tstart=0

Marco Gralike Written by: