milani_lucie
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
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
The record section needs to match the incoming file...
will type it up and test and get back to you...
will type it up and test and get back to you...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
ASKER
I have followed your syntax and converted the Openrowset into BCP. Sorry for that !
Thanks
Thanks
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