Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















BCP XML Format Files with SQL Server 2005

MSSQLTips author Greg Robidoux By:   |   Read Comments (4)   |   Related Tips: More > XML

Problem
One very common process that occurs in most SQL Server environments is the need to import and export data.  With SQL Server 7.0 and 2000 we had DTS (Data Transformation Services) and now with SQL Server 2005 we have SSIS (SQL Server Integration Services).  These are great GUI environments and give you a lot of flexibility  to move data in and out of SQL Server.  In addition to these GUI tools there is the tried and true BCP (Bulk Copy Program) that has been around for a lot longer then DTS or SSIS. 

Even though this tool has been around quite some time, there are always enhancements and with SQL Server 2005 additional enhancements have been added as well.  One of the features of BCP is to create a format file that defines the data that is coming into SQL Server and where it should go during the import process.  With SQL Server 2005 an enhancement has been made so this format file can either be the standard format or in an XML format.

Solution
Creating a format file for bcp is not new to SQL Server 2005 and these files can be created using older versions as well as with SQL Server 2005.  This 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.

8.0
4
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">
  <RECORD>
    <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"/>
  </RECORD>
  <ROW>
    <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"/>
  </ROW>
</BCPFORMAT>

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


Last Update: 9/28/2006


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, November 25, 2010 - 9:46:51 PM - Niall Read The Tip

The -x gives me an error

bcp: Unknown option x

then tells me to try the help utility.


Wednesday, March 16, 2011 - 3:00:23 PM - Greg Robidoux Read The Tip

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


Tuesday, December 06, 2011 - 8:25:39 AM - Gergely Gazda Read The Tip

 

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


Friday, May 03, 2013 - 3:57:34 AM - John Read The Tip

Hi

 

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.