USP_SITE_GETHIERARCHY

            create procedure dbo.USP_SITE_GETHIERARCHY
            as begin

                set nocount on;

                select 
                    SITE.ID as SITEID, 
                    (select 
                        PARENTSITE.ID 
                    from 
                        dbo.SITE AS PARENTSITE 
                    where 
                        SITE.HIERARCHYPATH.GetAncestor(1) = PARENTSITE.HIERARCHYPATH
                    ) as PARENTSITEID, 
                    SITE.NAME, 
                    row_number() over (partition by SITE.HIERARCHYPATH.GetAncestor(1) order by HIERARCHYPATH) as SEQUENCE
                from 
                    dbo.SITE 
                order by 
                    HIERARCHYPATH

                return 0;                
            end