BCP - XML Format file help needed !


I have the following data file in c:\T1.txt:


I have the following table in SQL Server:

USE Sample

-- Create a table

      b      int

I came to know that format files are used when - you do NOT need to import certain fields. I have created the format file using BCP command:

-- Create a format file (Issue this command at command prompt)

bcp Sample.dbo.T1 format nul -c -x -f c:\Format.xml -t, -T

Here is the format file i have got:

<?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="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
  <COLUMN SOURCE="1" NAME="b" xsi:type="SQLINT"/>

My intention is that, i need to copy Y column contents in the data file into table T1. Now i have tried to import the data using BCP utility:

-- Use BCP utility to IMPORT from a TXT file (Issue this command at command prompt)

bcp Sample.dbo.T1 in c:\T1.txt -f c:\Format.xml -T -F2

I am getting errors. Can you please FIX this and make it working ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try using FOR XML option in your query...
Check BOL for syntax..

What's New in FOR XML in Microsoft SQL Server 2005
Mark WillsTopic AdvisorCommented:
The record section needs to match the incoming file...

will type it up and test and get back to you...
Mark WillsTopic AdvisorCommented:
Yep, that is all it was... And the firstrow needs to be ignored.

When debugging these things, find it much easier to use openrowset  e.g.

select * FROM Openrowset(Bulk 'C:\ee\t1.txt', formatfile = 'c:\ee\format.xml', firstrow=2) A

and of course you can use that to insert into your table - kind of handy if you need to convert datatypes (usually make the <ROW> elements in the format char so you can validate by importing into a staging table first)...

<?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="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
  <COLUMN SOURCE="2" NAME="b" xsi:type="SQLINT"/>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Thanks, wondering why the "B" - did i miss something out ? thought your question was about the XML Format File ? How could I have answered any better ?
milani_lucieAuthor Commented:
I have followed your syntax and converted the Openrowset into BCP. Sorry for that !

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.