XML 格式化文件 (SQL Server)XML Format Files (SQL Server)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server 提供了一个 XML 架构,该架构定义了编写“XML 格式化文件”(用于将数据大容量导入到 SQL ServerSQL Server 表中)的语法。provides an XML schema that defines syntax for writing XML format files to use for bulk importing data into a SQL ServerSQL Server table. XML 格式化文件必须符合用 XML 架构定义语言 (XSDL) 定义的这种架构。XML format files must adhere to this schema, which is defined in the XML Schema Definition Language (XSDL). 只有当 SQL ServerSQL Server 工具和 SQL ServerSQL Server Native Client 一起安装后,才支持 XML 格式化文件。XML format files are only supported when SQL ServerSQL Server tools are installed together with SQL ServerSQL Server Native Client.

可以结合使用 XML 格式化文件和 bcp 命令、BULK INSERT 语句或 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句。You can use an XML format file with a bcp command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. 使用 bcp 命令,你可以自动生成表的 XML 格式化文件;有关详细信息,请参阅 bcp UtilityThe bcp command allows you to automatically generate an XML format file for a table; for more information, see bcp Utility.

备注

大容量导出和导入支持两种类型的格式化文件: 非 XML 格式化文件XML 格式化文件Two types of format files are supported for bulk exporting and importing: non-XML format files and XML format files. XML 格式化文件比非 XML 格式化文件更灵活,功能更强大。XML format files provide a flexible and powerful alternative to non-XML format files. 有关非 XML 格式化文件的信息,请参阅 非 XML 格式化文件 (SQL Server)For information about non-XML format files, see Non-XML Format Files (SQL Server).

本主题内容:In This Topic:

XML 格式化文件的优点Benefits of XML Format Files

  • XML 格式化文件是自描述型,因此易于阅读、 创建和扩展。XML format files are self-describing, making them easy to read, create, and extend. 这些文件具有较强的可读性,这使您更容易理解在大容量操作过程中是如何解释数据的。They are human readable, making it easy to understand how data is interpreted during bulk operations.

  • XML 格式化文件包含目标列的数据类型。XML format files contain the data types of target columns. XML 编码清晰地描述了数据文件的数据类型和数据元素以及数据元素和表列之间的映射。The XML encoding clearly describes the data types and data elements of the data file and also the mapping between data elements and table columns.

    这可以将数据文件中数据的表示形式与文件中每个字段相关联的数据类型分离开来。This enables separation between how data is represented in the data file and what data type is associated with each field in the file. 例如,如果数据文件包含字符表示形式的数据,则相应的 SQL 列类型会丢失。For example, if a data file contains a character representation of the data, the corresponding SQL column type is lost.

  • XML 格式化文件允许从数据文件加载包含单一大型对象 (LOB) 数据类型的字段。An XML format file allows for loading of a field that contains a single large object (LOB) data type from a data file.

  • XML 格式化文件得以增强的同时,仍与早期版本保持兼容。An XML format file can be enhanced yet remain compatible with its earlier versions. 此外,XML 编码清晰,有利于为给定数据文件创建多个格式化文件。Furthermore, the clarity of XML encoding facilitates the creation of multiple format files for a given data file. 这有助于将所有或某些数据字段映射到不同表或视图中的列。This is useful if you have to map all or some of the data fields to columns in different tables or views.

  • XML 语法与操作的方向无关;即大容量导出和大容量导入中使用的语法相同。The XML syntax is independent of the direction of the operation; that is, the syntax is the same for bulk export and bulk import.

  • 使用 XML 格式化文件,可以将数据大容量导入表或非分区视图,并可以大容量导出数据。You can use XML format files to bulk import data into tables or non-partitioned views and to bulk export data.

  • 对于 OPENROWSET(BULK...) 函数,指定目标表是可选的。For the OPENROWSET(BULK...) function specifying a target table is optional. 这是因为该函数依赖 XML 格式化文件从数据文件中读取数据。This is because the function relies on the XML format file to read data from a data file.

    备注

    bcp 命令和 BULK INSERT 语句需要使用目标表,因为它们使用目标表列执行类型转换。A target table is necessary with the bcp command and the BULK INSERT statement, which uses the target table columns to do the type conversion.

XML 格式化文件的结构Structure of XML Format Files

和非 XML 格式化文件一样,XML 格式化文件定义数据文件中数据字段的格式和结构,并将这些数据字段映射到单个目标表中的相应列。Like a non-XML format file, an XML format file defines the format and structure of the data fields in a data file and maps those data fields to columns in a single target table.

