Примеры массового импорта и экспорта XML-документов (SQL Server)
Область применения: SQL Server 2016 (13.x) и более поздних
версий База данных SQL Azure Управляемый экземпляр SQL Azure
Вы можете массово импортировать XML-документы в базу данных SQL Server или выполнить массовый экспорт из базы данных 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 в SQL Server с помощью компонента массовой загрузки XML
- Коллекции схем XML (SQL Server)
Примеры
- А. Массовый импорт XML-данных в виде двоичного байтового потока
- B. Массовый импорт XML-данных в существующую строку
- C. Массовый импорт XML-данных из файла, содержащего DTD
- D. Указание признаков конца поля явным образом при помощи файла форматирования
- Е. Массовый экспорт XML-данных
Массовый импорт XML-данных в виде двоичного байтового потока
При массовом импорте XML-данных из файла, содержащего объявление кодировки, которое необходимо применить, укажите SINGLE_BLOB
параметр в предложении OPENROWSET(BULK...)
. Параметр SINGLE_BLOB
гарантирует, что средство синтаксического анализа XML в SQL Server импортирует данные в соответствии со схемой кодирования, указанной в объявлении XML.
Пример таблицы
Чтобы проверить пример A, создайте пример таблицы T
.
USE tempdb;
GO
CREATE TABLE T (
IntCol INT IDENTITY(1,1),
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;
Вы также можете явно указать имена столбцов следующим образом:
INSERT INTO T (
XmlCol
)
SELECT
x.BulkColumn
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-документов.
При импорте XML-данных из файла, содержащего DTD, может возникнуть следующая ошибка:
SQLState = 42000, NativeError = 6359
Error = [Microsoft][SQL Server Native Client][SQL Server]Parsing XML with internal subset DTDs not allowed.
Use CONVERT with style option 2 to enable limited internal subset DTD support.
BCP copy %s failed
Чтобы избежать этой проблемы, можно импортировать XML-данные из файла, содержащего DTD, при помощи функции OPENROWSET(BULK...)
, а затем указать параметр CONVERT
в предложении SELECT
. Базовым синтаксисом команды является:
INSERT ... SELECT CONVERT(...) FROM OPENROWSET(BULK...)
Пример файла данных
Перед проверкой этого примера массового импорта создайте файл (C:\SampleFolder\Dtdfile.xml
), содержащий следующий образец данных:
<!DOCTYPE DOC [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1>January</elem1>
Пример таблицы
В примере В используется образец таблицы T1
, созданный следующей инструкцией CREATE TABLE
:
USE tempdb;
GO
CREATE TABLE T1(XmlCol XML);
GO
Пример В
В этом примере используется OPENROWSET(BULK...)
и в предложении CONVERT
указывается параметр SELECT
для импорта XML-данных из Dtdfile.xml
в образец таблицы T1
.
INSERT INTO T1
SELECT CONVERT(XML, BulkColumn, 2)
FROM OPENROWSET(
BULK 'C:\SampleFolder\Dtdfile.xml',
SINGLE_BLOB
) AS [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;
GO
CREATE TABLE xTable (xCol XML);
GO
Пример файла форматирования
Признак конца поля должен быть указан в файле форматирования. В примере D используется файл 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:\SampleFolder\Xmltable.dat'
WITH (FORMATFILE = 'C:\SampleFolder\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-данных. SQL Server использует кодировку UTF-16 при экспорте XML-данных.