declare @x xml ='<?xml version="1.0" encoding="utf-8" ?> 
<Employee>
<EmployeeData>
<EmployeeID>65938</EmployeeID> 
<SID>schauhan</SID> 
<Name>
<FirstName>Sachin</FirstName> 
<LastName>Chauhan</LastName> 
<MI /> 
</Name>
<ExternalEmailID>sachinchauhan@abc.com</ExternalEmailID> 
<Status>
<Code>Value</Code>
<Description>Value</Description> 
</Status>
<JoiningDate>01-05-2013</JoiningDate> 
<ReportDate>01-05-2010</ReportDate> 
<YearsOfExp>3</YearsOfExp> 
<Area>
<Code>MLR</Code> 
<Description>Value</Description> 
</Area>
<Company>
<Code>SEC</Code> 
<Description>Value</Description> 
</Company>
<WorCulture>Value</WorCulture> 
<Location>Value</Location> 
<Experiences>
<ExperiencesPer>
<ExperiencesPer1>Value</ExperiencesPer1> 
<SExperience>Value</SExperience> 
</ExperiencesPer>
</Experiences>
<OverallRating>Value</OverallRating> 
</EmployeeData>
</Employee>'

SELECT p.q.value('EmployeeID[1]','int') AS EmployeeID,
p.q.value('SID[1]','varchar(100)') AS SID,
p.q.value('(Name/FirstName)[1]','varchar(100)') AS FirstName,
p.q.value('(Name/LastName)[1]','varchar(100)') AS LastName,
p.q.value('ExternalEmailID[1]','varchar(100)') AS ExternalEmailID,
p.q.value('(Status/Code)[1]','varchar(100)') AS Code,
p.q.value('(Status/Description)[1]','varchar(100)') AS Description,
p.q.value('JoiningDate[1]','datetime') AS JoiningDate,
p.q.value('ReportDate[1]','datetime') AS ReportDate,
p.q.value('YearsOfExp[1]','int') AS YearsOfExp,
p.q.value('(Area/Code)[1]','varchar(100)') AS AreaCode,
p.q.value('(Area/Description)[1]','varchar(100)') AS AreaDescription,
p.q.value('(Company/Code)[1]','varchar(100)') AS CompCode,
p.q.value('(Company/Description)[1]','varchar(100)') AS CompDescription,
p.q.value('WorCulture[1]','varchar(100)') AS WorCulture,
p.q.value('Location[1]','varchar(100)') AS Location,
p.q.value('(Experiences/ExperiencesPer/ExperiencesPer1)[1]','varchar(100)') AS ExperiencesPer1,
p.q.value('(Experiences/ExperiencesPer/SExperience)[1]','varchar(100)') AS SExperience,
p.q.value('OverallRating[1]','varchar(100)') AS OverallRating
FROM @x.nodes('/Employee/EmployeeData') p(q)


output
-----------------------------------------------------------------------------
EmployeeID	SID	FirstName	LastName	ExternalEmailID	Code	Description	JoiningDate	ReportDate	YearsOfExp	AreaCode	AreaDescription	CompCode	CompDescription	WorCulture	Location	ExperiencesPer1	SExperience	OverallRating
.....................................................................................................................................................................................................................................................................................................
65938	schauhan	Sachin	Chauhan	sachinchauhan@abc.com	Value	Value	2013-01-05 00:00:00.000	2010-01-05 00:00:00.000	3	MLR	Value	SEC	Value	Value	Value	Value	Value	Value