XML 格式化文件有两个主要组件,即 <RECORD> 和 <ROW>:An XML format file possesses two main components, <RECORD> and <ROW>:

  • <RECORD> 说明数据文件中存储的数据。<RECORD> describes the data as it is stored in the data file.

    每一个 <RECORD> 元素包含一个或多个 <FIELD> 元素。Each <RECORD> element contains a set of one or more <FIELD> elements. 这些元素与数据文件中的字段相对应。These elements correspond to fields in the data file. 基本语法如下:The basic syntax is as follows:

    <RECORD><RECORD>

    <FIELD .../> [ ...n ]<FIELD .../> [ ...n ]

    </RECORD></RECORD>

    每个 <FIELD> 元素说明特定数据字段的内容。Each <FIELD> element describes the contents of a specific data field. 一个字段只能映射到表中的一列,A field can only be mapped to one column in the table. 并不是所有字段都需要映射到列。Not all fields need to be mapped to columns.

    数据文件中字段的长度可以是固定或可变的,也可以由字符结尾。A field in a data file can be either of fixed/variable length or character terminated. 字段值 可以表示为字符(使用单字节表示形式)、宽字符(使用 Unicode 双字节表示形式)、本机数据库格式或文件名。A field value can be represented as: a character (using single-byte representation), a wide character (using Unicode two-byte representation), native database format, or a file name. 如果字段值为文件名,则文件名指向包含目标表中 BLOB 列的值的文件。If a field value is represented as a file name, the file name points to the file that contains the value of a BLOB column in the target table.

  • <ROW> 说明在将数据从文件导入 SQL ServerSQL Server 表中时,如何构造数据文件中的数据行。<ROW> describes how to construct data rows from a data file when the data from the file is imported into a SQL ServerSQL Server table.

    <ROW> 元素包含一组 <COLUMN> 元素。A <ROW> element contains a set of <COLUMN> elements. 这些元素与表列相对应。These elements correspond to table columns. 基本语法如下:The basic syntax is as follows:

    <ROW><ROW>

    <COLUMN .../> [ ...n ]<COLUMN .../> [ ...n ]

    </ROW></ROW>

    每个 <COLUMN> 元素均只能映射到数据文件中的一个字段。Each <COLUMN> element can be mapped to only one field in the data file. <ROW> 元素中 <COLUMN> 元素的顺序定义了其在批量操作中返回的顺序。The order of the <COLUMN> elements in the <ROW> element defines the order in which they are returned by the bulk operation. XML 格式化文件为每个 <COLUMN> 元素分配了一个本地名称,该名称与批量导入操作的目标表中的列没有关系。The XML format file assigns each <COLUMN> element a local name that has no relationship to the column in the target table of a bulk import operation.

XML 格式化文件的架构语法Schema Syntax for XML Format Files

本节概要介绍 XML 格式化文件的 XML 架构的元素和属性。This section contains a summary of the elements and attributes of the XML schema for 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. 本节还介绍大容量导入如何使用 <ROW> 和 <COLUMN> 元素以及如何将元素的 xsi:type 值放入数据集。This section also considers how bulk import uses the <ROW> and <COLUMN> elements and how to put the xsi:type value of an element into a data set.

若要查看该语法与实际的 XML 格式化文件的对应关系,请参阅本主题后面的 XML 格式化文件示例To see how the syntax corresponds to actual XML format files, see Sample XML Format Files, later in this topic.

备注

您可以修改格式化文件,以便从字段编号和/或顺序与表列的编号和/或顺序不同的数据文件进行大容量导入。You can modify a format file to let you bulk import from a data file in which the number and/or order of the fields differ from the number and/or order of table columns. 有关详细信息,请参阅 用来导入或导出数据的格式化文件 (SQL Server)For more information, see Format Files for Importing or Exporting Data (SQL Server).

本节内容:In This Section:

XML 架构的基本语法Basic Syntax of the XML Schema

此语法语句仅显示元素(<BCPFORMAT>、<RECORD>、<FIELD>、<ROW> 和 <COLUMN>)及其基本属性。This syntax statements show only the elements (<BCPFORMAT>, <RECORD>, <FIELD>, <ROW>, and <COLUMN>) and their basic attributes.

<BCPFORMAT ...><BCPFORMAT ...>

<RECORD><RECORD>

<FIELD ID = "fieldID" xsi:type = "fieldType" [...]<FIELD ID = "fieldID" xsi:type = "fieldType" [...]

/>

</RECORD></RECORD>

<ROW><ROW>

