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('
3 001000222
4 HGR REUSE BX FOR HANGERRTNS
5 1
6 1
7 1
8
9 123456
10 490010002222
11
12
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
