[Solved] Custom hierarchy

Here is a generic method regardless of the level of the hierarchy.

SQL

-- DDL and sample data population, start
DECLARE @tbl table (
    idGeo INT IDENTITY PRIMARY KEY,
    GEO   VARCHAR(64),
    PARENTID INT
);

insert into @tbl (GEO, PARENTID) values 
(   'EMEA',    NULL),
(   'France',  1),
(   'mIDCAPSfRANCE',   2),
(   'Germany', 1),
(   'France exl midcaps',  2),
(   'Amercias',    NULL),
(   'US',  6);
-- DDL and sample data population, end

--SELECT * FROM @tbl;

WITH cte AS 
(
    -- Anchor query
    SELECT idGEO, GEO, ParentID, 1 AS [Level]
        , CAST("https://stackoverflow.com/" + GEO AS VARCHAR(1000)) AS XPath
    FROM @tbl
    WHERE ParentID IS NULL
    UNION ALL
    -- Recursive query
    SELECT t.idGEO, t.GEO, t.ParentID, cte.[Level] + 1 AS [Level]
        , CAST(cte.[XPath] + "https://stackoverflow.com/" +  t.GEO AS VARCHAR(1000)) AS [XPath]
    FROM @tbl AS t
        INNER JOIN cte ON t.ParentID = cte.idGEO
    WHERE t.ParentID IS NOT NULL 
)
SELECT idGEO
    , REPLICATE('  ',[Level]-1) + GEO AS GEOHierarchy
    , GEO, ParentID, [Level], [XPath]
FROM cte
ORDER BY XPath;

Output