XML 格式檔案 (SQL Server)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

SQL Server 提供 XML 結構描述，其定義可撰寫 XML 格式檔案的語法，以用於將資料大量匯入 SQL Server 資料表。 XML 格式檔案必須遵守以 XML 結構描述定義語言 (XSDL) 定義的這個結構描述。 只有在同時安裝 SQL Server 工具和 SQL Server Native Client 時，才能支援 XML 格式檔案。

您可以搭配使用 XML 格式檔案與 bcp 命令、BULK INSERT 陳述式或 INSERT ... SELECT * FROM OPENROWSET(BULK...) 陳述式。 bcp 命令可讓您自動產生資料表的 XML 格式檔案；如需詳細資訊，請參閱＜ bcp Utility＞。

XML 格式檔案的優點

• XML 格式檔案具有自我描述能力，使其易於讀取、建立和擴充。 XML 格式檔案容易讀懂，以便輕鬆地了解在大量作業期間如何解譯資料。

• XML 格式檔案包含目標資料行的資料類型。 XML 編碼清楚描述資料檔的資料類型和資料元素，以及資料元素和資料表資料行之間的對應關係。

  如此可將資料檔呈現資料的方式，與檔案中每一個欄位和資料類型間的關係，予以區隔開來。 例如，資料檔若包含資料的字元表示法，就會遺失對應的 SQL 資料行類型。

• XML 格式檔案允許從資料檔載入包含單一大型物件 (LOB) 資料類型的欄位。

• XML 格式檔案雖然功能增強，但仍保持與其舊版的相容性。 此外，XML 清楚的編碼方式，有利於建立所指定的資料檔的多個格式檔案。 如果您需要將資料欄位的全部或部分對應到不同資料表或檢視表中的資料行，這會很有用。

• XML 語法與作業方向無關；也就是說，大量匯出和大量匯入作業的語法相同。

• 您可以使用 XML 格式檔案，大量匯入資料至資料表或非資料分割檢視中及大量匯出資料。

• 針對 OPENROWSET(BULK...) 函數，指定目標資料表是選擇性的。 原因是函數依賴 XML 格式檔案，才能從資料檔讀取資料。

  注意

  使用 bcp 命令和 BULK INSERT 陳述式 (使用目標資料表資料行來執行類型轉換)，需要目標資料表。

XML 格式檔案的結構

就像非 XML 格式檔案一樣，XML 格式檔案也定義資料檔中資料欄位的格式和結構，並將那些資料欄位對應到單一目標資料表中的資料行。

XML 格式檔案擁有兩個主要元件：<RECORD> 和 <ROW>：

• <RECORD> 描述儲存在資料檔案中的資料。

  每個 <RECORD> 元素包含一或多個 <FIELD> 元素組成的集合。 這些元素對應到資料檔案中的欄位。 基本語法如下：

  <RECORD>

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

  </RECORD>

  每一個 <FIELD> 元素描述特定資料欄位的內容。 一個欄位只能對應到資料表的一個資料行。 並非所有欄位都需要對應到資料行。

  資料檔中的欄位可以是固定/可變長度或以字元終止。 「欄位值」可以如此表示：一個字元 (使用單一位元組表示法)、一個寬字元 (使用 Unicode 雙位元組表示法)、原生資料庫格式或一個檔案名稱。 如果欄位值是以檔案名稱表示，則檔案名稱指向包含目標資料表中 BLOB 資料行的值的檔案。

• <ROW> 描述當資料檔案中的資料匯入 SQL Server 資料表的語法。

  <ROW> 元素包含一組 <COLUMN> 元素。 這些元素對應到資料表資料行。 基本語法如下：

  <ROW>

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

  </ROW>

  每個 <COLUMN> 元素都只能對應至資料檔案中的一個欄位。 <ROW> 元素中的 <COLUMN> 元素順序會定義大量作業傳回這些元素的順序。 XML 格式檔案會將本機名稱指派給每個 <COLUMN> 元素，而此名稱與大量匯入作業之目標資料表中的資料行沒有任何關聯性。

