Monthly Archives: March 2013

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:

insertXML

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:

insertXML2

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:

insertXML3

Product Types

I recently encountered a nice model on product types that I would like to share.
A product type can be understood as an concept of a product, much like the descriptions you might see in a catalogue. Examples like a Peugeot 206 Gentry which is a concept of a car. It is not equivalent to actual cars – it only describes a type of a car of which a lot of actual cars may exist.

Then the model:

productType

 

In the left upper corner, we see the entity “Product Type”. This entity may contain a description such as the Peugeot 206 Gentry. Other examples may be the hard disk WD3000EXDB which stands for a type hard disk.

As we may have hundreds, may be thousands of product type, we may classify these product type into several classes. We may have the class “cars” and a class “hard disks”.

Each of these classes may have (overlapping) sets of attributes that describe the product types. The product type class “hard disk” may have attributes like price, storage amount, delivery term etc. Likewise a product type class may have attributes like price, number of seats. The attributes are listed in an entity “Product Type Attributes”. As we may have overlapping sets, an associative entity is added.

The “Product Type Attributes” and “Product Type” both identify the values. We then know the Peugeot 206 Gentry costs 20000 euro, whereas the WD3000EXDB costs 200 euros.