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.
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. In part two, we'll examine different
ways to pull out the XML data.
If you have trouble, please post your comments on this web page.
~ Troy Campano ~
|