Some quick and easy examples into oracle XML DB.

Oracle XML DB is the API of SQL functions inside the oracle DBMS which makes oracle XML enabled.

If you don’t know much about XML, please refer to this quick tutorial :  especially the starting slide 74 discussing Oracle and XML.

XML DB is a series of SQL functions ,PL/SQL packages and DBMS features for processing XML data In and out of Oracle.

The most important feature; is adding the new column type “XMLType”; which enable storing structured and unstructured XML documents into oracle. A considerable step ahead from CLOB.

There are two sets of features :

1. Read Oracle Relational data and present them in XML. Using Oracle functions like : XMLElement() , XMLAttributes(), XMLForest(),XMLAgg() ..etc

2. Parse an XML field (XMLType or CLOB) and present it in Scalar Values (varchar2,char,number …etc). Using Oracle Functions like: TABLE () , XMLType() , XMLSEQUENCE() , Extract() , ExtractValue() ..etc

XMLType() is also an SQL function that converts a Varchar to XMLType:

Try this in TOAD:

select xmlType('<days><day>Monday</day><day>Thursday</day></days>') as x
from dual
;

The same effect , if you try this also:

WITH XMLDATA as (
select xmlType('<days><day>Monday</day><day>Thursday</day></days>') as x
from dual
)
select x
from XMLDATA
;

If you are using TOAD, the result would look as “(ORAXML)” which indicates that the XMLtype was recognized by TOAD.

Without going into much explanations (from the slides mentioned tutorial above):

WITH XMLDATA as (
select xmlType('
<days><day>Monday</day><day>Tuesday</day><day>Wednesday</day><day>Thursday</day><day>Friday</day><day>Saturday</day><day>Sunday</day></days>
') as DAYS
from dual
)
SELECT  VALUE(p) ,
VALUE(p).getStringVal() XML_DAY,
extractValue(VALUE(p),'//text()') WEEKDAY
FROM XMLDATA,
TABLE(XMLSEQUENCE(EXTRACT(DAYS, '/days/*'))) p
;

This displays the full days of the week.

VALUE(P)	XML_DAY	WEEKDAY
<day>Monday</day>	<day>Monday</day>	Monday
<day>Tuesday</day>	<day>Tuesday</day>	Tuesday
<day>Wednesday</day>	<day>Wednesday</day>	Wednesday
<day>Thursday</day>	<day>Thursday</day>	Thursday
<day>Friday</day>	<day>Friday</day>	Friday
<day>Saturday</day>	<day>Saturday</day>	Saturday
<day>Sunday</day>	<day>Sunday</day>	Sunday

Imagine that you want to generate a report of events per weekday from the events table. Some weeks will have events on Sunday and others don’t. If you just group by week day,then you will not get a standard report displaying Sunday events for some weeks and omitting Sunday record for others.

You could create a short table of the days of the week and OUTER JOIN with it to display Sunday even if there are no records/events on Sunday. Or you can do this:

WITH XMLDATA as (
select xmlType('
<days>
<day>
<name>Monday</name>
<order>1</order>
</day>
<day>
<name>Tuesday</name>
<order>2</order>
</day>
<day>
<name>Wednesday</name>
<order>3</order>
</day>
<day>
<name>Thursday</name>
<order>4</order>
</day>
<day>
<name>Friday</name>
<order>5</order>
</day>
<day>
<name>Saturday</name>
<order>6</order>
<weekend>TRUE</weekend>
</day>
<day>
<name>Sunday</name>
<order>7</order>
<weekend>TRUE</weekend>
</day>
</days>
') as DAYS
from dual
)
SELECT  extractValue(VALUE(p),'//name/text()') "name",
extractValue(VALUE(p),'//order/text()') "order",
nvl(extractValue(VALUE(p),'//weekend/text()'),'FALSE') "weekend"
FROM XMLDATA,
TABLE(XMLSEQUENCE(EXTRACT(DAYS, '/days/day'))) p
--where  extractValue(VALUE(p),'//weekend/text()')='TRUE'
order by extractValue(VALUE(p),'//order/text()')
;

The result:

name	order	weekend
Monday	1	FALSE
Tuesday	2	FALSE
Wednesday	3	FALSE
Thursday	4	FALSE
Friday	5	FALSE
Saturday	6	TRUE
Sunday	7	TRUE

The XML DB feature is used here in an unusual way to save the SQL developer the hassle of creating a table and populating it in Dev. ,Test and Production environments. and hence saving a lot of paperwork 🙂

Another scenario where I can think of; when this technique is useful, is when you want to have a small mapping table and save yourself a lot of SQL-DECODE function usage.

If you got this far I’m sure you can figure out how to OUTER JOIN the above SQL with the EVENTS table.

Rating and comments are highly appreciated.