Tech, Oracle, user experience, coffee, design standards, and shameless ranting

Querying XML in Oracle

Many people seem to have trouble reading XML data within Oracle.

Oracle has a full library of routines built in to allow you to interrogate XML data. Unfortunately, they’re not very well documented.

Just say you have an XML file you want to be able to SELECT from. it looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create table mytable 
(id number
,col1 clob);
 
insert into mytable values(1,'<Datasets>
  <Dataset Index_Name="NTAXA" Season_ID="1">
    <Value>1.62</Value>
  </Dataset>
  <Dataset Index_Name="NTAXA" Season_ID="7">
    <Value>1.7982</Value>
  </Dataset>
  <Dataset Index_Name="BMWP" Season_ID="1">
    <Value>1.25</Value>
  </Dataset>
</Datasets>
');
insert into mytable values(2,'<Datasets>
  <Dataset Index_Name="NTAXA" Season_ID="8">
    <Value>3</Value>
  </Dataset>
  <Dataset Index_Name="NTAXA" Season_ID="2">
    <Value>2</Value>
  </Dataset>
</Datasets>
');
commit;

Get your data out using this query

1
2
3
4
5
6
SELECT id,
       EXTRACTVALUE (VALUE (ds), '/Dataset/@Index_Name') Index_Name,
       EXTRACTVALUE (VALUE (ds), '/Dataset/@Season_ID') Season_ID,
       EXTRACTVALUE (VALUE (ds), '/Dataset/Value') VALUE
  FROM mytable rs,
       TABLE (XMLSEQUENCE (EXTRACT (xmltype(col1), '/Datasets/Dataset'))) ds

We’re using the @ symbol in the EXTRACTVALUE statement for Index_Name and Season_ID to indicate that we are looking for an ATTRIBUTE and not an element.

1
2
3
4
5
6
ID,INDEX_NAME,SEASON_ID,VALUE
1,NTAXA,1,1.62
1,NTAXA,7,1.7982
1,BMWP,1,1.25
2,NTAXA,8,3
2,NTAXA,2,2

Pretty simple huh? So why is it so hard to work with XML in Oracle?

Well, i’ve been misleading you a bit. For any real-world software the requirements are likely to be much more complicated than this simple example demonstrates.

You might have structured storage (xsd based xml instances), optional elements, external references to other schemas within the xsd, deeply nested xml structures etc..

Oracle can handle them all nicely, but it gets a bit more tricky – until that gotcha! moment.

I’ll be writing many more articles on the peculiarities of Oracle and XML, so hopefully all will become clear. Click the code tag or the xml tag below this post for more related articles.

Share

You can follow any responses to this entry through the RSS 2.0 feed.

Switch to our mobile site