<COLUMN SOURCE = "fieldID" NAME = "columnName" xsi:type = "columnType" [...]<COLUMN SOURCE = "fieldID" NAME = "columnName" xsi:type = "columnType" [...]

/>

</ROW></ROW>

</BCPFORMAT></BCPFORMAT>

备注

对于其他与 <FIELD> 或 <COLUMN> 元素中的 xsi:type 值相关的属性,将在本主题后面予以介绍。Additional attributes that are associated with the value of the xsi:type in a <FIELD> or <COLUMN> element are described later in this topic.

本节内容:In This Section:

架构元素Schema Elements

本节总结了 XML 架构为 XML 格式化文件定义的每个元素的作用。This section summarizes the purpose of each element that the XML schema defines for XML format files. 本主题在后面有单独的章节介绍这些属性。The attributes are described in separate sections later in this topic.

<BCPFORMAT><BCPFORMAT>
即格式化文件元素。它定义给定数据文件的记录结构及其与表中某行的各列的对应关系。Is the format-file element that defines the record structure of a given data file and its correspondence to the columns of a table row in the table.

<RECORD .../><RECORD .../>
定义包含一个或多个 <FIELD> 元素的复杂元素。Defines a complex element containing one or more <FIELD> elements. 在格式化文件中声明的字段的顺序与那些字段在数据文件中出现的顺序相同。The order in which the fields are declared in the format file is the order in which those fields appear in the data file.

<FIELD .../><FIELD .../>
定义数据文件中的字段,用来容纳数据。Defines a field in data file, which contains data.

本主题将在后面的 <FIELD> 元素的属性中讨论此元素的属性。The attributes of this element are discussed in Attributes of the <FIELD> Element, later in this topic.

<ROW .../><ROW .../>
定义包含一个或多个 <COLUMN> 元素的复杂元素。Defines a complex element containing one or more <COLUMN> elements. <COLUMN> 元素的顺序与 RECORD 定义中的 <FIELD> 元素的顺序无关。The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a RECORD definition. 但是,<COLUMN> 元素在格式化文件中的顺序决定了结果行集的列的顺序。Rather, the order of the <COLUMN> elements in a format file determines the column order of the resultant rowset. 数据字段的加载将按照相应的 <COLUMN> 元素在 <COLUMN> 元素中的声明顺序进行。Data fields are loaded in the order in which the corresponding <COLUMN> elements are declared in the <COLUMN> element.

有关详细信息,请参阅后面本主题中的大容量导入如何使用 <ROW> 元素For more information, see How Bulk Import Uses the <ROW> Element, later in this topic.

<COLUMN><COLUMN>
将列定义为元素 (<COLUMN>)。Defines a column as an element (<COLUMN>). 每个 <COLUMN> 元素对应一个 <FIELD> 元素(其 ID 在 <COLUMN> 元素的 SOURCE 属性中指定)。Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element).

本主题将在后面的 <COLUMN> 元素的属性中讨论此元素的属性。The attributes of this element are discussed in Attributes of the <COLUMN> Element, later in this topic. 另请参阅后面本主题中的大容量导入如何使用 <COLUMN> 元素Also see, How Bulk Import Uses the <COLUMN> Element, later in this topic.

</BCPFORMAT></BCPFORMAT>
用于结束格式化文件。Required to end the format file.

<FIELD> 元素的属性Attributes of the <FIELD> Element

本节介绍 <FIELD> 元素的属性,现将其架构语法总结如下:This section describes the attributes of the <FIELD> element, which are summarized in the following schema syntax:

<FIELD<FIELD

ID ="fieldID"ID ="fieldID"

xsi : type ="fieldType"xsi : type ="fieldType"

[ LENGTH ="n" ][ LENGTH ="n" ]

[ PREFIX_LENGTH ="p" ][ PREFIX_LENGTH ="p" ]

[ MAX_LENGTH ="m" ][ MAX_LENGTH ="m" ]

[ COLLATION ="collationName" ][ COLLATION ="collationName" ]

[ TERMINATOR ="terminator" ][ TERMINATOR ="terminator" ]

/>

每个 <FIELD> 元素都与其他元素无关。Each <FIELD> element is independent of the others. 字段是通过下列属性进行描述的:A field is described in terms of the following attributes:

FIELD 属性FIELD Attribute 说明Description 可选/Optional /

RequiredRequired
ID ="fieldID"ID ="fieldID" 指定数据文件中的字段的逻辑名称。Specifies the logical name of the field in the data file. 字段的 ID 是用于引用字段的键。The ID of a field is the key used to refer to the field.

