You will have to use a recursive Common Table Expression (CTE). I don't have your schema, but I believe from your Oracle query that this will yield the same result:
WITH MyCTE(type_id, id, obs_name)AS
(SELECT type_id, id, CAST([name] AS varchar(1024))
FROM prj_obs_units
WHERE id parent_id IS NULL
UNION ALL
SELECT c.type_id, c.id, CAST(p.obs_name + '\' + c.[name] AS varchar(1024))
FROM prj_obs_units AS c INNER JOIN MyCTE AS p ON c.parent_id = p.id)
SELECT type_id, id, obs_name
FROM MyCTE