Solved

# XML format files in BCP example needed ...

Posted on 2008-11-15
Medium Priority
1,148 Views
Hi,

Can you please provide me a link / article on XML format files in BCP ? I want to know how the XML format file will be used to either - to remove some columns while import / export or changing the positions of the columns.

Thanks
0
Question by:milani_lucie
• 2

LVL 19

Expert Comment

ID: 22970049
HI

http://www.mssqltips.com/tip.asp?tip=1060
0

LVL 18

Expert Comment

ID: 22971079
SQL Server Books Online has good examples
0

LVL 51

Expert Comment

ID: 22975270
The XML Format files really just describe the columns used in the import file... If you have played with format files, then it is not such a huge leap.

Basically in two sections... RECORDS describe the incoming record layout, ROWS describe the receiving table. Then is is pretty much a matter of manipulating what and how you want - like field and record terminators, lengths, skipping columns, data types etc. Now it does seem to be a bit more "fussy" than the old format, but it is more flexible.

Still recommend importing into a generic staging table then populating the real table from there... It also keeps the format file pretty easy...

The experts above have posted links, so I will just post a sample below...

incoming DATA file looks like :
1;markwills;ee
2;jmoss111;ee
3;elimesika;ee

populating a table that looks like :
create table my_import_ee_bestest (ee_id int, ee_signature varchar(20), ee_origin varchar(20))

then the appropriate FORMAT file would look like (and propably best to use real column names) :

<BCPFORMAT
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";"    MAX_LENGTH="20" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";"    MAX_LENGTH="20" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="TCOLUMN1" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="TCOLUMN2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="TCOLUMN3" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

So, if you save the above DATA file as c:\EE\DATA.txt and the above format file as C:\EE\FORMAT.xml then the following command will work:

select * from openrowset(bulk 'c:\ee\data.txt', formatfile = 'c:\ee\format.xml') as a

0

LVL 51

Accepted Solution

Mark Wills earned 1500 total points
ID: 22975366
Oh, and just to finish off....

The RECORD part must match the incoming file, no questions as far as I am concerned - it helps validate / checks the layout as well, but if you want to skip a few columns, then in the ROWS section, simply remove those lines : e.g. if we no longer need the third column :

<BCPFORMAT
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";"    MAX_LENGTH="20" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";"    MAX_LENGTH="20" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="TCOLUMN1" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="TCOLUMN2" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

or, if we wanted to swap column 2 for 3 then :

<BCPFORMAT
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";"    MAX_LENGTH="20" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";"    MAX_LENGTH="20" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="TCOLUMN1" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="TCOLUMN2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="TCOLUMN3" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

So, it is a lot more flexible now...
0

## Featured Post

Question has a verified solution.

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

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month13 days, 22 hours left to enroll