Parsing / breaking character separated values in a table in one query

Consider a table having following data,
Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123')

Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3')

Now if you want to break
the space separated values in the abovetable as;

ID     Val
1       Atif
1       Sheikh2
1       Sheikh123
2       Asif
2       Sheikh1
2       Sheikh2
2       Sheikh3

Here is the query;
;with wcte (id,val,newpos) as

(
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable

Union all

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos <> 1

)Select ID,Val from wcte order by id

Let me explain the above mentioned query. As you can see that i have used a
recursive function CTE.

The first query in the CTE query is;
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable
This creates the main record for parsing the string. This
loads the first value in the required data set and to use in the second part.
Here I used the newpos column to get the position to
parse the next value in the character separated string. It is just the length
of the previous parsed value. The second part is;

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos > 1

This query uses the posintion in newpos column and parses the next value. And as is is recursive, it goes on until the newpos
> 1. newpos, as you can
see in the first part of the query, is the charindex of the character which is
used to separate the values in the string.

Thats it...