OSB: how to use properly fn-bea:execute-sql

Full documentation is here
http://download.oracle.com/docs/cd/E14571_01/doc.1111/e15867/xquery.htm#OSBAG451

This statement:

assign "fn-bea:execute-sql('geo_osb_ds',
     xs:QName('Location'),
     'select SITENAME, LATITUDE, LONGITUDE from GEO_SITE where SITEID > 0')" to sites

will NOT work, only 1 element will be returned.

Bear in mind that bea:execute-sql returns a element()* (with an *), which means that the XML-Fragment returned by the function NEEDS A CONTAINER NODE.

If you want the entire result set to be returned, provide a container node:

<locations>
{fn-bea:execute-sql('geo_osb_ds',
     xs:QName('Location'),
     'select SITENAME, LATITUDE, LONGITUDE from GEO_SITE where SITEID > 0')
}     </locations>   


One more thing:
this

fn-bea:execute-sql('geo_osb_ds',xs:QName('Location'),
'select SITENAME, LATITUDE, LONGITUDE from GEO_SITE where SITEID = $sideId')

will NOT work, the binding of $siteId will not happen. You must use this syntax:

fn-bea:execute-sql('geo_osb_ds',xs:QName('Location'),
'select SITENAME, LATITUDE, LONGITUDE from GEO_SITE where SITEID = ?', $sideId)


Besides, there is no way you can assign a namespace to the result of the query,
the namespace will always be empty:

<java:Location>
<SITEID xmlns="">1.0</SITEID>
<SITENAME xmlns="">Suresh</SITENAME>
<STATEID xmlns="">345.0</STATEID>
<COUNTRYID xmlns="">23.0</COUNTRYID>
</java:Location>


so when you acces it with XPath you should do something like:

declare namespace empty = "";

let $countryId = $item/empty:COUNTRYID/text()


Also, even if you provide a column name in lowercase, as in
select SITEID as siteid...
fn-bea:execute-sql will convert the element name in UPPERCASE.
This is a major pain in the neck, it means that you still need to map this element into whatever case you need.



As for using the SQL IN clause, forget to be able to do it using bind variable "?" in the SQL statement, and passing a XQuery string like 'element1, element2' : it will simply not work, I have tried everything. I don't know which SQL Statement is exactly generated, I should activate the SQL DEbug flag....
A workaround is forget the fn-bea:execute-sql ? bind variable mechanism and build your SQL query dynamically: 



let $sqlStat := fn:concat('select bla from bla where bla in "', $myListOfMatches, "'")
fn-bea:execute-sql('geo_osb20_ds', xs:QName('ns1:Location'), $sqlStat)

Dirty trick but it works.