<FIELD ID ="fieldID"/> maps to <COLUMN SOURCE ="fieldID"/><FIELD ID ="fieldID"/> maps to <COLUMN SOURCE ="fieldID"/>
RequiredRequired
xsi:type ="fieldType"xsi:type ="fieldType" 这是一个 XML 构造,用法类似于属性。它定义元素实例的类型。This is an XML construct (used like an attribute) that identifies the type of the instance of the element. fieldType 的值决定了给定实例中需要下面哪个可选属性。The value of fieldType determines which of the optional attributes (below) you need in a given instance. 必需(取决于数据类型)Required (depending on the data type)
LENGTH ="n"LENGTH ="n" 此属性定义固定长度的数据类型实例的长度。This attribute defines the length for an instance of a fixed-length data type.

n 值必须是正整数。The value of n must be a positive integer.
除非是 xsi:type 值所必需,否则可选。Optional unless required by the xsi:type value
PREFIX_LENGTH ="p"PREFIX_LENGTH ="p" 此属性定义二进制数据表示形式的前缀的长度。This attribute defines the prefix length for a binary data representation. PREFIX_LENGTH 值 p 必须是下列值之一:1、2、4 或 8。The PREFIX_LENGTH value, p, must be one of the following: 1, 2, 4, or 8. 除非是 xsi:type 值所必需,否则可选。Optional unless required by the xsi:type value
MAX_LENGTH ="m"MAX_LENGTH ="m" 此属性为给定字段中可以存储的最大字节数。This attribute is the maximum number of bytes that can be stored in a given field. 如果没有目标表,列的最大长度就是未知的。Without a target table, the column max-length is not known. MAX_LENGTH 属性限定输出字符列的最大长度,从而限制为列值分配的存储空间。The MAX_LENGTH attribute restricts the maximum length of an output character column, limiting the storage allocated for the column value. 当在 SELECT FROM 子句中使用了 OPENROWSET 函数的 BULK 选项时,使用该属性将带来极大的方便。This is especially convenient when using the OPENROWSET function's BULK option in a SELECT FROM clause.

m 值必须是正整数。The value of m must be a positive integer. 默认情况下, char 列的最大长度为 8000 个字符, nchar 列的最大长度为 4000 个字符。By default, the maximum length is 8000 characters for a char column and 4000 characters for an nchar column.
可选Optional
COLLATION ="collationName"COLLATION ="collationName" COLLATION 仅适用于字符字段。COLLATION is only allowed for character fields. 有关 SQL 排序规则名称的列表,请参阅 SQL Server 排序规则名称 (Transact SQL)For a list of the SQL collation names, see SQL Server Collation Name (Transact-SQL). 可选Optional
TERMINATOR = "terminator"TERMINATOR = "terminator" 此属性指定数据字段的终止符。This attribute specifies the terminator of a data field. 该终止符可以是任意字符。The terminator can be any character. 该字符必须是数据中没有的唯一字符。The terminator must be a unique character that is not part of the data.

默认情况下,该字段的终止符为制表符(用 \t 表示)。By default, the field terminator is the tab character (represented as \t). 若要表示段落标记,请使用 \r\n。To represent a paragraph mark, use \r\n.
仅和需要该属性的字符数据 xsi:type 一起使用。Used only with an xsi:type of character data, which requires this attribute
<FIELD> 元素的 Xsi:type 值Xsi:type values of the <FIELD> Element

xsi:type 值是标识元素实例的数据类型的 XML 构造(用法同属性)。The xsi:type value is an XML construct (used like an attribute) that identifies the data type of an instance of an element. 本节后面将介绍有关“在数据集中包含 xsi:type 值”的信息。For information on using the "Putting the xsi:type Value into a Data Set," later in this section.

<FIELD> 元素的 xsi:type 值支持下列数据类型。The xsi:type value of the <FIELD> element supports the following data types.

<FIELD> 的 xsi:type 值<FIELD> xsi:type values 数据类型Required XML Attribute(s)

的可选 XML 属性for Data Type
数据类型Optional XML Attribute(s)

的可选 XML 属性for Data Type
NativeFixedNativeFixed LENGTHLENGTH 无。None.
NativePrefixNativePrefix PREFIX_LENGTHPREFIX_LENGTH MAX_LENGTHMAX_LENGTH
CharFixedCharFixed LENGTHLENGTH COLLATIONCOLLATION
NCharFixedNCharFixed LENGTHLENGTH COLLATIONCOLLATION
CharPrefixCharPrefix PREFIX_LENGTHPREFIX_LENGTH MAX_LENGTH、COLLATIONMAX_LENGTH, COLLATION
NCharPrefixNCharPrefix PREFIX_LENGTHPREFIX_LENGTH MAX_LENGTH、COLLATIONMAX_LENGTH, COLLATION
CharTermCharTerm TERMINATORTERMINATOR MAX_LENGTH、COLLATIONMAX_LENGTH, COLLATION
NCharTermNCharTerm TERMINATORTERMINATOR MAX_LENGTH、COLLATIONMAX_LENGTH, COLLATION

