Sample XML Format Files

The examples in this topic illustrate key aspects of using XML format files. The syntax of a format file is independent of the direction of the operation; that is, the syntax is the same for bulk export and bulk import.

The examples illustrate the following:

• Ordering character-data fields the same as table columns

• Ordering data fields and table columns differently

• Omitting a data field

• Mapping different types of fields to columns

• Mapping XML data to a table

• Importing fixed-length or fixed-width fields

Note

For information about how to create format files, see Creating a Format File.

Examples

In the data files shown in the following examples, <tab> indicates a tab character in a data file, and <return> indicates a carriage return.

A. Ordering character-data fields the same as table columns

The following example shows an XML format file that describes a data file containing three fields of character data. The format file maps the data file to a table that contains three columns. The data fields correspond one-to-one with the columns of the table.

Table (row): Person (Age int, FirstName varchar(20), LastName varchar(30))

Data file (record): Age<tab>Firstname<tab>Lastname<return>

The following XML format file reads from the data file to the table.

In the <RECORD> element, the format file represents the data values in all three fields as character data. For each field, the TERMINATOR attribute indicates the terminator that follows the data value.

The data fields correspond one-to-one with the columns of the table. In the <ROW> element, the format file maps the column Age to the first field, the column FirstName to the second field, and the column LastName to the third field.

<?xml version="1.0"?>
<BCPFORMAT 
xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" 
      MAX_LENGTH="12"/> 
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" 
      MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" 
      MAX_LENGTH="30" 
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

Note

For an equivalent AdventureWorks2008R2 example, see Creating a Format File.

B. Ordering data fields and table columns differently

The following example shows an XML format file that describes a data file containing three fields of character data. The format file maps the data file to a table that contains three columns that are ordered differently from the fields of the data file.

Table (row): Person (Age int, FirstName varchar(20), LastName varchar(30))

Data file (record): Age<tab>Lastname<tab>Firstname<return>

In the <RECORD> element, the format file represents the data values in all three fields as character data.

In the <ROW> element, the format file maps the column Age to the first field, the column FirstName to the third field, and the column LastName to the second field.

<?xml version="1.0"?>
<BCPFORMAT 
xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" 
      MAX_LENGTH="12"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" 
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" 
      MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="firstname" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="2" NAME="lastname" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

Note

For an equivalent AdventureWorks2008R2 example, see Using a Format File to Map Table Columns to Data-File Fields.

C. Omitting a data field

The following example shows an XML format file that describes a data file containing four fields of character data. The format file maps the data file to a table that contains three columns. The second data field does not correspond to any table column.

Table (row): Person (Age int, FirstName Varchar(20), LastName Varchar(30))

Data file (record): Age<tab>employeeID<tab>Firstname<tab>Lastname<return>

In the <RECORD> element, the format file represents the data values in all four fields as character data. For each field, the TERMINATOR attribute indicates the terminator that follows the data value.

In the <ROW> element, the format file maps the column Age to the first field, the column FirstName to the third field, and the column LastName to the fourth field.

<?xml version="1.0"?>

<BCPFORMAT 
xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" 
      MAX_LENGTH="12"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" 
      MAX_LENGTH="10" 
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" 
      MAX_LENGTH="20" 
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" 
      MAX_LENGTH="30" 
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="firstname" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="4" NAME="lastname" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

Note

For an equivalent AdventureWorks2008R2 example, see Using a Format File to Skip a Data Field.

D. Mapping <FIELD> xsi:type to <COLUMN> xsi:type

The following example shows different types of fields and their mappings to columns.

<?xml version = "1.0"?>
<BCPFORMAT
xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <RECORD>
      <FIELD xsi:type="CharTerm" ID="C1" TERMINATOR="\t" 
            MAX_LENGTH="4"/>
      <FIELD xsi:type="CharFixed" ID="C2" LENGTH="10" 
         COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>
      <FIELD xsi:type="CharPrefix" ID="C3" PREFIX_LENGTH="2" 
         MAX_LENGTH="32" COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>
      <FIELD xsi:type="NCharTerm" ID="C4" TERMINATOR="\t" 
         MAX_LENGTH="4"/>
      <FIELD xsi:type="NCharFixed" ID="C5" LENGTH="10" 
         COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>
      <FIELD xsi:type="NCharPrefix" ID="C6" PREFIX_LENGTH="2" 
         MAX_LENGTH="32" COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>
      <FIELD xsi:type="NativeFixed" ID="C7" LENGTH="4"/>
   </RECORD>
   <ROW>
      <COLUMN SOURCE="C1" NAME="Age" xsi:type="SQLTINYINT"/>
      <COLUMN SOURCE="C2" NAME="FirstName" xsi:type="SQLVARYCHAR" 
      LENGTH="16" NULLABLE="NO"/>
      <COLUMN SOURCE="C3" NAME="LastName" />
      <COLUMN SOURCE="C4" NAME="Salary" xsi:type="SQLMONEY"/>
      <COLUMN SOURCE="C5" NAME="Picture" xsi:type="SQLIMAGE"/>
      <COLUMN SOURCE="C6" NAME="Bio" xsi:type="SQLTEXT"/>
      <COLUMN SOURCE="C7" NAME="Interest"xsi:type="SQLDECIMAL" 
      PRECISION="5" SCALE="3"/>
   </ROW>
</BCPFORMAT>

E. Mapping XML data to a table

The following example creates an empty two-column table (t_xml), in which the first column maps to the int data type and the second column maps to the xml data type.

CREATE TABLE t_xml (c1 int, c2 xml)

The following XML format file would load a data file into table t_xml.

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="8"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNCHAR"/>
 </ROW>
</BCPFORMAT>

F. Importing fixed-length or fixed-width fields

The following example describes fixed fields of 10 or 6 characters each. The format file represents these field lengths/widths as LENGTH="10" and LENGTH="6", respectively. Every row of the data files ends with a carriage return-line feed combination, {CR}{LF}, which the format file represents as TERMINATOR="\r\n".

<?xml version="1.0"?>
<BCPFORMAT
       xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="C1" xsi:type="SQLINT" />
    <COLUMN SOURCE="2" NAME="C2" xsi:type="SQLINT" />
  </ROW>
</BCPFORMAT>

Additional Examples

For additional examples of both non-XML format files and XML format files, see the following topics:

• Using a Format File to Skip a Table Column

• Using a Format File to Skip a Data Field

• Using a Format File to Map Table Columns to Data-File Fields

See Also

Concepts

Understanding XML Format Files

Understanding Non-XML Format Files

Schema Syntax for XML Format Files

Working with Format Files