# BCP - XML Format file help needed !

Hi,

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

x,y
1,2
2,3
3,4
4,5
5,6

I have the following table in SQL Server:

USE Sample
GO

-- Create a table

CREATE TABLE T1
(
b      int
)
GO

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

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 ?

Thanks
###### 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.

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

What's New in FOR XML in Microsoft SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx
The record section needs to match the incoming file...

will type it up and test and get back to you...
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"?>
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="b" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>


Experts Exchange Solution brought to you by