This is what you are looking for.

DECLARE @x xml;

SELECT @x = P

FROM OPENROWSET (BULK '\\usd\SurveyComputing\Sample\SampleRepository\Visit_Survey_2013_08_16_07_21_43.XML', SINGLE_BLOB) AS FMG(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

select *

from OPENXML (@hdoc, 'SURVEY_EXTRACT/VISIT_SURVEY', 2)

WITH

(

SURVEY_ID int,

SURVEY_TYPE nvarchar(128),

ENGLOC_ID int,

INDEX_NUM nvarchar(128),

RECORD_NUM nvarchar(128),

FM_MMM nvarchar(128),

FINAL_CONFERENCE_DATE datetime,

VISIT_TYPE nvarchar(128),

SERVICE_TYPE nvarchar(128),

[LANGUAGE] nvarchar(128),

LOCATION_ADDRESS_1 nvarchar(128),

LOCATION_ADDRESS_2 nvarchar(128),

LOCATION_CITY nvarchar(128),

LOCATION_STATE_PROVINCE nvarchar(128),

LOCATION_POSTAL_CODE nvarchar(128),

LOCATION_COUNTRY nvarchar(128),

ACCOUNT_NUM nvarchar(128),

ACCOUNT_NAME nvarchar(128),

AE_INSURANCE_OFFICE nvarchar(128),

FE_SERVICING_OFFICE nvarchar(128),

AE_NAME nvarchar(128),

AE_EMAIL nvarchar(128),

FE_NAME nvarchar(128),

VISIT_SURVEY_DATE nvarchar(128),

SURVEY_CNTCTS nvarchar(128),

SURVEY_CONTACT_ID int 'SURVEY_CNTCTS/SURVEY_CONTACT_ID',

FIRST_NAME nvarchar(128) 'SURVEY_CNTCTS/FIRST_NAME',

LAST_NAME nvarchar(128) 'SURVEY_CNTCTS/LAST_NAME',

MIDDLE_INITIAL nvarchar(128) 'SURVEY_CNTCTS/MIDDLE_INITIAL',

PREFIX nvarchar(128) 'SURVEY_CNTCTS/PREFIX',

SUFFIX nvarchar(128) 'SURVEY_CNTCTS/SUFFIX',

PROFESSIONAL_TITLE nvarchar(128) 'SURVEY_CNTCTS/PROFESSIONAL_TITLE',

EMAIL nvarchar(128) 'SURVEY_CNTCTS/EMAIL',

PHONE nvarchar(128) 'SURVEY_CNTCTS/PHONE',

SURVEY_CNTCTS_ROLE nvarchar(128) 'SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE',

SURVEY_CONTACT_ROLE_ID nvarchar(128) 'SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE/SURVEY_CONTACT_ROLE_ID',

SURVEY_CONTACT_ROLE nvarchar(128) 'SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE/SURVEY_CONTACT_ROLE',

VISIT_SURVEY nvarchar(128) '../VISIT_SURVEY',

SURVEY_EXTRACT nvarchar(128) '../../SURVEY_EXTRACT'

)

exec sp_xml_removedocument @hdoc