How to store and extract XML data in and from an Oracle DataBase (DB)

Posted: 2006-06-08 | Categories: Oracle, Programming | Tags: XML

Here are some snippets of code showing how to accomplish this:

    
CREATE TABLE SampleTable (id number primary key, person XMLType)

This first example creates a table with only two columns: id and person. The first is the PK of the table and the second is of XMLType type. The latter is going to contain our XML data.

Now let’s insert one row in the table.

 
INSERT INTO SampleTable VALUES (1, XMLType('XMLString'))

Where you must replace XMLString with any string representing XML. For example, you can replace it with:

   
<persons>
  <person>
    <surname>Lacava</surname>
    <name>Alessandro</name>
    <date_OF_BIRTH DAY="31" MONTH="01" YEAR="1976" />
  </person>
</persons>

I decided to decode the date of birth that way in order to show you (in the next example) how to retrieve, apart from a common XML node, an attribute as well.

  
SELECT extractValue(person, '/PERSONS/PERSON/SURNAME') AS surname,
  extractValue(person, '/PERSONS/PERSON/DATE_OF_BIRTH/@YEAR') AS year_of_birth
FROM SampleTable

This last example extracts the surname and the year of birth from the XML data represented by the person column that is of XMLType type. As you can see to extract the XML data you use regular XPath expressions.

Note: If your XML is using a namespace you will have to indicate it using the following syntax:

 
extractValue(column_name, XPath_Expression, 'xmlns="namespace_URI"')

where XPath_Expression is the XPath expression used to extract the value you’re interested in and namespace_URI is the URI indicating your namespace.

One last note: You can use XML Data inside an Oracle DB from version 9.0.2 on.