Hibernate version:3.0.5
We need to extract elements out of XML element stored as XML Type in Oracle 9i database
the XML is
Code:
<?xml version="1.0" encoding="utf-8"?>
<PurchaseOrder xmlns:edi='http://ecommerce.org/schema'>
<edi:price units='Euro'>32.18</edi:price>
<Reference>BLAKE-2001062514034328PDT</Reference>
<Actions>
<Action>
<User>KING</User>
<Date/>
</Action>
</Actions>
<Reject/>
<Requester>David E. Blake</Requester>
<User>BLAKE</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>David E. Blake</name>
<address>400 Oracle Parkway Redwood Shores, CA, 94065 USA</address>
<telephone>650 999 9999</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>The Birth of a Nation</Description>
<Part Id="EE888" UnitPrice="65.39" Quantity="31"/>
</LineItem>
</LineItems>
</PurchaseOrder>
Since we intend to use "extract" provided by Oracle we used native SQL The code used is
Code:
String query = "select P.PODOCUMENT.extract('//PurchaseOrder/edi:price/text()', 'xmlns:edi=\"http://ecommerce.org/schema\"').getStringVal() as PODOC from PURCHASEORDER P where P.PODOCUMENT.extract('//PurchaseOrder/Reference/text()').getStringVal() = 'BLAKE-2001062514034328PDT' ";
Session session = instance.getSession();
org.hibernate.Query oQuery = session.createSQLQuery(query)
.addScalar("PODOC", Hibernate.STRING);
ArrayList arrResult = new ArrayList(oQuery.list());
This works fine if there exists no namespace in the XML but it fails in case we have a namespace we get the error
Quote:
select P.PODOCUMENT.extract('//PurchaseOrder/edi:price/text()', 'xmlns:edi="http://ecommerce.org/schema"').getStringVal() as PODOC from PURCHASEORDER P where P.PODOCUMENT.extract('//PurchaseOrder/Reference/text()').getStringVal() = 'BLAKE-2001062514034328PDT'
could not execute query
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at com.hibernate.example.AddItemAction.getXML(AddItemAction.java:124)
at com.hibernate.example.AddItemAction.main(AddItemAction.java:77)
Caused by: java.sql.SQLException: ORA-31013: Invalid XPATH expression
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:913)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:984)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2885)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2926)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 6 more
The XPATH is correct as the same we get the results if the same query is run using simple JDBC Connecyion object
Thanks,
Umesh
Login
FAQ
Search