Here are some snippets of code showing how to accomplish this:
CREATE TABLE SampleTable (id number primary key, person XMLType)
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"')
XPath_Expression is the XPath expression used to extract the value you’re interested in and
the URI indicating your namespace.
One last note: You can use XML Data inside an Oracle DB from version 9.0.2 on.