Link to home
Start Free TrialLog in
Avatar of milani_lucie
milani_lucieFlag for United States of America

asked on

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"?>
<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="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
Avatar of MohammedU
MohammedU
Flag of United States of America image

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
Avatar of Mark Wills
The record section needs to match the incoming file...

will type it up and test and get back to you...
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
Avatar of milani_lucie

ASKER

I have followed your syntax and converted the Openrowset into BCP. Sorry for that !

Thanks