xmltype.extractValue

WITH x AS
 (SELECT '<?xml version="1.0" encoding="GB2312"?>
<root>                                              
  <workOrderId>427269723</workOrderId>      
  <isSuccess>true</isSuccess>            
  <resultCode>0</resultCode>         
  <workOrderDesc><![CDATA[操作成功。]]></workOrderDesc> 
  <timeStamp>2014-02-01 02:24:39</timeStamp>            
</root>' A
    FROM dual)
SELECT extractValue(xmltype(x.a), '/root/workOrderDesc') as v FROM x

V
---------
操作成功。

如果不想用正则，可以采用下面的方法

WITH x AS
 (SELECT '<?xml version="1.0" encoding="GB2312"?>
<root>                                              
  <workOrderId>427269723</workOrderId>      
  <isSuccess>true</isSuccess>            
  <resultCode>0</resultCode>         
  <workOrderDesc><![CDATA[操作成功。]]></workOrderDesc> 
  <timeStamp>2014-02-01 02:24:39</timeStamp>            
</root>' A
    FROM dual)
SELECT ltrim(regexp_substr(A, 'CDATA\[[^]]+', 1), 'CDATA\[') FROM x

问题在于有多个CDATA时，还需要进一步定位