Friday, July 19, 2013

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.

2 comments:

  1. hi, If i am creating a table as mentioned above in Plsql developer and querying the data as per the query given. It is working. But if i am trying to create a view object with the above query then it is giving error as --> SQL Query Error Message: ORA-00942: table or view does not exist

    It is treating XMLTable as a table and therefore it is throwing this error. What should i do then ?

    ReplyDelete
  2. I tested on jdev 11.1.1.4 and 11g database and it works for me. What version of jdeveloper are you using? If the error happens at design time you may be able to ignore it if it lets you close query dialog. If it works in SQL devloper you should be able to drop the query into the view object assuming you are going to the same database

    ReplyDelete