Why do I get "XML parsing: line 2, character 0, incorrect document syntax" when Bulk Inserting in MS SQL Server

Question

I'm doing a BULK INSERT into a table using a FMT format file, but I get the following error:

XML parsing: line 2, character 0, incorrect document syntax

Here is my code

BULK INSERT [DM_Flux].[dbo].[Stage] FROM 'C:\temp\data.dat'
WITH (FORMATFILE = 'C:\temp\FormatBcp.fmt')

Here is the formatfile (standard format file, not XML):

10.0
5
1   SQLCHAR 0   2   ""  1   Id  ""
2   SQLCHAR 0   40  ""  2   Name    ""
3   SQLCHAR 0   50  ""  3   Street  ""
4   SQLCHAR 0   8   ""  4   StreetNo    ""
5   SQLCHAR 0   300 "\r\n"  7   BulkData    ""

Why do I get an XML error with this?

Answer 1

If the format file is encoded as Unicode the bulk insert will automatically think it is an XML-file and treat it as such. Make sure the file is encoded as ANSI and you should be fine.

Answer 2

Also another point just in case anyone else runs into this...

If you're sure the file is ANSI but still getting this error check the first line of the format file (the version number). The version number must match your SQL version number (or be an older version number).

MSDN reference:

The version of the bcp utility (Bcp.exe) used to read a format file must be the same as, or a later version than was used to create the format file. For example, SQL Server 2008 bcp can read a version 9.0 format file, which is generated by SQL Server 2005 bcp, but SQL Server 2005 bcp cannot read a version 10.0 format file, which is generated by SQL Server 2008 bcp.