Примеры массового импорта и экспорта XML-документов (SQL Server)

Статья
06/25/2013

Можно выполнить массовый импорт XML-документов в базу данных SQL Server или осуществить массовый экспорт XML-документов из базы данных SQL Server. В этом разделе приведены примеры и того, и другого.

Для выполнения массового импорта данных из файла в таблицу SQL Server или несекционированное представление могут использоваться следующие средства.

Служебная программа bcp

Программа bcp может выполнять экспорт везде в базе данных SQL Server, где работает инструкция SELECT, включая секционированные представления.
BULK INSERT
Инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...)

Дополнительные сведения см. в разделах Массовый импорт и экспорт данных с использованием программы bcp (SQL Server) и Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK...) (SQL Server).

Примеры

Далее следуют примеры.

А. Массовый импорт XML-данных в виде двоичного байтового потока
Б. Массовый импорт XML-данных в существующую строку
В. Массовый импорт XML-данных из файла, содержащего DTD
Г. Указание признаков конца поля явным образом при помощи файла форматирования
Д. Массовый экспорт XML-данных

А.Массовый импорт XML-данных в виде двоичного байтового потока

При массовом импорте XML-данных из файла, содержащего объявление кодировки, которое необходимо применить, нужно указать параметр SINGLE_BLOB в функции OPENROWSET(BULK…). Параметр SINGLE_BLOB гарантирует, что средство синтаксического анализа XML в SQL Server произведет импорт данных в соответствии со схемой кодирования, указанной в XML-объявлении.

Образец таблицы

Для проверки примера A необходимо создать образец таблицы T.

USE tempdb
CREATE TABLE T (IntCol int, XmlCol xml);
GO

Образец файла данных

Перед запуском примера А необходимо создать файл в кодировке UTF-8 (C:\SampleFolder\SampleData3.txt), содержащий следующий образец, который определяет схему в кодировке UTF-8.

<?xml version="1.0" encoding="UTF-8"?>
<Root>
          <ProductDescription ProductModelID="5">
             <Summary>Some Text</Summary>
          </ProductDescription>
</Root>

Пример A

В этом примере используется параметр SINGLE_BLOB в инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...) для импорта данных из файла с именем SampleData3.txt и вставки экземпляра данных XML в таблицу с одним столбцом, образец таблицы T.

INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'c:\SampleFolder\SampleData3.txt',
   SINGLE_BLOB) AS x;

Замечания

С помощью параметра SINGLE_BLOB можно избежать несоответствия между кодировкой XML-документа (указанной в объявлении кодировки XML) и кодовой страницей строки, используемой сервером.

Если при использовании типов данных NCLOB или CLOB возникает конфликт кодовой страницы или кодировки, необходимо выполнить одно из следующих действий.

Удалить XML-декларацию, чтобы успешно импортировать содержимое XML-файла данных.
Указать кодовую страницу в параметре CODEPAGE запроса, который соответствует схеме кодирования, используемой в XML-декларации.
Подобрать настройки параметров сортировки баз данных для схемы кодирования XML-данных, отличной от кодировки Юникод.

[В начало]

Б.Массовый импорт XML-данных в существующую строку

В этом примере при помощи поставщика массового набора строк OPENROWSET в существующую строку или строки образца таблицы T добавляются инструкции XML.

Примечание
Для выполнения этого примера требуется выполнить скрипт проверки, приведенный в примере А. В этом примере создается таблица tempdb.dbo.T, а затем выполняется массовый импорт данных из таблицы SampleData3.txt.

Образец файла данных

В примере Б используется измененная версия образца файла данных SampleData3.txt из предыдущего примера. Для запуска этого примера нужно изменить содержимое этого файла следующим образом:

<Root>
          <ProductDescription ProductModelID="10">
             <Summary>Some New Text</Summary>
          </ProductDescription>
</Root>

Пример Б

-- Query before update shows initial state of XmlCol values.
SELECT * FROM T
UPDATE T
SET XmlCol =(
SELECT * FROM OPENROWSET(
   BULK 'C:\SampleFolder\SampleData3.txt',
           SINGLE_BLOB
) AS x
)
WHERE IntCol = 1;
GO

[В начало]

В.Массовый импорт XML-данных из файла, содержащего DTD

Примечание по безопасности
Включать поддержку для определений типов документов (DTD) не рекомендуется, если только это не является неотъемлемой частью среды XML. Включение поддержки DTD увеличивает уязвимую контактную зону сервера и может привести к атаке типа «отказ в обслуживании». При необходимости включения поддержки DTD снизить риск для этой опасности можно с помощью обработки только доверенных XML-документов.

Включать поддержку для определений типов документов (DTD) не рекомендуется, если только это не является неотъемлемой частью среды XML. Включение поддержки DTD увеличивает уязвимую контактную зону сервера и может привести к атаке типа «отказ в обслуживании». При необходимости включения поддержки DTD снизить риск для этой опасности можно с помощью обработки только доверенных XML-документов.

При попытке использования команды bcp для импорта XML-данных из файла, содержащего DTD, может возникнуть одна из следующих ошибок:

SQLState = 42000, NativeError = 6359

«Error = [Microsoft][SQL Server Native Client][ SQL Server]Разбор XML при помощи встроенного DTD не допускается. Используйте CONVERT с параметром стиля 2 для включения ограниченной поддержки встроенного DTD.»

«Не удалось выполнить BCP-копирование %s»

