Solved

# BCP - XML Format file help needed !

Posted on 2008-11-16
941 Views
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
0
Question by:milani_lucie

LVL 15

Expert Comment

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
0

LVL 51

Expert Comment

The record section needs to match the incoming file...

will type it up and test and get back to you...
0

LVL 51

Accepted Solution

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>

0

LVL 51

Expert Comment

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 ?
0

Author Comment

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

Thanks
0

## Featured Post

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
how to add IIS SMTP to handle application/Scanner relays into office 365.