Given a table called Relations with columns IdNode, IdParentNode that describes relations between nodes, the following query uses a recursive CTE to return all ancestors (at all levels) for all child nodes.
;WITH cteTree AS
SELECT IdNode, IdParentNode, 1 as TreeLevel
SELECT cteTree.IdNode, R.IdParentNode, TreeLevel + 1
INNER JOIN Relations R ON cteTree.IdParentNode = R.IdNode
SELECT IdNode, IdParentNode, TreeLevel
OPTION(MAXRECURSION 200) means that it looks only 10 levels deep. You can use
OPTION(MAXRECURSION 0) if you want to traverse all the levels, though you have to make sure you don't have a circular reference.