有关值数据类型 MicrosoftMicrosoft SQL ServerSQL Server 的详细信息,请参阅 数据类型 (Transact-SQL)For more information about MicrosoftMicrosoft SQL ServerSQL Server data types, see Data Types (Transact-SQL).

<COLUMN> 元素的属性Attributes of the <COLUMN> Element

本节介绍 <COLUMN> 元素的属性,现将其架构语法总结如下:This section describes the attributes of the <COLUMN> element, which are summarized in the following schema syntax:

<COLUMN<COLUMN

SOURCE = "fieldID"SOURCE = "fieldID"

NAME = "columnName"NAME = "columnName"

xsi:type = "columnType"xsi:type = "columnType"

[ LENGTH = "n" ][ LENGTH = "n" ]

[ PRECISION = "n" ][ PRECISION = "n" ]

[ SCALE = "value" ][ SCALE = "value" ]

[ NULLABLE = { "YES"[ NULLABLE = { "YES"

"NO" } ]"NO" } ]

/>

使用下列属性将字段映射到目标表中的列:A field is mapped to a column in the target table using the following attributes:

COLUMN 属性COLUMN Attribute 描述Description 可选/Optional /

RequiredRequired
SOURCE ="fieldID"SOURCE ="fieldID" 指定映射到列的字段 ID。Specifies the ID of the field being mapped to the column.

<COLUMN SOURCE ="fieldID"/> maps to <FIELD ID ="fieldID"/><COLUMN SOURCE ="fieldID"/> maps to <FIELD ID ="fieldID"/>
RequiredRequired
NAME = "columnName"NAME = "columnName" 指定格式化文件所表示的行集中的列名。Specifies the name of the column in the row set represented by the format file. 此列名用于标识结果集中的列,并且该列不需要与目标表中使用的列名相对应。This column name is used to identify the column in the result set, and it need not correspond to the column name used in the target table. RequiredRequired
xsi : type ="ColumnType"xsi : type ="ColumnType" 这是一个 XML 构造,用法类似于属性。它定义元素实例的数据类型。This is an XML construct (used like an attribute) that identifies the data type of the instance of the element. ColumnType 的值决定了给定实例中需要下面哪个可选属性。The value of ColumnType determines which of the optional attributes (below) you need in a given instance.

注意:<COLUMN> 元素的 Xsi:type 值部分中的 <COLUMN< 元素表列出了 ColumnType 的可能值及其相关属性。Note: The possible values of ColumnType and their associated attributes are listed in the <COLUMN> element table in the Xsi:type values of the <COLUMN> Element section.
可选Optional
LENGTH ="n"LENGTH ="n" 定义固定长度的数据类型实例的长度。Defines the length for an instance of a fixed-length data type. 仅当 xsi:type 为字符串数据类型时,才使用 LENGTH。LENGTH is used only when the xsi:type is a string data type.

n 值必须是正整数。The value of n must be a positive integer.
可选(仅当 xsi:type 是字符串数据类型时才可用)Optional (available only if the xsi:type is a string data type)
PRECISION ="n"PRECISION ="n" 指示数字的位数。Indicates the number of digits in a number. 例如,数 123.45 精度为 5。For example, the number 123.45 has a precision of 5.

该值必须是正整数。The value must be a positive integer.
可选(仅在 xsi:type 是变量数字数据类型时才可用)Optional (available only if the xsi:type is a variable-number data type)
SCALE ="int"SCALE ="int" 指示数字中小数点右边的位数。Indicates the number of digits to the right of the decimal point in a number. 例如,数字 123.45 的小数位数为 2。For example, the number 123.45 has a scale of 2.

该值必须为整数。The value must be an integer.
可选(仅在 xsi:type 是变量数字数据类型时才可用)Optional (available only if the xsi:type is a variable-number data type)
NULLABLE = { " YES "NULLABLE = { " YES "

" NO " }" NO " }
指示列是否可以接受 NULL 值。Indicates whether a column can assume NULL values. 此属性与 FIELDS 完全无关。This attribute is completely independent of FIELDS. 但是,如果列不可为空值,而字段指定为 NULL(未指定任何值),将产生运行时错误。However, if a column is not NULLABLE and field specifies NULL (by not specifying any value), a run-time error results.

