Have you ever been struggling with an Oracle Database which stores XML values within a VARCHAR2 or CLOB field? Needed to do an evaluation where one specific tag was required?
I had to deal with this problem recently and it turned out not to be as hard as expected. Oracle has build-in XML functionality, which enables you to do some magic.
So for my query, I first needed to convert the CLOB or VARCHAR2 element  into an XML – this can be done via XMLTYPE .
Having now a XML object, it’s more or less easy to extract a tag with the “extractValue” function. Simply give the XML-object and the search pattern for the tag as argument. For the search pattern you start with a backslash followed by the parent element, backslash child element and so on. Personal note: never put a backslash on the end. In  further details in regards to the XPath Construct are given. The return type is always a VARCHAR2.
In my specific case I build the following SQL, which extract all deals from 2013 are extracted and their distinct SOURCE_ID and their count values are shown.
select distinct SOURCE_ID, count(SOURCE_ID) from ( select extractValue(XMLTYPE(ORIGINAL_TRADE), '/deal/source_id') "SOURCE_ID" from DEALS. ARCHIVE where TIMESTAMP like '2013%' ) group by SOURCE_ID order by SOURCE_ID;
As a general starting point for Oracle SQL topics I can recommend .
 Oracle, XMLType