XML records in Oracle tables

It is possible to store XML records in an Oracle table. This is one (of the many) way to retrieve XML information from an Oracle environment. It is actually quite simple. An Oracle table may contain columns that allows us to store XML records. An example is:

  CREATE TABLE "OE"."TPERSON_DATA" 
   (	"PERSON_ID" NUMBER(3,0), 
	"PERSON_DATA" "XMLTYPE"
   )

We see that the attribute “Person_Data” in table tPERSON_DATA may contain XML records.
This table can be loaded with standard insert statements like:

We have an XML record having “book” in the root. One branch is “title”, another is “price”. Likewise, we also see an author branch and a year branch. This can be queried with SQL statements that have an inline XMLQUERY expression.

The full statement is:

SELECT person_id, XMLQuery ('for $i in /book
                             where $i/price > 30
                             return $i/title'
                  passing by value person_data
                  RETURNING CONTENT) XMLData
FROM oe.tperson_data;

The SQL statement is Select person_id etc, where the XMLQUERY expression is:

for $i in /book
where $i/price > 30
return $i/title'
passing by value person_data
RETURNING CONTENT

This XMLQuery is composed of 3 sections. One section is the apostrophe constrained line that retrieves a part of the XML records. This is an XQuery expression. The second part reads like “passing by value person_data” where person_data is the name of the attribute in which the XML record is stored. The final third part is RETURNING CONTENT which are key words.
The apostrophe constrained line can be understood as “for $i in /book” where $i contains the content of the XML record and /book is the root on the XML record. The second line “where $i/price “refers to one branch that acts as a selection. The third line “return $i/title” refers to another branch that is returned to the query.

This explains the outcome where we will see:

The outcome is “Learning XML” that is retrieved from the XML record with book as root and that complies to the restriction that the price is larger than 30.

Another expression is:

 SELECT EXTRACT(person_data, '/book/title') as "Titel" 
 FROM oe.tperson_data w;

that also provides us with the title: