This worked for me:

declare @xml as xml = '

<Category>

<Employee EmployeeID="01">

<Name>

<Title>Mr</Title>

<Forenames>XXX</Forenames>

<Surname>YYY</Surname>

</Name>

</Employee>

<Employee EmployeeID="02">

<Name>

<Title>Mr</Title>

<Forenames>MMM</Forenames>

<Surname>ZZZ</Surname>

</Name>

</Employee>

<Employee EmployeeID="03">

<Name>

<Title>Mr</Title>

<Forenames>Caron</Forenames>

<Surname>Cobb</Surname>

</Name>

</Employee>

</Category>

'

select sum(cast(employee.exist('@EmployeeID') as int))

from @xml.nodes('/Category/Employee') xmlnode(employee)