Oracle XMLDB's XMLCAST and XMLQUERY incompatible with iBatis?
- by tthong
I've been trying to select a list of values from XMLs stored in an
XMLType column but I keep getting the errors which are listed at the
tail end of this post.
The select id is
getXMLFragment
, and the relevant subset of the
sqlmap.xml is as follows:
<select id="getXMLFragment" resultClass="list">
SELECT
XMLCAST(XMLQUERY('$CUSTOMER/CUSTOMER/DETAILS/
CUST_NAME/text()' PASSING CUSTOMER AS
"CUSTOMER" RETURNING CONTENT) AS VARCHAR2(20))
AS customers FROM SHOP.CLIENT_INFO
</select>
(CUSTOMER is an XMLType column in CLIENT_INFO)
and I call the statement using
List<String> custNames= (List<String>)
sqlMap.queryForList("getXMLFragment");
I am using ibatis-2.3.4.726.jar.
Is it because iBatis does not recognise XMLDB queries and hence,
tokenizes the string wrongly? On a sidenote, I have implemented
XMLTypeCallback.java to handle XMLType insertions successfully, and I
think it will work should I wish to retrieve the entire XML. However,
in this case, I need to extract only individual values due to
requirements. A workaround would be greatly appreciated.
Thanks in advance.
The exceptions generated are listed below:
--- The error occurred in sqlMap.xml.
--- The error occurred while preparing the mapped statement for
execution.
--- Check the getXMLFragment.
--- Check the SQL statement.
--- Cause: java.util.NoSuchElementException
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:
204)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:
139)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:
567)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:
541)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:
118)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:
122)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:
98)
at Main.main(Main.java:60)
Caused by: java.util.NoSuchElementException
at java.util.StringTokenizer.nextToken(StringTokenizer.java:332)
at
com.ibatis.sqlmap.engine.mapping.sql.simple.SimpleDynamicSql.processDynamicElements(SimpleDynamicSql.java:
90)
at
com.ibatis.sqlmap.engine.mapping.sql.simple.SimpleDynamicSql.getSql(SimpleDynamicSql.java:
45)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:
184)
... 7 more