XML 格式檔案的結構描述語法

本節包含 XML 格式檔案之 XML 結構描述的元素和屬性摘要。 格式檔案的語法與作業方向無關；也就是說，不管是大量匯出還是大量匯入作業，語法都相同。 本節也探討大量匯入如何使用 <ROW> 與 <COLUMN> 元素，以及如何將元素的 xsi:type 值放入資料集中。

若要了解語法如何對應到實際的 XML 格式檔案，請參閱範例 XML 格式檔案。

XML 結構描述的基本語法

此語法陳述式只會顯示元素 (<BCPFORMAT>、<RECORD>、<FIELD>、<ROW> 和 <COLUMN>) 及其基本屬性。

結構描述元素

本節摘要說明 XML 結構描述為 XML 格式檔案定義之每個元素的目的。 稍後會在此主題另一節說明屬性。

<BCPFORMAT>
格式檔案元素，它定義所指定資料檔的記錄結構，以及它和資料表中資料表資料列的資料行之對應關係。

<RECORD .../>
定義包含一或多個 <FIELD> 元素的複雜元素。 格式檔案中欄位的宣告順序是欄位出現在資料檔中的順序。

<FIELD .../>
定義資料檔中包含資料的欄位。

這個元素的屬性將在本主題稍後的 <FIELD> 元素的屬性中加以討論。

<ROW .../>
定義包含一或多個 <COLUMN> 元素的複雜元素。 <COLUMN> 元素的順序與 RECORD 定義中的 <FIELD> 元素順序無關。 相反的，格式檔案中的 <COLUMN> 元素順序會決定結果資料列集中的資料行順序。 資料欄位會以對應的 <COLUMN> 元素在 <COLUMN> 元素中宣告的順序載入。

如需詳細資訊，請參閱本主題稍後的大量匯入如何使用 <ROW> 元素。

<COLUMN>
定義資料行做為元素 (<COLUMN>)。 每個 <COLUMN> 元素都會對應至 <FIELD> 元素 (其識別碼指定於 <COLUMN> 元素的 SOURCE 屬性中)。

這個元素的屬性將在本主題稍後的 <COLUMN> 元素的屬性中加以討論。 另請參閱本主題稍後的大量匯入如何使用 <COLUMN> 元素。

</BCPFORMAT>
結束格式檔案時需要用到。

<FIELD> 元素的屬性

本節描述 <FIELD> 元素的屬性，這些屬性會摘要在下列的結構描述語法中：

<FIELD
ID ="fieldID"
xsi:type ="fieldType"
[ LENGTH ="n" ]
[ PREFIX_LENGTH ="p" ]
[ MAX_LENGTH ="m" ]
[ COLLATION ="collationName" ]
[ TERMINATOR ="terminator" ]
/>

每個 <FIELD> 元素都與其他元素無關。 以下透過屬性來說明欄位：

<FIELD> 元素的 Xsi:type 值

xsi:type 值是識別元素執行個體之資料類型的 XML 建構 (如同屬性般使用)。 如需有關使用「將 xsi:type 值放入資料集」的資訊，請參閱本節稍後的部分。

<FIELD> 元素的 Xsi:type 值支援下列資料類型。

如需 Microsoft SQL Server 資料類型的詳細資訊，請參閱資料類型 (Transact-SQL)。

<COLUMN> 元素的屬性

本節描述 <COLUMN> 元素的屬性，這些屬性會摘要在下列的結構描述語法中：

<COLUMN
SOURCE = "fieldID"
NAME = "columnName"
xsi:type = "columnType"
[ LENGTH = "n" ]
[ PRECISION = "n" ]
[ SCALE = "value" ]
[ NULLABLE = { "YES"
"NO" } ]
/>

