Need Jackrabbit sql 2 equivalent query for xpath query

Question

I need a jackrabbit sql 2 equivalent query for this xpath query

xpath = "/jcr:root//institutes/institute[*]/(@title | @overallScore)"

I have this in place for sql2 I can get '/IN/institues/institute' by using ISCHILDNODE() constraint

But I want to return all institutes in this way '/%/institutes/institute'. If I can achieve this using join please let me know the full statement

Currenlty I am using this query but with no success

ref: reference link

sql2 = "SELECT institute.title, institute.overallScore FROM [nt:unstructured] AS country "
            + "INNER JOIN [nt:unstructured] AS institutes ON ISCHILDNODE(institutes, country) "
            + "INNER JOIN [nt:unstructured] AS institute ON ISCHILDNODE(institute, institutes) "
            + "WHERE NAME(institutes) = 'institutes' ORDER BY institute.overallScore DESC";

I also found that PATH() like is not implemented in jackrabbit

Answer 1

I didn't test it myself, and I'm not sure if it's efficient, but this query should work:

select b.[jcr:path] as [jcr:path], 
       b.[title] as [title], 
       b.[overallScore] as [overallScore] 
from [nt:base] as a 
inner join [nt:base] as b on ischildnode(b, a) 
where name(a) = 'institutes' 
and name(b) = 'institute'