SQL & PL/SQL

Hi Experts,

Need you help in how to extract node values from xml. I have tried using the extract value function and xml table but both are taking a lot of time.

Below query takes around 27 mins to create table with parallel ON. Please suggest what can be done or any other alternative of doing this.

Log table:  Column Name : Data : XMLTYPE

Temp Table : EMP.

  create table temp as

with mydata as (select data xx from log_table, emp where emp.id=log.id)

select * from mydata,xmltable(

'*/*[local-name()=''partya''][@id=''123'']'

passing mydata.xx

columns party_a_name varchar2(20) PATH '/',

issuer varchar2(1000) PATH '//@idscheme')

  Name                                   Operation                                                                              Rows

SELECT STATEMENT                                                                                                         661182611

  NESTED LOOPS                                                                                                               661182611

    NESTED LOOPS                                                                                                             80948

      TABLE ACCESS FULL EMP                                                                                            77816

      TABLE ACCESS BY INDEX ROWID BATCHED         Log_table                                       1

       INDEX RANGE SCAN                                              RRLD_UK1                                    1

    COLLECTION ITERATOR PICKLER FETCH          XQSEQUENCEFROMXMLTYPE             8168