Schema Syntax for XML Format Files

Artikkeli
12/03/2008

This section describes the syntax of XML format files. To see how the syntax corresponds to actual XML format files, see Sample XML Format Files. 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.

You can use an XML format file with a bcp command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement.

Note

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. For more information, see Using a Format File to Map Fields to Columns During Bulk Import.

Basic Syntax of the XML Schema

This syntax statements show only the elements (<BCPFORMAT>, <RECORD>, <FIELD>, <ROW>, and <COLUMN>) and their basic attributes.

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

</RECORD>

<ROW>

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

</ROW>

</BCPFORMAT>

Note

Additional attributes that are associated with the value of the xsi:type in a <FIELD> or <COLUMN> element are described later in this topic.

Description of the Schema Elements

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>
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 .../>
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 .../>
Defines a field in data file, which contains data.

The attributes of this element are discussed in "Attributes of the <FIELD> Element," later in this topic.

<ROW .../>
Defines a complex element containing one or more <COLUMN> elements. The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a RECORD definition. Rather, the order of the <COLUMN> elements in a format file determines the column order of the resultant rowset. Data fields are loaded in the order in which the corresponding <COLUMN> elements are declared in the <COLUMN> element.

For more information, see "How Bulk Import Uses the <ROW> Element," later in this topic.

<COLUMN>
Defines a column as an element (<COLUMN>). Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element).

The attributes of this element are discussed in "Attributes of the <COLUMN> Element," later in this topic. Also see, "How Bulk Import Uses the <COLUMN> Element," later in this topic.

</BCPFORMAT>
Required to end the format file.

Attributes of the <FIELD> Element

This section describes the attributes of the <FIELD> element, which are summarized in the following schema syntax:

<FIELD

ID ="fieldID"

xsi**:**type ="fieldType"

[ LENGTH ="n" ]

[ PREFIX_LENGTH ="p" ]

[ MAX_LENGTH ="m" ]

[ COLLATION ="collationName" ]

[ TERMINATOR ="terminator" ]

Each <FIELD> element is independent of the others. A field is described in terms of the following attributes:

FIELD Attribute	Description	Optional / Required
ID ="fieldID"	Specifies the logical name of the field in the data file. The ID of a field is the key used to refer to the field. <FIELD ID="fieldID"/> maps to <COLUMN SOURCE="fieldID"/>	Required
xsi:type ="fieldType"	This is an XML construct (used like an attribute) that identifies the type of the instance of the element. 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"	This attribute defines the length for an instance of a fixed-length data type. The value of n must be a positive integer.	Optional unless required by the xsi:type value
PREFIX_LENGTH ="p"	This attribute defines the prefix length for a binary data representation. The PREFIX_LENGTH value, p, must be one of the following: 1, 2, 4, or 8.	Optional unless required by the xsi:type value
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. The MAX_LENGTH attribute restricts the maximum length of an output character column, limiting the storage allocated for the column value. This is especially convenient when using the OPENROWSET function's BULK option in a SELECT FROM clause. The value of m must be a positive integer. By default, the maximum length is 8000 characters for a char column and 4000 characters for an nchar column.	Optional
COLLATION ="collationName"	COLLATION is only allowed for character fields. For a list of the SQL collation names, see SQL Collation Name (Transact-SQL).	Optional
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. By default, the field terminator is the tab character (represented as \t). To represent a paragraph mark, use \r\n.	Used only with an xsi:type of character data, which requires this attribute

Xsi:type values of the <FIELD> Element

The xsi:type value is an XML construct (used like an attribute) that identifies the data type of an instance of an element. For information on using the "Putting the xsi:type Value into a Data Set," later in this section.

The xsi:type value of the <FIELD> element supports the following data types.

<FIELD> xsi:type values	Required XML Attribute(s) for Data Type	Optional XML Attribute(s) for Data Type
NativeFixed	LENGTH	None.
NativePrefix	PREFIX_LENGTH	MAX_LENGTH
CharFixed	LENGTH	COLLATION
NCharFixed	LENGTH	COLLATION
CharPrefix	PREFIX_LENGTH	MAX_LENGTH, COLLATION
NCharPrefix	PREFIX_LENGTH	MAX_LENGTH, COLLATION
CharTerm	TERMINATOR	MAX_LENGTH, COLLATION
NCharTerm	TERMINATOR	MAX_LENGTH, COLLATION

For more information about Microsoft SQL Server data types, see Data Types (Transact-SQL).

Attributes of the <COLUMN> Element

This section describes the attributes of the <COLUMN> element, which are summarized in the following schema syntax:

<COLUMN

SOURCE = "fieldID"

NAME = "columnName"

xsi:type = "columnType"

