Extract a XML tag with Oracle SQL

By | 2014/05/08

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 [1] into an XML – this can be done via XMLTYPE [2].

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 [2] 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 [4].

[1] Oracle, Data Types

[2] Oracle, XMLType

[3] Oracle, ExtractValue

[4] psoug.org

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.