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

Here are some code snippets 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 has type XMLType. The latter is going to contain our XML data.

Now let’s insert a row into the table.

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

Here, 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 encode the date of birth that way in order to show you (in the next example) how to retrieve an attribute as well as an ordinary XML node.

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 which has type XMLType. As you can see, you extract XML data using regular XPath expressions.

Note: If your XML uses 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 in an Oracle database from version 9.0.2 onwards.