To retrive values  Employee Code & Name from xml...

Declare @tempEmp xml
set @tempEmp='<Root>
<Employee Code="1">
<Name>Tim</Name>
</Employee>
<Employee Code="2">
<Name>Jimmy</Name>
</Employee>
</Root>'

SELECT T.c.value('@Code','int'),T.c.value('Name[1]','VarChar(100)')
FROM @tempEmp.nodes('/Root/Employee') T(c)