First format the data correctly by selecting each type of location into a view with a union. Start with the types at the top of the tree. Then use recursion to build the tree.

recursion.sql

CREATE location_recursion AS

WITH recursion_tree AS 
(
	SELECT	location_id, name, friendly_id, parent_id, manager_id, location_type, parent_location_type,
			CAST(location_id AS VARCHAR(1000)) AS Path
    FROM         dbo.__Location_Children_vw
    WHERE     (parent_id IS NULL)
    UNION ALL
    SELECT  t.location_id, t.name, t.friendly_id, t.parent_id, t.manager_id, t.location_type, t.parent_location_type,
			CAST(a.path + '/' + CAST(t.location_id AS VARCHAR(1000)) AS VARCHAR(1000)) AS Path
      FROM  __Location_Children_vw t 
			INNER JOIN recursion_tree a 
			ON t.parent_id = a.location_id
)
SELECT *, len([path]) - len(replace([path], '/', '')) + 1  as [ancestry_depth] 
FROM   recursion_tree