欄位會使用下列屬性對應到目標資料表的資料行：

<COLUMN> 元素的 Xsi:type 值

xsi:type 值是識別元素執行個體之資料類型的 XML 建構 (如同屬性般使用)。 如需有關使用「將 xsi:type 值放入資料集」的資訊，請參閱本節稍後的部分。

<COLUMN> 元素支援原生 SQL 資料類型，如下所示：

如需 SQL Server 資料類型的詳細資訊，請參閱資料類型 (Transact-SQL)。

大量匯入如何使用 <ROW> 元素

某些內容會忽略 <ROW> 元素。 <ROW> 元素是否會影響大量匯入作業，視作業執行方式而定：

• bcp 命令

  將資料載入目標資料表時，bcp 會略過 <ROW> 元件。 相反地， bcp 會根據目標資料表的資料行類型來載入資料。

• Transact-SQL 陳述式 (BULK INSERT 和 OPENROWSET 的 Bulk 資料列集提供者)

  大量匯入資料到資料表時，Transact-SQL 陳述式會使用 <ROW> 元件來產生輸入資料列集。 此外，Transact-SQL 陳述式會根據在目標資料表中 <ROW> 與對應資料行中指定的資料行類型，執行適當的類型轉換。 若格式檔案中指定的資料行類型與目標資料表不同，會發生額外的類型轉換。 此額外類型轉換可能會導致 BULK INSERT 或 OPENROWSET 的 Bulk 資料列集提供者發生某些不一致 (亦即，遺失精確度) 的行為 (與 bcp相較)。

  <ROW> 元素中的資訊可讓您建構資料列，而不需要任何額外資訊。 因此，您可以使用 SELECT 陳述式 (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile) 來產生資料列集。

  注意

  OPENROWSET BULK 子句需要格式檔案 (請注意，要有 XML 格式檔案才能將欄位的資料類型轉換為資料行的資料類型)。

大量匯入如何使用 <COLUMN> 元素

若要大量匯入資料至資料表中，格式檔案中的 <COLUMN> 元素會藉由指定下列各項，將資料檔案欄位對應到資料表資料：

• 資料檔的資料列中每一個欄位的位置。

• 資料行類型，用來將欄位資料類型轉換成所要的資料行資料類型。

若沒有任何資料行對應到欄位，則該欄位不會被複製到產生的資料列。 此行為可讓資料檔產生具有不同資料行的資料列 (在不同的資料表中)。

同樣地，從資料表大量匯出資料時，格式檔案中的每一個 <COLUMN> 會將輸入資料表資料列中的資料行對應到輸出資料檔案中的對應欄位。

將 xsi:type 值放到資料集中

當您使用 XML Schema Definition (XSD) 語言來驗證 XML 文件時，xsi:type 值不會放到資料集中。 然而，您可以透過將 XML 格式檔案載入到 XML 文件 (例如： myDoc)，來將 xsi:type 資訊放到資料集中，如以下程式碼片段所示：

...;  
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 格式檔案

本節包含有關在各種情況下使用 XML 格式檔案的詳細資訊，包括 Adventure Works 範例。

這些範例說明使用 XML 格式檔案的重要部分，如下所示：

• 以與資料表資料行相同的方式排序字元資料欄位

• 以不同的方式排序資料欄位與資料表資料行

• 省略資料欄位

• 將不同類型的欄位對應到資料行

• 將 XML 資料對應到資料表

• 匯入固定長度或固定寬度的欄位

• 其他範例

A. 以與資料表資料行相同的方式排序字元資料欄位

下列範例顯示 XML 格式檔案，其中描述包含三個字元資料欄位的資料檔。 格式檔案將資料檔對應到包含三個資料行的資料表。 資料欄位是以一對一的方式，來對應資料表的資料行。

資料表 (資料列)： Person (Age int, FirstName varchar(20), LastName varchar(30))

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

下列 XML 格式檔案會將資料檔中的資料讀入資料表。

