XML-файлы форматирования (SQL Server)

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

SQL Server предоставляет XML-схему, которая определяет синтаксис для записи XML-файлов форматирования, используемых для массового импорта данных в таблицу SQL Server. XML-файлы форматирования должны придерживаться этой схемы, которая определена при помощи языка XML Schema Definition Language (XSDL). XML-файлы форматирования поддерживаются только в том случае, если средства SQL Server устанавливаются вместе с собственным клиентом SQL Server.

Xml-файл форматирования можно использовать с помощью команды bcp , инструкции BULK INSERT или INSERT ... ИНСТРУКЦИЯ SELECT * FROM OPENROWSET(BULK...). Команда bcp позволяет автоматически создать XML-файл форматирования для таблицы. Дополнительные сведения см. в разделе bcp Utility.

Преимущества 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> . Эти элементы соответствуют полям в файле данных. Базовый синтаксис:

  <ЗАПИСЬ>

  <ПОЛЕ .../> [ ...n ]

  </ЗАПИСЬ>

  Каждый <элемент FIELD> описывает содержимое определенного поля данных. Поле может быть сопоставлено только с одним столбцом таблицы. Столбцам не обязательно сопоставлять все поля.

  Поле в файле данных может иметь фиксированную или переменную длину или завершаться определенным символом. Значение поля может быть представлено в следующем виде: символ (однобайтовое представление), широкий символ (двухбайтовое представление Юникода), собственный формат базы данных или имя файла. Если значение поля представляется в виде имени файла, оно указывает на файл, который содержит значение столбца BLOB в целевой таблице.

• <ROW> описывает, как создавать строки данных из файла данных при импорте данных из файла в таблицу SQL Server.

  Элемент <ROW> содержит набор <элементов COLUMN> . Эти элементы соответствуют столбцам таблицы. Базовый синтаксис:

  <ROW>

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

  </РЯД>

  Каждый <элемент COLUMN> можно сопоставить только с одним полем в файле данных. Порядок элементов COLUMN> в <элементе ROW> определяет порядок<, в котором они возвращаются массовой операцией. XML-файл форматирования назначает каждому <элементу COLUMN> локальное имя, которое не имеет связи со столбцом в целевой таблице операции массового импорта.

Синтаксис схемы для XML-файлов форматирования

Этот раздел содержит список элементов и атрибутов схемы XML для XML-файлов форматирования. Синтаксис файла форматирования не зависит от направления операции; для операций массового импорта и массового экспорта синтаксис одинаков. В этом разделе также рассматривается, как массовый импорт использует <элементы ROW> и <COLUMN> и как поместить значение xsi:type элемента в набор данных.

Сведения о том, как синтаксис соответствует фактическим XML-файлам форматирования, см . в примере XML-файлов форматирования.

Базовый синтаксис схемы XML

В этом синтаксисе показаны только элементы (<BCPFORMAT>, RECORD>, <FIELD>, <<ROW> и COLUMN>) и <их основные атрибуты.

Элементы схемы

В этом разделе кратко описаны назначения каждого элемента, определяемого схемой XML для XML-файла форматирования. Атрибуты описаны в отдельных подразделах ниже в этом разделе.

<BCPFORMAT>
Элемент файла форматирования, который определяет структуру записей данного файла данных и его соответствие столбцам строки таблицы.

<ЗАПИСЬ.../>
Определяет сложный элемент, содержащий один или несколько <элементов FIELD> . Порядок, в котором поля объявлены в файле форматирования, является порядком, в котором эти поля будут расположены в файле данных.

<ПОЛЕ.../>
Определяет поле в файле данных, которое содержит данные.

Атрибуты этого элемента рассматриваются в атрибутах <элемента FIELD> далее в этом разделе.

<РЯД.../>
Определяет сложный элемент, содержащий один или несколько <элементов COLUMN> . Порядок элементов COLUMN> не зависит от порядка <<элементов FIELD> в определении RECORD. Скорее, порядок элементов COLUMN> в файле форматирования определяет порядок столбцов <результирующей строки. Поля данных загружаются в том порядке, в котором соответствующие <элементы COLUMN объявляются в элементе <COLUMN>>.

Дополнительные сведения см. в разделе о том, как массовый <импорт использует элемент ROW>, далее в этом разделе.

<COLUMN>
Определяет столбец как элемент (<COLUMN>). Каждый <элемент COLUMN соответствует элементу <FIELD> (идентификатор которого указан в атрибуте <SOURCE элемента COLUMN>>).

Атрибуты этого элемента рассматриваются в атрибутах <элемента COLUMN> далее в этом разделе. См. также, как массовый <импорт использует элемент COLUMN> далее в этом разделе.

</BCPFORMAT>
Требуется в конце файла форматирования.

<Атрибуты элемента FIELD>

