Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Querying XML CLOB Data Directly in Oracle

DZone's Guide to

Querying XML CLOB Data Directly in Oracle

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

Oracle 9i introduced a new datatype - XMLType - specifically designed for handling XML naively in the database.

However, we may find we have a database that stores XML directly in a CLOB/NCLOB datatype (either because of legacy data, or because we are supporting multiple database platforms).

It can be useful to query this data directly using the XMLType functions available. This post just shows a very simple example of a query to do this.

(Note: there may be better ways to do this - but this worked for me when I needed an ad-hoc query quickly!)

Our Table

Assume we have a  table called USER, which has the following columns

Our embedded XML (example for Ringo)

Our Query

Say we want to look at account details in the XML - e.g. find which users all have the same sort code. A query like the following:

SELECT ID, NAME 
XMLTYPE(u.accountxml).EXTRACT('/person/account/sortCode/text()') as SORTCODE 
FROM USER  u;

Will produce output like this. Obviously from here you can use all the standard SQL operators to filter, join etc further to get what you need:

Do you pay to use your database? What if your database paid you? Learn more with RavenDB.

Topics:
java ,sql ,oracle ,tips and tricks ,tools & methods ,clob ,xmltype

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}