Search code examples
sql-serverbulkinsertbcp

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

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?

Solution

  • 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.