I am having extreme difficulty constructing a query which returns an XML style hierarchy.

We have a database table which contains a hierarchy of URLs for our website. The table contains the columns: ID, URL, DisplayName, ParentID, ItemOrder

The parent ID forms a recursive relationship between the current item and it's parent. The item should site below it's parent in the hierarchy and it should also be ordered using the item order against items at the same level in the hierarchy.

I have managed to get a recursive query working so it drills down the hierarchy sequentially but I cannot order this by the item order as well.

My current query is below:

WITH Parents AS
(
SELECT MenuItemId, URL, ParentItemId, ItemOrder
FROM CambsMenu

UNION ALL

SELECT si.MenuItemId, si.URL, si.ParentItemId, si.ItemOrder
FROM CambsMenu si INNER JOIN Parents p
ON si.ParentItemId = p.MenuItemId
)

SELECT DISTINCT *
FROM Parents