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

score 1 · Accepted Answer · answered Jul 23 '14 at 07:41

1

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'

answered Jul 23 '14 at 07:41

Tomek Rękawek

9,204
2
27
43

do you know why XPATH was deprecated in JCR 2? XPath queries are sometimes much shorter and more readable compared to SQL2 queries. – TheFooProgrammer Jul 23 '14 at 12:31
1

Even though it was deprecated, I'm still using XPATH because of this. Internaly they are translated to SQL2 though. It is so much easier to map the node structure to XPATH. So I'd also like to know why we should use the way more complex and hard to read SQL2 over XPATH. – Thomas Jul 24 '14 at 06:32

score -3 · Answer 2 · answered Jul 23 '14 at 14:57

-3

you can easily convert xpath to sql2 and vice-verse using "query tool" in crxde enter image description here

answered Jul 23 '14 at 14:57

apurvc

740
4
12

3

You can't. The purpose of this dropdown is to define the type of query entered in the *Query* field. – Tomek Rękawek Jul 24 '14 at 08:18

SQL2 equivalent for this XPath query

2 Answers2