В этом разделе описываются атрибуты <элемента FIELD> , которые приведены в следующем синтаксисе схемы:

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

Каждый <элемент FIELD> не зависит от других элементов. Поле описывается на основе следующих атрибутов:

Значения <Xsi:type элемента FIELD>

Значение xsi:type — это конструкция XML, используемая как атрибут и определяющая тип данных экземпляра элемента. Сведения по применению этой конструкции см. в пункте «Размещение значения xsi:type в наборе данных» ниже в этом разделе.

Значение <xsi:type элемента FIELD> поддерживает следующие типы данных.

Дополнительные сведения о типах данных Microsoft SQL Server см. в разделе "Типы данных" (Transact-SQL).

<Атрибуты элемента COLUMN>

В этом разделе описываются атрибуты <элемента COLUMN> , которые приведены в следующем синтаксисе схемы:

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

Поле сопоставлено со столбцом целевой таблицы с использованием следующих атрибутов:

Значения <Xsi:type элемента COLUMN>

Значение xsi:type — это конструкция XML, используемая как атрибут и определяющая тип данных экземпляра элемента. Сведения по применению этой конструкции см. в пункте «Размещение значения xsi:type в наборе данных» ниже в этом разделе.

Элемент COLUMN> поддерживает собственные <типы данных SQL следующим образом:

Дополнительные сведения о типах данных SQL Server см. в разделе Типы данных (Transact-SQL).

Как массовый <импорт использует элемент ROW>

Элемент <ROW> игнорируется в некоторых контекстах. <Влияет ли элемент ROW> на операцию массового импорта, зависит от того, как выполняется операция:

• Команда bcp

  При загрузке данных в целевую таблицу bcp игнорирует <компонент ROW> . Вместо этого команда bcp загружает данные с учетом типов столбцов целевой таблицы.

• Инструкции Transact-SQL (поставщик набора строк BULK INSERT и OPENROWSET)

  При массовом импорте данных в таблицу инструкции Transact-SQL используют <компонент ROW> для создания входного набора строк. Кроме того, инструкции Transact-SQL выполняют соответствующие преобразования типов на основе типов столбцов, указанных в <строке и> соответствующем столбце в целевой таблице. При несовпадении типов столбцов в файле форматирования и в целевой таблице производится дополнительное преобразование типов, Это дополнительное преобразование типа может привести к некоторым различиям (т. е. к потере точности) в работе команды BULK INSERT или поставщика массового набора строк OPENROWSET по сравнению с командой bcp.

  Сведения в элементе <ROW> позволяют создавать строку, не требуя дополнительных сведений. По этой причине можно создать набор строк с помощью инструкции SELECT (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile).

  Примечание.

  Чтобы использовать предложение OPENROWSET BULK, необходим файл форматирования (обратите внимание, что преобразование типа данных поля в тип данных столбца доступно только при наличии XML-файла форматирования).

Как массовый <импорт использует элемент COLUMN>

Для массового импорта данных в таблицу <элементы COLUMN> в формате сопоставляют поле файла данных с столбцами таблицы, указав:

• позицию каждого поля в строке файла данных;

• тип столбца, используемый для преобразования типа данных поля в необходимый тип данных столбца.

Если с полем не сопоставлено ни одного столбца, поле не копируется в сформированные строки. Такое поведение позволяет файлам данных формировать строки с различными столбцами (в разных таблицах).

Аналогичным образом для массового экспорта данных из таблицы каждый <столбец> в файле форматирования сопоставляет столбец из строки входной таблицы с соответствующим полем в выходном файле данных.

Поместите значение xsi:type в набор данных

Если XML-документ проверяется при помощи языка определения схемы XML (XSD), значение xsi:type не помещается в набор данных. Тем не менее, сведения xsi:type можно поместить в набор данных, загрузив XML-файл форматирования в XML-документ, например myDoc, как показано в следующем фрагменте кода:

...;  
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-данных с таблицей;

• импорт полей фиксированной длины и полей фиксированной ширины.

• Дополнительный пример

А. Упорядочить поля символьных данных так же, как столбцы таблицы

В следующем примере представлен XML-файл форматирования, описывающий файл данных, в котором содержатся три поля символьных данных. Файл форматирования сопоставляет файл данных с таблицей, содержащей три столбца. Поля данных соответствуют «один к одному» столбцам таблицы.

Таблица (строка): Person (Age int, FirstName varchar(20), LastName varchar(30))

Файл данных (запись): возвращаемое имя вкладки "Возраст<" вкладки>>"Имя_<имени<">

Следующий 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))

Файл данных (запись): возвращаемое имя вкладки>"Возраст<" вкладки>Lastname<<>

В элементе <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))

Файл данных (запись): возвращаемое имя вкладки "Возраст<" tab>employeeID<tab>Firstname<tab>Lastname<>

В элементе <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>

Е. Сопоставление 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)