You can retrieve all names without a CTE and CROSS APPLY as well:

DECLARE @x XML;

SET @x = '<Employees>

<Employee ID="101">

<Name>Jacob</Name>

<Department>IT</Department>

</Employee>

<Employee ID="354">

<Name>Steve</Name>

<Department>IT</Department>

</Employee>

<Employee ID="456">

<Name>Bob</Name>

<Department>IT</Department>

</Employee>

<Employee ID="478">

<Name>Joe</Name>

<Department>IT</Department>

</Employee>

<Employee ID="981">

<Name>Louis</Name>

<Department>IT</Department>

</Employee>

</Employees>';

-- select "Name"

WITH Num(i)

AS

(

SELECT 1

UNION ALL

SELECT i + 1

FROM Num

WHERE i < (SELECT @x.value('count(/Employees/Employee)','int') )

)

SELECT x.value('Name[1]', 'VARCHAR(20)')

FROM Num

CROSS APPLY @x.nodes('/Employees/Employee[position()=sql:column("i")]') e(x);

That's if you really want to use the position() function. Without using position() it gets a little simpler:

SELECT x.value('Name[1]', 'VARCHAR(20)')

FROM @x.nodes('/Employees/Employee') e(x);