DECLARE @X XML

SELECT @X = CAST(x AS XML)

FROM OPENROWSET(

BULK 'C:\temp\teste.xml',

SINGLE_BLOB) AS T(x)

INSERT INTO OSUSR_DFP_PEP_ENTITIES (ENT_ID, NAME, FIRSTNAME, LASTNAME, PREFIX, SUFFIX,

AKA, NAMESOURCE, PARENTID, GOVDESIGNATION, ENTRYTYPE, ENTRYCATEGORY, ENTRYSUBCATEGORY,

ORGANIZATION, POSITIONS, REMARKS, DOB, POB, COUNTRY, EXPIRATIONDATE, EFFECTIVEDATE,

PICTUREFILE, LINKEDTO, RELATED_ID, SOURCEWEBLINK, TOUCHDATE, DIRECTID, PASSPORTID,

NATIONALID, OTHERID, DOB2, ENTLEVEL, MASTERID, WATCH, RELATIONSHIPS)

SELECT X.product.value('(Ent_ID/text())[1]', 'INT'),

X.product.value('(Name/text())[1]', 'nvarchar(1000)'),

X.product.value('(FirstName/text())[1]', 'nvarchar(500)'),

X.product.value('(LastName/text())[1]', 'nvarchar(500)'),

X.product.value('(Prefix/text())[1]', 'nvarchar(500)'),

X.product.value('(Suffix/text())[1]', 'nvarchar(500)'),

X.product.value('(Aka/text())[1]', 'nvarchar(500)'),

X.product.value('(NameSource/text())[1]', 'nvarchar(500)'),

X.product.value('(ParentID/text())[1]', 'INT'),

X.product.value('(GovDesignation/text())[1]', 'nvarchar(500)'),

X.product.value('(EntryType/text())[1]', 'nvarchar(500)'),

X.product.value('(EntryCategory/text())[1]', 'nvarchar(500)'),

X.product.value('(EntrySubCategory/text())[1]', 'nvarchar(500)'),

X.product.value('(Organization/text())[1]', 'nvarchar(500)'),

X.product.value('(Positions/text())[1]', 'nvarchar(500)'),

X.product.value('(Remarks/text())[1]', 'nvarchar(max)'),

X.product.value('(DOB/text())[1]', 'nvarchar(500)'),

X.product.value('(POB/text())[1]', 'nvarchar(500)'),

X.product.value('(Country/text())[1]', 'nvarchar(500)'),

X.product.value('(ExpirationDate/text())[1]', 'nvarchar(500)'),

X.product.value('(EffectiveDate/text())[1]', 'nvarchar(500)'),

X.product.value('(PictureFile/text())[1]', 'nvarchar(500)'),

X.product.value('(LinkedTo/text())[1]', 'nvarchar(500)'),

X.product.value('(Related_ID/text())[1]', 'INT'),

X.product.value('(SourceWebLink/text())[1]', 'nvarchar(max)'),

X.product.value('(TouchDate/text())[1]', 'nvarchar(500)'),

X.product.value('(DirectID/text())[1]', 'nvarchar(500)'),

X.product.value('(PassportID/text())[1]', 'nvarchar(500)'),

X.product.value('(NationalID/text())[1]', 'nvarchar(500)'),

X.product.value('(OtherID/text())[1]', 'nvarchar(50)'),

X.product.value('(DOB2/text())[1]', 'nvarchar(500)'),

X.product.value('(EntLevel/text())[1]', 'nvarchar(500)'),

X.product.value('(MasterID/text())[1]', 'INT'),

X.product.value('(Watch/text())[1]'), 'bit',

X.product.value('(Relationships/text())[1]', 'bit')

FROM @X.nodes('Export/Entities') AS X(product);