Implement support for native recursive query functionality of popular DBMSes

We have a couple of areas in our system where need to load tree structures from our database. These trees can be very deep (often 20+ levels). SQL Server 2005 introduced Common Table Expressions, a kind of in-line view that can be used recursively.

These allow us to quickly get a result set like this:

id, label, parentId
1, foo, null
2, foo2, 1
3, foo3, 2
4, foo4, 2
5, foo5, 4
6, foo6, 1

From a tree like this:

[1, foo]

using a query like this (not tested):

with MyCTE(
id,
label,
parentId)
as
( select n.id, n.label, n.parentid
from Node n
UNION ALL
select c.id, c.label, c.parentId
from MyCTE c
inner join Node n on n.id=c.parentId)
select * from MyCTE

It happens many times more quickly than we've been able to load the graphs with Hibernate, even using batching etc.

We've tried using hibernate's built in native sql support without success. The alias injection breaks the Common Table Expression definition ( with MyCTE ), as it is a view definition and doesn't allow for the 'id as id_276' syntax, rather requiring just a column name.

I know that there are several other major DBMSes that support recursive querying now. Is there a way to have support for this functionality in hibernate core?