Invoking database using xquery giving duplicate values

Question

I am using a XQuery to query database in an OSB project. Consider the following table:

userId Name    Category
------ ------- --------
1      Dheepan Student
2      Raju    Student

and the XQuery

let $userName:=fn-bea:execute-sql(
            $dataSourceJndiName,
            xs:string("NAME"),
            xs:string("select NAME from USER where CATEGORY= 'Student'")
           )/*:NAME[1]
return <root> {data($userName)} </root>

For this query I am getting the result as <root>Dheepan Raju</root>. But I need to return only one row even the query returns more than one row like the following <root>Dheepan</root>. I have used predicate [1] in the query but no clue why it concatenates the values and returning. Can anybody tell me how to return only the first row when more than one row is returned.

Answer 1

You need to use proper paranthesis:

let $userName:=(fn-bea:execute-sql(
$dataSourceJndiName,
xs:string("NAME"),
xs:string("select NAME from USER where CATEGORY= 'Student'")
)/*:NAME)[1]
return <root> {data($userName)} </root>