SELECT
    @max = @x.query(' 
                        { count(/Employees/Employee) }
                     ').value('e[1]','int')

select @max = count(*) from
    @x.nodes('/Employees/Employee')
    e(x)

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);

SELECT x.value('Name[1]', 'VARCHAR(20)') 
FROM @x.nodes('/Employees/Employee')  e(x);