Splitting different parts of a string
Splitting a string with a recursive CTE is usually a bad choice but this is a case where I at least would consider a solution using that. 

If you used a faster string split technique you would have to preserve the order of items and rebuild parent_category using for xml. That might still be faster than using a recursive CTE but here is the recursive stuff anyway. 

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  category_path nvarchar(max)
);

insert into YourTable values
('RootCategory\Middle Category\Child Category\Child of child category'),
('RootCategory\Middle Category\Other Child Category\');


Query 1:

with C as
(
  select left(category_path, charindex('\', category_path+'\') - 1) as category_name,
         stuff(category_path, 1, charindex('\', category_path+'\'), '') as category_path,
         cast(null as nvarchar(max)) as parent_path
  from YourTable
  union all
  select left(category_path, charindex('\', category_path+'\') - 1),
         stuff(category_path, 1, charindex('\', category_path+'\'), ''),
         coalesce(parent_path+'\', '')+category_name
  from C
  where category_path &lt;&gt; ''
)
select distinct
       category_name,
       parent_path
from C;


Results:

|           CATEGORY_NAME |                                 PARENT_PATH |
-------------------------------------------------------------------------
|          Child Category |                RootCategory\Middle Category |
| Child of child category | RootCategory\Middle Category\Child Category |
|         Middle Category |                                RootCategory |
|    Other Child Category |                RootCategory\Middle Category |
|            RootCategory |                                      (null) |