[ LENGTH = "n" ]

[ PRECISION = "n" ]

[ SCALE = "value" ]

[ NULLABLE = { "YES"

"NO" } ]

A field is mapped to a column in the target table using the following attributes:

COLUMN Attribute

Description

Optional / Required

SOURCE ="fieldID"

Specifies the ID of the field being mapped to the column.

Required

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.

Required

xsi**:**type ="ColumnType"

This is an XML construct (used like an attribute) that identifies the data type of the instance of the element. The value of ColumnType determines which of the optional attributes (below) you need in a given instance.

ms189327.note(en-US,SQL.90).gif Note:

The possible values of ColumnType and their associated attributes are listed in the next table.

Optional

LENGTH ="n"

Defines the length for an instance of a fixed-length data type. LENGTH is used only when the xsi:type is a string data type.

The value of n must be a positive integer.

Optional (available only if the xsi:type is a string data type)

PRECISION ="n"

Indicates the number of digits in a number. For example, the number 123.45 has a precision of 5.

The value must be a positive integer.

Optional (available only if the xsi:type is a variable-number data type)

SCALE ="int"

Indicates the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a scale of 2.

The value must be an integer.

Optional (available only if the xsi:type is a variable-number data type)

NULLABLE = { "YES"

"NO" }

Indicates whether a column can assume NULL values. This attribute is completely independent of FIELDS. However, if a column is not NULLABLE and field specifies NULL (by not specifying any value), a run-time error results.

Optional (available for any data type)

Xsi:type values of the <COLUMN> Element

The <COLUMN> element supports native SQL data types, as follows:

Type Category	<COLUMN> Data Types	Required XML Attribute(s) for Data Type	Optional XML Attribute(s) for Data Type
Fixed	SQLBIT, SQLTINYINT, SQLSMALLINT, SQLINT, SQLBIGINT, SQLFLT4, SQLFLT8, SQLDATETIME, SQLDATETIM4, SQLDATETIM8, SQLMONEY, SQLMONEY4, SQLVARIANT, and SQLUNIQUEID	None.	NULLABLE
Variable Number	SQLDECIMAL and SQLNUMERIC	None.	NULLABLE, PRECISION, SCALE
LOB	SQLIMAGE, CharLOB, SQLTEXT, and SQLUDT	None.	NULLABLE
Character LOB	SQLNTEXT	None.	NULLABLE
Binary string	SQLBINARY and SQLVARYBIN	None.	NULLABLE, LENGTH
Character string	SQLCHAR, SQLVARYCHAR, SQLNCHAR, and SQLNVARCHAR	None.	NULLABLE, LENGTH

Important

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).

For more information about SQL Server data types, see Data Types (Transact-SQL).

How Bulk Import Uses the <ROW> Element

Whether the <ROW> element affects a bulk-import operation depends on how the operation is performed:

the bcp command
When data is loaded into a target table, bcp ignores the <ROW> component. Instead, bcp loads the data based on the column types of the target table.
T-SQL statements (BULK INSERT and OPENROWSET's Bulk rowset provider)
When bulk importing data into a table, Transact-SQL statements use the <ROW> component to generate the input rowset. Also, Transact-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. 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.
The information in the <ROW> element allows a row to be constructed without requiring any additional information. For this reason, you can generate a rowset using a SELECT statement (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile).

Note

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).

How Bulk Import Uses the <COLUMN> Element

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).

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.

Putting the xsi:type Value into a Data Set

When an XML document is validated through the XML Schema Definition (XSD) language, the xsi:type value is not put into the data set. 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");
}

Jaa

Schema Syntax for XML Format Files

Basic Syntax of the XML Schema

Description of the Schema Elements

Attributes of the <FIELD> Element

Xsi:type values of the <FIELD> Element

Attributes of the <COLUMN> Element

Xsi:type values of the <COLUMN> Element

How Bulk Import Uses the <ROW> Element

How Bulk Import Uses the <COLUMN> Element

Putting the xsi:type Value into a Data Set

See Also

Concepts

Other Resources

Help and Information

Lisäresursseja

Jaa

Schema Syntax for XML Format Files

Basic Syntax of the XML Schema

Description of the Schema Elements

Attributes of the <FIELD> Element

Xsi:type values of the <FIELD> Element

Attributes of the <COLUMN> Element

Xsi:type values of the <COLUMN> Element

How Bulk Import Uses the &lt;ROW&gt; Element

How Bulk Import Uses the &lt;COLUMN&gt; Element

Putting the xsi:type Value into a Data Set

See Also

Concepts

Other Resources

Help and Information

Lisäresursseja

How Bulk Import Uses the <ROW> Element

How Bulk Import Uses the <COLUMN> Element