BCP XML Format Files with SQL Server

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | > XML


One very common process that occurs in most SQL Server environments is the need to import and export data. One of the best tools to use for this is BCP (Bulk Copy Program).  When loading data into SQL Server, it is helpful to use a format flle that specifies what the incoming data looks like and in this article we will look at how to generate a standard format file and XML format file for BCP.


Creating a format file for BCP can be done by using a command similar to the following, which creates a format file based on the structure of the Categories table in the Northwind database.

bcp Northwind.dbo.Categories format nul -c -f categories.fmt -T -S servername

This creates a standard format file that can then be edited to handle differences in your source and destination formats.  When you run this command, this is the output you get.  For additional information take a look at books online.

1   SQLCHAR    0   12  "\t"     1   CategoryID    ""
2   SQLCHAR    0   30  "\t"     2   CategoryName  SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR    0   0   "\t"     3   Description   SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR    0   0   "\r\n"   4   Picture       ""

In order to create an XML version of the format file the command is identical except we use the -x parameter.

bcp AdventureWorks.HumanResources.Department format nul -c -x -f department.xml -T -S servername

This is the output from running the above command. Instead of having a simple text file we now have an XML file that is easier to understand as well as easier to modify.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http:// www.w3.org/2001/XMLSchema-instance">
    <FIELD ID="1" xsi:type="CharFixed" LENGTH="7"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="100"COLLATION="Latin1_General_CS_AS"/>
    <FIELD ID="3" xsi:type="CharFixed" LENGTH="100"COLLATION="Latin1_General_CS_AS"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"MAX_LENGTH="24"/>
    <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
    <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>

Other then how to create and modify the format files, the rest of BCP still works the same.  So you would still reference your format file just as before.  The -x option is only used when creating the format file.

Next Steps
  • Take a look at understanding the XML schema syntax
  • Take a look at how BCP works and how this could be an addition to your processing if you are not already using BCP
  • Take a look at how to create a format file and other options for creating a format file

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, April 13, 2018 - 6:48:41 PM - Tim Wilcox Back To Top (75698)

 Is there any information on how to do this with data that is numeric/decimals? In my case, it is numeric (5,2). An example is 123.45. It is my undertanding that one would do something as such but that does not work.


        <COLUMN SOURCE="6" NAME="AVGWAGE" xsi:type="SQLDECIMAL" precision="5" scale="2" nullable ="YES"/>


Friday, May 3, 2013 - 3:57:34 AM - John Back To Top (23698)



I need to insert/update data from txt file to sql server based on a primary key . Please help me in doing this. I am doing this from command prompt.

My scenario is I have data in a table and need to inset data if not present and if present need to update.


Thanks for all help

Tuesday, December 6, 2011 - 8:25:39 AM - Gergely Gazda Back To Top (15296)


How can I import an xml formatted data file into MSSQL using bcp?

Wednesday, March 16, 2011 - 3:00:23 PM - Greg Robidoux Back To Top (13238)

When using the -x parameter you also need to make sure you use the -f parameter and provide a format_file name.

Here is some more info about this: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Thursday, November 25, 2010 - 9:46:51 PM - Niall Back To Top (10397)

The -x gives me an error

bcp: Unknown option x

then tells me to try the help utility.

get free sql tips
agree to terms