SQL2 equivalent for this XPath query

Question

Is there an SQL2 query that is identical to the following XPath query:

/jcr:root/content/ancestor//parent/jcr:content

It should return the following nodes, for example:

/content/ancestor/a/b/c/parent/jcr:content
/content/ancestor/a/parent/jcr:content
/content/ancestor/parent/jcr:content

But not the following nodes:

/content/xxx/a/b/c/parent/jcr:content
/content/xxx/a/parent/jcr:content
/content/xxx/parent/jcr:content

Tomek Rękawek Tomek Rękawek · Accepted Answer · 2014-07-23T07:41:59

Following should work:

SELECT * FROM [nt:base] AS s 
  INNER JOIN [nt:base] AS parent
    ON ISCHILDNODE(s, parent)
WHERE
  ISDESCENDANTNODE(s, [/content/ancestor]) AND
  NAME(parent) = 'parent' AND
  NAME(s) = 'jcr:content'

If the type of s is cq:PageContent and type of parent is cq:Page you may use this information to make the query faster:

SELECT * FROM [cq:PageContent] AS s 
  INNER JOIN [cq:Page] AS parent
    ON ISCHILDNODE(s, parent)
WHERE
  ISDESCENDANTNODE(s, [/content/ancestor]) AND
  NAME(parent) = 'parent' AND
  NAME(s) = 'jcr:content'

SQL2 equivalent for this XPath query

2 Answers