?
Solved

XML format files in BCP example needed ...

Posted on 2008-11-15
4
Medium Priority
?
1,148 Views
Last Modified: 2012-05-05
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
Comment
Question by:milani_lucie
  • 2
4 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 22970049
HI

Take a look at this article
http://www.mssqltips.com/tip.asp?tip=1060
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22971079
SQL Server Books Online has good examples
0
 
LVL 51

Expert Comment

by:Mark Wills
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="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="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

Open in new window

0
 
LVL 51

Accepted Solution

by:
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="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="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="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="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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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…

809 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