JPA support for XML datatype columns

Question

For a new project we are starting (which uses JPA 2 on top of Hibernate 3) we need to be able to store XML documents and then query on their content and structure at a later date. The databases we would like to support are DB2, Oracle and SQLServer.

I know all three DB vendors support native XML querying (using SQL and XQuery like statements) but is there any direct support within Hibernate for this? In other words, can I write one set of hibernate data access code which queries all 3 database types agnostically using a built-in feature of Hibernate/JPA or would I need to code specific queries for each DB type?

I appreciate that I could define the column as a @Lob column and then read all the records, parse and inspect but I was hoping to leverage the power of the DB engine rather than having to do it myself.

For reference we would only ever be using one DB flavour at a time, it's just that we want to be able to support all 3 should we need to.

Thanks Steve

Answer 1

JPA doesn't offer specific support for these proprietary types beyond Lob. For more advanced support, I guess you'll have to implement some Hibernate custom user types. The following documents might give you some ideas.

• XML DataType Mapping with Oracle
• Hibernate with Oracle XmlType

Of course, they will be database specific and will make the code less portable.

Regarding querying, AFAIK any function that is called in the WHERE clause of an HQL statement and that isn't known to Hibernate is passed as is to the database. So you could leverage specific functions. But again, this will make the queries database specific. Maybe consider using orm.xml mappings for specific deployments.