UFN_SITE_GETPATH

            create function dbo.UFN_SITE_GETPATH(
                @SITEID uniqueidentifier
            )
            returns nvarchar(1000)
            as
            begin
                declare @PATH nvarchar(1000);

                with CTE_BASEUP(ID, PARENTSITEID, NAME)
                as(
                    select
                        SITE.ID,
                        (    
                            select 
                                PARENTSITE.ID 
                            from 
                                dbo.SITE [PARENTSITE] 
                            where 
                                SITE.HIERARCHYPATH.GetAncestor(1) = PARENTSITE.HIERARCHYPATH
                        ) as PARENTSITEID,
                        SITE.NAME
                    from dbo.SITE
                    where SITE.ID = @SITEID

                    union all

                    select
                        SITE.ID,
                        (    
                            select 
                                PARENTSITE.ID 
                            from 
                                dbo.SITE [PARENTSITE] 
                            where 
                                SITE.HIERARCHYPATH.GetAncestor(1) = PARENTSITE.HIERARCHYPATH
                        ) as SITEID,
                        SITE.NAME
                    from dbo.SITE
                        inner join CTE_BASEUP on CTE_BASEUP.PARENTSITEID=SITE.ID
                ),
                CTE_ROOTDOWN(ID, PARENTSITEID, NAME, PATH)
                as(
                    select
                        CTE_BASEUP.ID,
                        CTE_BASEUP.PARENTSITEID,
                        CTE_BASEUP.NAME,
                        convert(nvarchar(1000), CTE_BASEUP.NAME)
                    from CTE_BASEUP
                    where PARENTSITEID is null

                    union all

                    select
                        CTE_BASEUP.ID,
                        CTE_BASEUP.PARENTSITEID,
                        CTE_BASEUP.NAME,
                        convert(nvarchar(1000), CTE_ROOTDOWN.PATH + '\' + CTE_BASEUP.NAME)
                    from CTE_BASEUP
                        inner join CTE_ROOTDOWN on CTE_ROOTDOWN.ID=CTE_BASEUP.PARENTSITEID

                )
                select @PATH=PATH
                from CTE_ROOTDOWN
                where ID = @SITEID;

                return @PATH;
            end