NULLABLE 属性仅在您只执行普通 SELECT FROM OPENROWSET(BULK...) 语句时才使用。The NULLABLE attribute is used only if you do a plain SELECT FROM OPENROWSET(BULK...) statement.
可选(任何数据类型均可用)Optional (available for any data type)
<COLUMN> 元素的 Xsi:type 值Xsi:type values of the <COLUMN> Element

xsi:type 值是标识元素实例的数据类型的 XML 构造(用法同属性)。The xsi:type value is an XML construct (used like an attribute) that identifies the data type of an instance of an element. 本节后面将介绍有关“在数据集中包含 xsi:type 值”的信息。For information on using the "Putting the xsi:type Value into a Data Set," later in this section.

<COLUMN> 元素支持下列本机 SQL 数据类型:The <COLUMN> element supports native SQL data types, as follows:

类型类别Type Category <COLUMN> 数据类型<COLUMN> Data Types 数据类型Required XML Attribute(s)

的可选 XML 属性for Data Type
数据类型Optional XML Attribute(s)

的可选 XML 属性for Data Type
固定Fixed SQLBITSQLTINYINTSQLSMALLINTSQLINTSQLBIGINTSQLFLT4SQLFLT8SQLDATETIMESQLDATETIM4SQLDATETIM8SQLMONEYSQLMONEY4SQLVARIANTSQLUNIQUEIDSQLBIT, SQLTINYINT, SQLSMALLINT, SQLINT, SQLBIGINT, SQLFLT4, SQLFLT8, SQLDATETIME, SQLDATETIM4, SQLDATETIM8, SQLMONEY, SQLMONEY4, SQLVARIANT, and SQLUNIQUEID 无。None. NULLABLENULLABLE
变量号Variable Number SQLDECIMALSQLNUMERICSQLDECIMAL and SQLNUMERIC 无。None. NULLABLE、PRECISION、SCALENULLABLE, PRECISION, SCALE
LOBLOB SQLIMAGECharLOBSQLTEXTSQLUDTSQLIMAGE, CharLOB, SQLTEXT, and SQLUDT 无。None. NULLABLENULLABLE
字符 LOBCharacter LOB SQLNTEXTSQLNTEXT 无。None. NULLABLENULLABLE
二进制字符串Binary string SQLBINARYSQLVARYBINSQLBINARY and SQLVARYBIN 无。None. NULLABLE、LENGTHNULLABLE, LENGTH
字符串Character string SQLCHARSQLVARYCHARSQLNCHARSQLNVARCHARSQLCHAR, SQLVARYCHAR, SQLNCHAR, and SQLNVARCHAR 无。None. NULLABLE、LENGTHNULLABLE, LENGTH

重要

若要大容量导出或导入 SQLXML 数据,请在格式化文件中使用下列数据类型之一:SQLCHAR 或 SQLVARYCHAR(数据以客户端代码页或排序规则隐含的代码页的形式发送)、SQLNCHAR 或 SQLNVARCHAR(数据以 Unicode 的形式发送)或者 SQLBINARY 或 SQLVARYBIN(数据不经任何转换直接发送)。To bulk export or import SQLXML data, use one of the following data types in your format file: SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation), SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode), or SQLBINARY or SQLVARYBIN (the data is sent without any conversion).

有关值数据类型 SQL ServerSQL Server 的详细信息,请参阅 数据类型 (Transact-SQL)For more information about SQL ServerSQL Server data types, see Data Types (Transact-SQL).

大容量导入如何使用 <ROW> 元素How Bulk Import Uses the <ROW> Element

