Try the following, then just join normally:

DECLARE @xmlInsertRecordString XML

DECLARE @iDoc INT

SET @xmlInsertRecordString = '<AllForcast>

<SESA>SESA99999</SESA>

<EmployeeName>Alex Bea</EmployeeName>

<HiringStatus>Full Time</HiringStatus>

<EmployeeGroup>HW</EmployeeGroup>

<EmployeeSub-Group>HW</EmployeeSub-Group>

<City>Burnaby</City>

<ProjectStatus>Active</ProjectStatus>

<ProjectName>AB-1</ProjectName>

<ProjectCode>411</ProjectCode>

<ProjectManager>John Doe</ProjectManager>

<Notes></Notes>

<Date>Jan 1 2016</Date>

<Effort>1</Effort>

<RecordBy>SESA123456</RecordBy>

<RecordID>4</RecordID>

</AllForcast>'

EXEC sp_xml_preparedocument @iDoc OUTPUT, @xmlInsertRecordString

SELECT

*

FROM

OPENXML( @iDoc, '//AllForcast')

WITH (EmpSubGroup VARCHAR( 50) 'EmployeeSub-Group',

SESA VARCHAR(50) 'SESA',

ProjectCode VARCHAR(50) 'ProjectCode'

) AS ox

EXEC sp_xml_removedocument @iDoc