Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

BCP - XML Format file help needed !

Posted on 2008-11-16
5
Medium Priority
?
964 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:milani_lucie
  • 3
5 Comments
 
LVL 15

Expert Comment

by:MohammedU
ID: 22977768
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

by:Mark Wills
ID: 22991029
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

by:
Mark Wills earned 1500 total points
ID: 22991069
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">
 <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>

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22993321
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

by:milani_lucie
ID: 22996779
I have followed your syntax and converted the Openrowset into BCP. Sorry for that !

Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question