在某些上下文中可以忽略 <ROW> 元素。The <ROW> element is ignored in some contexts. <ROW> 元素是否影响大容量导入操作取决于操作的执行方式:Whether the <ROW> element affects a bulk-import operation depends on how the operation is performed:

  • bcp 命令the bcp command

    在目标表加载数据时,bcp 忽略 <ROW> 组件。When data is loaded into a target table, bcp ignores the <ROW> component. 相反, bcp 根据目标表的列类型来加载数据。Instead, bcp loads the data based on the column types of the target table.

  • Transact-SQLTransact-SQL 语句(BULK INSERT 和 OPENROWSET 的大容量行集访问接口)statements (BULK INSERT and OPENROWSET's Bulk rowset provider)

    在将数据大容量地导入表中时,Transact-SQLTransact-SQL 语句将使用 <ROW> 组件来生成输入行集。When bulk importing data into a table, Transact-SQLTransact-SQL statements use the <ROW> component to generate the input rowset. Transact-SQLTransact-SQL 语句还将根据 <ROW> 下指定的列类型和目标表中的对应列,进行适当的类型转换。Also, Transact-SQLTransact-SQL statements perform appropriate type conversions based on the column types specified under <ROW> and the corresponding column in the target table. 如果格式化文件和目标表中指定的列类型之间存在不匹配,还将进行额外的类型转换。If a mismatch exists between column types as specified in the format file and in the target table, an extra type conversion occurs. bcp相比,此额外的类型转换可能引起 BULK INSERT 或 OPENROWSET 的 BULK 行集提供程序中的行为出现某些差异(即损失精度)。This extra type conversion may lead to some discrepancy (that is, a loss of precision) in behavior in BULK INSERT or OPENROWSET's Bulk rowset provider as compared to bcp.

    无需任何其他信息,仅利用 <ROW> 元素中的信息即可构造行。The information in the <ROW> element allows a row to be constructed without requiring any additional information. 因此,可以使用 SELECT 语句 (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile) 来生成行集。For this reason, you can generate a rowset using a SELECT statement (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile).

    备注

    OPENROWSET BULK 子句需要格式化文件(请注意,将字段的数据类型转换为列的数据类型只能使用 XML 格式化文件进行)。The OPENROWSET BULK clause requires a format file (note that converting from the data type of the field to the data type of a column is available only with an XML format file).

大容量导入如何使用 <COLUMN> 元素How Bulk Import Uses the <COLUMN> Element

为了将数据大容量地导入表中,格式化文件中的 <COLUMN> 元素将数据文件字段映射到表列,方法是指定:For bulk importing data into a table, the <COLUMN> elements in a format file map a data-file field to table columns by specifying:

  • 行中每个字段在数据文件中的位置。The position of each field within a row in the data file.

  • 列类型,用于将字段数据类型转换为所需的列数据类型。The column type, which is used to convert the field data type to the desired column data type.

如果没有列映射到某个字段,该字段将不会被复制到生成的行。If no column is mapped to a field, the field is not copied into the generated row(s). 此行为使得数据文件能够在不同的表中生成含有不同列的行。This behavior allows a data file to generate rows with different columns (in different tables).

与此类似,为了将数据从表中大容量地导出,格式化文件中的各个 <COLUMN> 将输入表行的列映射到输出数据文件中与之对应的字段。Similarly, for bulk exporting data from a table, each <COLUMN> in the format file maps the column from the input table row to its corresponding field in the output data file.

将 xsi:type 值放入数据集Putting the xsi:type Value into a Data Set

当通过 XML 架构定义 (XSD) 语言验证 XML 文档时,xsi:type 值不放入数据集。When an XML document is validated through the XML Schema Definition (XSD) language, the xsi:type value is not put into the data set. 但是,通过将 XML 格式化文件加载到 XML 文档(如 myDoc)中,可以将 xsi:type 信息放入数据集。如下列代码段所示:However, you can put the xsi:type information into the data set by loading the XML format file into an XML document (for example, myDoc), as illustrated in the following code snippet:

...;  
myDoc.LoadXml(xmlFormat);  
XmlNodeList ColumnList = myDoc.GetElementsByTagName("COLUMN");  
for(int i=0;i<ColumnList.Count;i++)  
{  
   Console.Write("COLUMN: xsi:type=" +ColumnList[i].Attributes["type",  
      "http://www.w3.org/2001/XMLSchema-instance"].Value+"\n");  
}  

XML 格式化文件示例Sample XML Format Files

本节包含在各种情况下使用 XML 格式化文件的信息,并提供了一个 Adventure WorksAdventure Works 示例。This section contains information on using XML format files in a variety of cases, including an Adventure WorksAdventure Works example.

备注

在下列示例所示的数据文件中, <tab> 表示数据文件中的一个制表符, <return> 表示一个回车符。In the data files shown in the following examples, <tab> indicates a tab character in a data file, and <return> indicates a carriage return.

这些示例说明了使用 XML 格式化文件的各个主要方面,如下所述:The examples illustrate key aspects of using XML format files, as follows:

备注

有关创建格式化文件的信息,请参阅 创建格式化文件 (SQL Server)For information about how to create format files, see Create a Format File (SQL Server).

A.A. 对字符数据字段和表列进行相同的排序Ordering character-data fields the same as table columns

下面的示例显示了一个 XML 格式化文件,该文件描述一个包含三个字符数据字段的数据文件。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.

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

数据文件(记录): Age<tab>Firstname<tab>Lastname<return>Data file (record): Age<tab>Firstname<tab>Lastname<return>

以下 XML 格式化文件从数据文件读取数据到表中。The following XML format file reads from the data file to the table.

<RECORD> 元素中,格式化文件将所有三个字段中的数据值表示为字符数据。In the <RECORD> element, the format file represents the data values in all three fields as character data. 对于每个字段, TERMINATOR 属性指示位于数据值后面的终止符。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. <ROW> 元素中,格式化文件将 Age 列映射到第一个字段,将 FirstName 列映射到第二个字段,将 LastName 列映射到第三个字段。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>  

备注

有关等效的 AdventureWorks2012AdventureWorks2012 示例,请参阅 创建格式化文件 (SQL Server)For an equivalent AdventureWorks2012AdventureWorks2012 example, see Create a Format File (SQL Server).

B.B. 对数据字段和表列进行不同的排序Ordering data fields and table columns differently

下面的示例显示了一个 XML 格式化文件,该文件描述一个包含三个字符数据字段的数据文件。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.

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

数据文件(记录):Age<tab>Lastname<tab>Firstname<return>Data file (record): Age<tab>Lastname<tab>Firstname<return>

<RECORD> 元素中,格式化文件将所有三个字段中的数据值表示为字符数据。In the <RECORD> element, the format file represents the data values in all three fields as character data.

<ROW> 元素中,格式化文件将 Age 列映射到第一个字段,将 FirstName 列映射到第三个字段,将 LastName 列映射到第二个字段。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>  

备注

有关等效的 AdventureWorks2012AdventureWorks2012 示例,请参阅 使用格式化文件将表列映射到数据文件字段 (SQL Server)For an equivalent AdventureWorks2012AdventureWorks2012 example, see Use a Format File to Map Table Columns to Data-File Fields (SQL Server).

C.C. 省略数据字段Omitting a data field

下面的示例显示了一个 XML 格式化文件,该文件描述一个包含四个字符数据字段的数据文件。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.

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

数据文件(记录): Age<tab>employeeID<tab>Firstname<tab>Lastname<return>Data file (record): Age<tab>employeeID<tab>Firstname<tab>Lastname<return>

<RECORD> 元素中,格式化文件将所有四个字段中的数据值表示为字符数据。In the <RECORD> element, the format file represents the data values in all four fields as character data. 对于每个字段,TERMINATOR 属性指示位于数据值后面的终止符。For each field, the TERMINATOR attribute indicates the terminator that follows the data value.

<ROW> 元素中,格式化文件将 Age 列映射到第一个字段,将 FirstName 列映射到第三个字段,将 LastName 列映射到第四个字段。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>  

备注

有关等效的 AdventureWorks2012AdventureWorks2012 示例,请参阅 使用格式化文件跳过数据字段 (SQL Server)For an equivalent AdventureWorks2012AdventureWorks2012 example, see Use a Format File to Skip a Data Field (SQL Server).

D.D. 将 <FIELD> xsi:type 映射到 <COLUMN> xsi:typeMapping <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.E. 将 XML 数据映射到表Mapping XML data to a table

下面的示例创建了一个空的两列表 (t_xml),表中的第一列映射到 int 数据类型,第二列映射到 xml 数据类型。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)  

以下 XML 格式化文件将数据文件加载到表 t_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.F. 导入固定长度或固定宽度的字段Importing fixed-length or fixed-width fields

下面的示例分别介绍包含 10 个或 6 个字符的固定字段。The following example describes fixed fields of 10 or 6 characters each. 格式化文件将这些字段的长度/宽度分别表示为 LENGTH="10"LENGTH="6"The format file represents these field lengths/widths as LENGTH="10" and LENGTH="6", respectively. 数据文件中的每行都以回车符-换行符组合 {CR}{LF} 结束,格式化文件将这表示为 TERMINATOR="\r\n"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

有关非 XML 格式化文件和 XML 格式化文件的其他示例,请参阅下列主题:For additional examples of both non-XML format files and XML format files, see the following topics:

相关任务Related Tasks

相关内容Related Content

无。None.

另请参阅See Also

批量导入和导出数据 (SQL Server) Bulk Import and Export of Data (SQL Server)
数据类型 (Transact-SQL) Data Types (Transact-SQL)
非 XML 格式化文件 (SQL Server) Non-XML Format Files (SQL Server)
用来导入或导出数据的格式化文件 (SQL Server)Format Files for Importing or Exporting Data (SQL Server)