Using XML data in ADF View Object

I recently was tasked with creating a generic preferences table that can be used to story any type of web site preference.   This needed to be generic enough so that new types of preferences can be added without requiring a data model change.  Using XML in the value column was the perfect solution for this.

This is how the table is defined


  CREATE TABLE "USER_PREFERENCE"
   (
     "USER_PREFERENCE_ID" NUMBER(38,0) NOT NULL ENABLE,
    "USER_ID" NUMBER(38,0) NOT NULL ENABLE,
    "PREFERENCE" VARCHAR2(40 BYTE) NOT NULL ENABLE,
    "VALUE" VARCHAR2(4000 BYTE) NOT NULL ENABLE
}

The first type of preference I needed to add was a URL link. I defined this in XML


<url >
  <label>Yahoo </label>
  <location>http://yahoo.com/location>
  <open>defaultBrowser</open>
  <sortorder>1</sortorder>
</url>

I insert this xml into the value column and "CUSTOMLINK" into the preference column along  with the USER_ID and primary key USER_PREFERENCE_ID sequence.


Here is the query I used to retrieve my custom url links.  Notice that I'm not actually returning any column from the table.   All of the columns returned are coming from the xml nodes in the value column.  This query uses a feature of Oracle available since 10.2 (XMLTABLE).  You can think of it as a table within a column that the row is being joined to.  "url "is the alias for this table containing all of the xml elements as table columns.



    SELECT
           url.label,
           url.location,
           url.open,
           url.sortorder
    FROM USER_PREFERENCE ,
    --  XMLTable defines a join with contents of value column treating it like a table
              XMLTable('url'
                       PASSING XMLTYPE(USER_PREFERENCE.value) 
                       COLUMNS
                       label  VARCHAR2(40) PATH 'label',
                       location  VARCHAR2(1025) PATH 'location',
                       open VARCHAR2(40) PATH 'open',
                       sortorder VARCHAR2(4) PATH 'sortorder',
                       permission VARCHAR2(40) PATH 'permission'
                       ) as url
   
    WHERE PREFERENCE = 'CUSTOMLINK'
    AND  USER_ID = :UserIdBind
    order by sortorder




I used a read only view object for this.   I could have created a entity based view object and use expert mode to insert similar sql.   For storing and committing data in the value column I would need to add custom code to the setters for the 4 xml based columns and there construct the xml containing the values from all of these columns and then set that xml into the value column.

This gives me great flexibility for adding additional columns to my query without making any changes to the data model.  Querying XML will probably not perform well if you have a huge result sets but  is a great solution for queries that return a small number of rows as this one does.