在 <RECORD> 元素中，格式檔案是以字元資料來表示這三個欄位內的資料值。 每一個欄位的 TERMINATOR 屬性，都會指出跟在資料值後面的結束字元。

資料欄位是以一對一的方式，來對應資料表的資料行。 在 <ROW> 元素中，格式檔案會將資料行 Age 對應到第一個欄位、資料行 FirstName 對應到第二個欄位，以及資料行 LastName 對應到第三個欄位。

<?xml version="1.0"?>  
<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="\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>

B. 以不同的方式排序資料欄位與資料表資料行

下列範例顯示 XML 格式檔案，其中描述包含三個字元資料欄位的資料檔。 格式檔案會將資料檔對應到包含三個資料行 (排序方式與資料檔的欄位不同) 的資料表。

資料表 (資料列)： Person (Age int, FirstName varchar(20), LastName varchar(30))

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

在 <RECORD> 元素中，格式檔案是以字元資料來表示這三個欄位內的資料值。

在 <ROW> 元素中，格式檔案會將資料行 Age 對應到第一個欄位、資料行 FirstName 對應到第三個欄位，以及資料行 LastName 對應到第二個欄位。

<?xml version="1.0"?>  
<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="\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>

C. 省略資料欄位

下列範例顯示 XML 格式檔案，其中描述包含四個字元資料欄位的資料檔。 格式檔案將資料檔對應到包含三個資料行的資料表。 第二個資料欄位不對應到任何資料表資料行。

資料表 (資料列)： Person (Age int, FirstName Varchar(20), LastName Varchar(30))

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

在 <RECORD> 元素中，格式檔案是以字元資料來表示這四個欄位內的資料值。 每一個欄位的 TERMINATOR 屬性，都會指出跟在資料值後面的結束字元。

在 <ROW> 元素中，格式檔案會將資料行 Age 對應到第一個欄位、資料行 FirstName 對應到第三個欄位，以及資料行 LastName 對應到第四個欄位。

<?xml version = "1.0"?>  
<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="\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>

D. 將 <FIELD> xsi:type 對應到 <COLUMN> xsi:type

下列範例顯示不同類型的欄位，以及它們與資料行的對應關係。

<?xml version = "1.0"?>  
<BCPFORMAT  
xmlns="http://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. 將 XML 資料對應到資料表

下列範例建立包含兩個資料行的空資料表 (t_xml)，其中第一個資料行對應到 int 資料類型，而第二個資料行則對應到 xml 資料類型。

CREATE TABLE t_xml (c1 int, c2 xml)

下列 XML 格式檔案將會在資料表 t_xml中載入資料檔。

<?xml version="1.0"?>  
<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="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. 匯入固定長度或固定寬度的欄位

下列範例描述各有 10 個或 6 個字元的固定欄位。 格式檔案分別以 LENGTH="10" 和 LENGTH="6"來表示這些欄位長度/寬度。 資料檔案的每一列都是以歸位字元和換行字元的組合 {CR}{LF} 作為結束，這在格式檔案中是以 TERMINATOR="\r\n"來表示。

<?xml version="1.0"?>  
<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="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>

其他範例

如需非 XML 格式檔案及 XML 格式檔案的其他範例，請參閱下列主題：

• 使用格式檔案以略過資料表資料行 (SQL Server)

• 使用格式檔案略過資料欄位 (SQL Server)

• 使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server)

相關工作

• 建立格式檔案 (SQL Server)

• 使用格式檔案大量匯入資料 (SQL Server)

• 使用格式檔案以略過資料表資料行 (SQL Server)

• 使用格式檔案略過資料欄位 (SQL Server)

• 使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server)

下一步

• 資料的大量匯入及匯出 (SQL Server)
• 資料類型 (Transact-SQL)
• 非 XML 格式檔案 (SQL Server)
• 匯入或匯出資料的格式檔案 (SQL Server)