Импорт XML в MySQL

Задача
Вы хотите импортировать XML-документ в таблицу MySQL.

Решение
Настройте программу синтаксического анализа XML для чтения документа. Затем используйте записи документа для формирования и выполнения предложений INSERT.

Обсуждение
Процедура импорта документа XML зависит от возможности синтаксического разбора документа и извлечения из него содержимого записей. Все определяется тем, как записан документ. Например, в одном формате имена и значения столбцов могут быть представлены как атрибуты элементов :












...


Другой формат может использовать имена столбцов как имена элементов, а значения столбцов – как содержимое этих элементов:




Jane
A
47


Jane
B
50

...


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


В данном разделе я предполагаю наличие второго из только что описанных форматов.

Для обработки такого документа можно использовать модуль XML::XPath, позволяющий ссылаться на элементы документа посредством путевых выражений. Например, путь //row выбирает все элементы ниже корня документа, а путь * выбирает всех потомков указанного элемента. Пути можно использовать в XML::XPath для получения всех элементов , а затем для получения списка всех столбцов каждой строки.

Сценарий xml_to_mysql.pl принимает три аргумента:

% xml_to_mysql.pl имя_БД имя_таблицы имя_файла_xml

Аргумент имени файла указывает, какой документ следует импортировать, а аргументы имен базы данных и таблицы – в какую таблицу импортировать документ.

Сценарий xml_to_mysql.pl обрабатывает аргументы командной строки и устанавливает соединение с MySQL (код не приводится), затем обрабатывает документ:

#! /usr/bin/perl -w
# xml_to_mysql.pl – чтение файла XML в MySQL
use strict;
use DBI;
use XML::XPath;
# ... обработка опций командной строки (не приводится) ...
# ... соединение с базой данных (не приводится) ...
# Открыть файл для чтения
my $xp = XML::XPath->new (filename => $file_name);
my $row_list = $xp->find ("//row"); # найти множество элементов
print "Number of records: " .


$row_list->size () . "\n";
foreach my $row ($row_list->get_nodelist ()) # цикл по строкам
{
my @name; # массив для имен столбцов
my @val; # массив для значений столбцов
my $col_list = $row->find ("*"); # потомки (столбцы) строки
foreach my $col ($col_list->get_nodelist ()) # цикл по столбцам
{
# сохранение имени и значения столбца
push (@name, $col->getName ());
push (@val, $col->string_value ());
}
# создание и выполнение предложения INSERT
my $stmt = "INSERT INTO $tbl_name ("
. join (",", @name)
. ") VALUES ("
. join (",", ("?") x scalar (@val))
. ")";
$dbh->do ($stmt, undef, @val);
}
$dbh->disconnect ();
exit (0);

Сценарий создает объект XML::XPath, который открывает и анализирует документ. Затем запрашивается набор элементов объекта при помощи пути //row. Размер этого набора указывает количество записей, содержащихся в документе.

Для обработки каждой строки сценарий использует путь * для поиска всех потомков объекта строки. Каждый потомок соответствует столбцу строки. Такое использование * в качестве пути для get_nodelist() удобно, так как нам не нужно заранее знать, какие столбцы следует ожидать.


Сценарий xml_to_mysql.pl получает имя и значение каждого столбца и сохраняет их в массивах @name и @value. После того, как все столбцы обработаны, массивы используются для построения предложения INSERT, которое указывает имена столбцов, обнаруженных в строке, и включает заполнитель для каждого значения данных (формирование списка заполнителей рассматривается в рецепте 2.6). Затем сценарий запускает предложение, передавая значения столбцов в do() для связывания с заполнителями.

В предыдущем разделе сценарий mysql_to_xml.pl использовался для экспорта содержимого таблицы expt в документ XML. Сценарий xml_to_mysql.pl может применяться для выполнения обратной операции импорта документа в MySQL:

% xml_to_mysql.pl cookbook expt expt.xml

По мере обработки документа сценарий формирует и выполняет такой набор предложений:

INSERT INTO expt (subject,test,score) VALUES ('Jane','A','47')
INSERT INTO expt (subject,test,score) VALUES ('Jane','B','50')
INSERT INTO expt (subject,test) VALUES ('Jane','C')
INSERT INTO expt (subject,test) VALUES ('Jane','D')
INSERT INTO expt (subject,test,score) VALUES ('Marvin','A','52')
INSERT INTO expt (subject,test,score) VALUES ('Marvin','B','45')
INSERT INTO expt (subject,test,score) VALUES ('Marvin','C','53')
INSERT INTO expt (subject,test) VALUES ('Marvin','D')

Заметьте, что не все из этих выражений вставляют одно и то же количество столбцов.


Предложения с «отсутствующими» значениями соответствуют строкам со значениями NULL.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Диагностическая утилита для LOAD DATA
Извлечение и перестановка столбцов файлов данных
Импорт с помощью LOAD DATA и утилиты mysqlimport
Импорт файлов в формате CSV
Использование временных таблиц для преобразования дат

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных