Hibernate mapping using Oracle XML

[Sverre Moe]

Before I begin with my problem let me explain my hibernate setup. Hope you will bare with me and not leave in the middle of the explanation .

I have a primary table called Automobile, with a secondary table called AutomobileDetails. The AutomobileDetails contain some attributes to connect to Automobile, and a string value which is an XML.

Some(not all) Automobiles have a connection to ONE AutomobileDetails.
I have defined a discriminator-value in the mapping to Automobile for those who do have such a connection. In this discriminator I have defined a <many-to-one> to the AutomobileDetails. This many-to-one is tied to the mapping of AutomobileDetails.

<subclass name="Volvo" discriminator-value="VOVLO">
  <many-to-one name="automobileDetails" class="AutomobileDetails" unique="true" insert="false" update="false" not-found="ignore">
    <column name="ID1"/>
    <column name="ID2"/>
  </many-to-one>
</subclass>

In the setter method for this automobileDetails I have an XML parser which goes through the XML string og populates the values in differenct variables in the mapped object.

I have discovered that Oracle can do this for me, but I am not sure how to set this up in my Hibernate mapping.

The following SQL is used to get a single value from this XML string

extractValue(xmltype(table1.detailString), 'ID4/@test') as test

I could set up a <property formula=""/>, and define the SQL select in this formula for the test variable, but I need to pass two paramter in this formula from the primary keys of the hibernate mapping Automobile. Could I do this or is it some other way I could populate these variables with the values from this XML string?

Something like this maybe (:id1 and :id2 refers to the primary keys)?

<property name="test" formula="select extractValue(xmltype(detail_string), 'ID4/@test') as test
from AutomobileDetails
where AutomobileDetails.ID1 = :id1
and AutomobileDetails.ID2 = :id2"/>

In addition to retrieving the data in the AutomobileDetails I also want to update them when the Automobile object is stored. Volvo is a subclass of Automobile.
[ May 19, 2008: Message edited by: Sverre Moe ]