Чтобы избежать этой проблемы, можно импортировать XML-данные из файла, содержащего DTD, при помощи функции OPENROWSET(BULK...), а затем указать параметр CONVERT в предложении SELECT. Базовым синтаксисом команды является:

INSERT ... SELECT CONVERT(…) FROM OPENROWSET(BULK...)

Образец файла данных

Перед проверкой этого примера массового импорта создайте файл (C:\temp\Dtdfile.xml), содержащий следующий образец данных:

<!DOCTYPE DOC [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1>January</elem1>

Образец таблицы

В примере В используется образец таблицы T1, созданный следующей инструкцией CREATE TABLE:

USE tempdb;
CREATE TABLE T1(XmlCol xml);
GO

Пример В

В этом примере используется OPENROWSET(BULK...) и в предложении SELECT указывается параметр CONVERT для импорта XML-данных из Dtdfile.xml в образец таблицы T1.

INSERT T1
  SELECT CONVERT(xml, BulkColumn, 2) FROM 
    OPENROWSET(Bulk 'c:\temp\Dtdfile.xml', SINGLE_BLOB) [rowsetresults];

После выполнения инструкции INSERT определение DTD исключается из XML и хранится в таблице T1.

[В начало]

Г.Указание признаков конца поля явным образом при помощи файла форматирования

В следующем примере демонстрируется, как выполнить массовый импорт XML-документа Xmltable.dat.

Образец файла данных

Документ из Xmltable.dat содержит два значения XML, по одному в каждой строке. Первое значение XML имеет кодировку UTF-16, второе — UTF-8.

Содержимое этого файла данных показано в следующем шестнадцатеричном дампе:

FF FE 3C 00 3F 00 78 00-6D 00 6C 00 20 00 76 00  *..<.?.x.m.l. .v.*
65 00 72 00 73 00 69 00-6F 00 6E 00 3D 00 22 00  *e.r.s.i.o.n.=.".*
31 00 2E 00 30 00 22 00-20 00 65 00 6E 00 63 00  *1...0.". .e.n.c.*
6F 00 64 00 69 00 6E 00-67 00 3D 00 22 00 75 00  *o.d.i.n.g.=.".u.*
74 00 66 00 2D 00 31 00-36 00 22 00 3F 00 3E 00  *t.f.-.1.6.".?.>.*
3C 00 72 00 6F 00 6F 00-74 00 3E 00 A2 4F 9C 76  *<.r.o.o.t.>..O.v*
0C FA 77 E4 80 00 89 00-00 06 90 06 91 2E 9B 2E  *..w.............*
99 34 A2 34 86 00 83 02-92 20 7F 02 4E C5 E4 A3  *.4.4..... ..N...*
34 B2 B7 B3 B7 FE F8 FF-F8 00 3C 00 2F 00 72 00  *4.........<./.r.*
6F 00 6F 00 74 00 3E 00-00 00 00 00 7A EF BB BF  *o.o.t.>.....z...*
3C 3F 78 6D 6C 20 76 65-72 73 69 6F 6E 3D 22 31  *<?xml version="1*
2E 30 22 20 65 6E 63 6F-64 69 6E 67 3D 22 75 74  *.0" encoding="ut*
66 2D 38 22 3F 3E 3C 72-6F 6F 74 3E E4 BE A2 E7  *f-8"?><root>....*
9A 9C EF A8 8C EE 91 B7-C2 80 C2 89 D8 80 DA 90  *................*
E2 BA 91 E2 BA 9B E3 92-99 E3 92 A2 C2 86 CA 83  *................*
E2 82 92 C9 BF EC 95 8E-EA 8F A4 EB 88 B4 EB 8E  *................*
B7 EF BA B7 EF BF B8 C3-B8 3C 2F 72 6F 6F 74 3E  *.........</root>*
00 00 00 00 7A                                   *....z*

Образец таблицы

При выполнении массового импорта или экспорта XML-документа следует использовать признаки конца поля, которые могут отсутствовать в некоторых документах, например последовательность из четырех значений NULL (\0), заканчивающаяся буквой z: \0\0\0\0z.

В этом примере показано, как использовать эти признаки конца поля в образце таблицы xTable. Чтобы создать этот образец таблицы, используйте следующую инструкцию CREATE TABLE:

USE tempdb;
CREATE TABLE xTable (xCol xml);
GO

Образец файла форматирования

Признак конца поля должен быть указан в файле форматирования. В примере Г создается файл форматирования Xmltable.fmt в формате, отличном от XML, который содержит следующее:

9.0
1
1       SQLBINARY     0       0       "\0\0\0\0z"    1     xCol         ""

Этот файл форматирования можно использовать для массового импорта XML-документов в таблицу xTable при помощи команды bcp или инструкции BULK INSERT или INSERT ... SELECT * FROM OPENROWSET(BULK...).

Пример Г

В этом примере используется файл форматирования Xmltable.fmt в инструкции BULK INSERT для импорта содержимого файла XML-данных с именем Xmltable.dat.

BULK INSERT xTable 
FROM 'C:\Xmltable.dat'
WITH (FORMATFILE = 'C:\Xmltable.fmt');
GO

[В начало]

Д.Массовый экспорт XML-данных

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

bcp bulktest..xTable out a-wn.out -N -T -S<server_name>\<instance_name>

Примечание
SQL Server не сохраняет кодировку XML, если XML-данные постоянно хранятся в базе данных. Поэтому оригинальная кодировка полей XML недоступна при экспорте XML-данных. Для экспорта XML-данных SQL Server использует кодировку UTF-16.

[В начало]