SQL & PL/SQL

Hi,

I am running the following query to get all non-shared reasons from gv$sql_shared_cursor

select
     xt.r1 raison,
     count(1) cnt
from
     gv$sql_shared_cursor sh
     inner join xmltable (
        '/ChildNode'
        passing xmlparse(content sh.reason)
        columns
            c1 number       path 'ChildNumber',
            r1 varchar2(40) path 'reason',
            d1 varchar2(40) path 'details'
    )
    xt on ( 1 = 1 )
where
  length(xt.r1)>1
group by
    xt.r1
order by cnt desc;


RAISON                                          CNT
---------------------------------------- ----------
Optimizer mismatch(12)                         8775
Optimizer mismatch(10)                         1592
Rolling Invalidate Window Exceeded(2)         1361
NLS Settings(2)                                542
Bind UACs mismatch(0)                           435
Bind mismatch(3)                               400
Bind mismatch(1)                               397
Bind mismatch(22)                              202
Marked for Purge(1)                             136
Session Specific Cursor Session Mismatch        25
Bind mismatch(25)                               13
Not Typechecked(0)                                4
Bind mismatch(8)                                 3
Rolling Invalidate Window Exceeded(1)            2
Authorization Check failed(4)                     1
Auto Reoptimization Mismatch(1)                  1

However, depending on the content of the reason(CLOB) column in v$sql_shared_cursor I sometimes face the following error:

ORA-31011 : XML parsing failed
LPX : 00210 : expected ‘=’ instead of ‘>’

I know that this error is due to a non correct xml content. But can I workaround this error? Or get the same result excluding rows with non correct reason content?

Thanks

Mohamed Houri