Working with XML data in an Oracle Database

Introduction
Is your application using XML to store data? With Oracle 9i and above you can store your XML in the database and query it to pull out the pieces of data that you need. You need to make sure that you've installed the XDK and XML capabilities of your Oracle Database before being able to use this XML functionality.

Creating a table with an XMLType datatype
The XMLType datatype (which is really the SYS.XMLType datatype) is basically a CLOB data type but with a some extra functions that allow the data to be made available to SQL. First create a new table like so:

$ sqlplus scott/tiger

SQL> CREATE TABLE mytable
2  (
3   myid  number(10),
4   myxml XMLType
5  );

Table created.

Then insert some XML data into the table.

SQL> insert into mytable values
2  (
3     1,
4     XMLType('<users>
5               <person>
6                <name>Bruce Scott</name>
7                <email>Bruce@SomeWebsite.com</email>
8               </person>
9              </users>'
10           )
11 );
1 row created.

And now we can use SQL to SELECT the data out:

SQL> select extractValue(myxml, '/USERS/PERSON/EMAIL') as XmlEMail
2  from   mytable
3  where  existsNode(myxml, '/USERS/PERSON[NAME="Bruce Scott"]') = 1;

XMLEMAIL
-----------------------------------------------------------------------

Bruce@SomeWebsite.com

It's as easy as that! That's all you need to do to store and retrieve XML from an Oracle Database.

If you have trouble, please post your comments on this web page.
share on: facebook