Lets take as an example following table:
cmsPage
PageId Parrent PageId
6 NULL
44 6
50 44
51 44
52 6
Lets find all children of page with PageId 44
WITH PagesList (PageId)
AS
(
-- Anchor member definition
SELECT PageId FROM cmsPage WHERE PageId=44
UNION ALL
-- Recursive member definition
SELECT c.PageId From cmsPage as c
INNER JOIN PagesList as p
ON c.ParrentPageId = p.PageId
)
-- Statement that executes the CTE
SELECT PageId FROM PagesList
GO
Result would be:
44
50
51