Как загрузить xml файл в mysql

Обновлено: 07.07.2024

Полная БД выгружается налоговой в двух форматах - DBF и XML. Но с DBF работать неудобно, да и таблицы там нарублены на кучу частей. В общем, сразу решено было использовать исходные данные в XML, тем более что MySQL имеет средства загрузки из них напрямую.

Первым делом на основе XSD-схем данных были построены скрипты создания таблиц. Заодно выяснилось, что схемы даны не все, а их содержимое не всегда соответствует файлу с описанием формата выгрузки, размещённому на сайте ФИАС. Но задача несложная, работаем по факту.

В качестве движка выбран MyISAM - это ускоряет импорт. Также были отключены (в скриптах - закомментированы) ограничения и первичные индексы - с той же целью.

Имея готовые структуры, несложно выполнить импорт данных. Первым делом имена XML-файлов для удобства работы были укорочены. Затем построены запросы импорта данных (исходные файлы были расположены по пути b:\fias).

Импорт всех файлов, кроме самого большого, прошёл быстро и хорошо (большие файлы NORMDOC.XML и ADDROBJ.XML импортировались 2 и 4 минуты соответственно, все остальные практически мгновенно).

А вот с самым большим, 16-гигабайтным HOUSE.XML возникла проблема. Сервер импортировал порядка четверти файла, после чего потребление памяти возрастало с исходных 450 Мбайт до 2 Гбайт и процесс обрывался по ошибке недостатка памяти.

Было принято решение поделить файл на части и выполнить их импорт. Для нарезки было быстро накидано приложение на VB6, файл поделен на 9 частей по 1,8 Гбайт.

Итоговый размер базы данных составил около 9 Гбайт.

Весь процесс выполнялся локально на рабочей станции.

Я не знаю как нарезать XML.
а остальное сделал

Со всем вышеприведённым кодом провёл только что импорт свежих данных - успешно.

Рукозады, которые выкладывают свежую БД, не следят за выгрузкой. В результате в некоторых записях выгружаемого XML кириллица заменяется энтитьками, и размер данных поля начинает превышать установленную их же собственной схемой данных длину. Соответственно возникают грабли при импорте - ведь LOAD XML не производит конвертацию энтитек в символы.

Есть несколько способов передачи XML-данных в SQL Server . Пример:

Если в базе данных SQL Server данные хранятся в столбце типа [n]text или image, то эту таблицу можно импортировать с помощью служб Службы Integration Services. Изменить тип столбца на XML можно с использованием инструкции ALTER TABLE.

Массовое копирование данных из другой базы данных SQL Server можно выполнить с использованием команды bcp out, после чего с помощью команды bcp in произвести массовую вставку данных в базу данных более поздней версии.

Если в базе данных SQL Server данные хранятся в реляционных столбцах, необходимо создать новую таблицу со столбцом [n]text и, возможно, с первичным ключевым столбцом для идентификации строк. Чтобы получить XML-данные, созданные на сервере с помощью инструкции FOR XML, и записать их в столбец [n]text , требуется программный код на клиентской стороне. Затем эти данные необходимо передать в базу данных более поздней версии, выбрав любую из вышеупомянутых методик. XML-данные можно напрямую записать в XML-столбец базы данных более поздней версии.

Массовая загрузка XML-данных

Массовую загрузку XML-данных на сервер можно осуществить при помощи реализованных в SQL Serverсредств массовой загрузки, таких как bcp. Инструкция OPENROWSET позволяет загрузить данные в XML-столбец из файлов. Это показано в следующем примере.

Пример Загрузка XML-данных из файлов

Следующий пример показывает, как вставить строку в таблицу T. Значение XML-столбца загружается из файла «C:\MyFile\xmlfile.xml» как объект CLOB, а целочисленному столбцу назначается значение 10.

Кодировка текста

SQL Server хранит XML-данные в кодировке Юникод (UTF-16). XML-данные, извлекаемые из баз данных сервера, предоставляются в кодировке UTF-16. Если требуются данные в другой кодировке, извлеченные данные нужно преобразовать. Иногда XML-данные могут быть представлены в другой кодировке. Если это так, во время загрузки данных нужно быть внимательным. Пример:

Если текст XML представлен в кодировке Юникод (UCS-2, UTF-16), можно назначить его XML-столбцу, переменной или параметру без каких-либо проблем.

Если кодировка отлична от Юникода и неявна из-за исходной кодовой страницы, строковая кодовая страница в базе данных должна быть той же самой или совместимой с элементами кода, которые следует загрузить. Если нужно, используйте предложение COLLATE. Если такой кодовой страницы на сервере не существует, необходимо добавить явную XML-декларацию с корректной кодировкой.

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

Пример явное указание кодировки

Предположим, что есть XML-документ vcdoc, хранящийся как varchar(max) , который не объявлен явно как XML. Приведенная ниже инструкция добавляет объявление XML с кодировкой "iso8859-1", присоединяет к нему XML-документ, приводит результат к типу varbinary(max) (чтобы сохранить двоичное представление) и, наконец, приводит его к типу XML. Это позволяет процессору XML выполнять синтаксический анализ данных в соответствии с указанной кодировкой «iso8859-1» и создавать для строковых значений соответствующее представление UTF-16.

Несоответствия кодировок строк

При копировании и вставке XML как строкового литерала в окно редактора запросов служб в среде SQL Server Management Studioмогут возникнуть несоответствия с кодировкой строк типа (N)VARCHAR. Это будет зависеть от кодировки копируемого экземпляра XML. Во многих случаях может возникнуть необходимость удаления XML-декларации. Пример:

Затем нужно будет добавить N, чтобы сделать экземпляр XML экземпляром Юникода. Пример:

Как известно, в MySQL 5.1 внедрили функции для работы с XML, сегодня я расскажу про эти две функции более подробно, а так же вкратце опишу как работать с XPath в MySQL.

Для работы с XML есть две функции:

ExtractValue() - Позволяет выбирать записи средствами XPAth;
UpdateXML() - Возвращает измененный XML-фрагмент.

Функция ExtractValue и XPath

Для примеров, я буду работать с переменной, но с тем же успехом вы можете записать XML-фрагмент в любое поле с текстовым типом данных. XPath поддерживается на любом движке.
Создадим переменную @xml:

Выберем XML-фрагмент с помощью XPath:

Как видно, мы выбрали содержимое ноды <z> -> "test".

Примеры:

Немного ознакомимся с языком запросов XPath и приведу несколько примеров.
Создадим для примера такую вот переменную:

1) Посчитаем количество элементов с нодой <x>:

2) Выведем содержимое первой ноды <x>:

3) Выведем содержимое ноды с атрибутом id и значением атрибута "y-id":

В данный момент MySQL поддерживает XPath версии 1.0, рекомендую посмотреть переведенную спецификацию по этой версии языка.

Функция UpdateXML

С помощью функции UpdateXML мы можем изменять какие-либо XML-фрагменты. Используется для изменения XML-фрагмента и сохранения в базу данных, например при UPDATE. Синтаксис следующий:

Пример:

2) Заменим содержимое ноды с атрибутом id и значением атрибута "y-id" на "<c>test</c>":

Производительность

Потестим производительность на моей машине для разработки:

Microsoft Windows XP [Версия 5.1.2600] SP3
Mysql 5.1.26

Intel Core Duo T2050 1.6 GHz (L2 Cache 2MB, Bus Speed 133 MHZ, Rated FSB 533 MHz)
Memory Type DDR2, Dual Channels, Size 1024 MB (Frequency 266 MHz)

Для теста создадим переменную:

Проверим на выборку 1 000 000 раз.

Проверим на изменение XML-фрагмента 1 000 000 раз.

Выводы никакие делать не буду, так как тесты слишком приблизительны. Надо тестировать на конкретной машине, желательно не под Windows (на Linux или под OpenSolaris с конкретно собранной для этой ОСи движком MySQL) и надо тестить конкретные XPath-запросы!

Я спрашивал об этом ранее около 6 недель назад, но мне было не совсем ясно, с точки зрения того, что я написал, что я ожидал и как этого можно достичь. Несмотря на терпение некоторых пользователей, я не смог решить свою проблему. Я вернулся и попытался узнать немного больше и, надеюсь, теперь я могу опубликовать что-то, что имеет больше смысла.

Клиент отправляет свои графики доставки в формате файла XML. Это не самый красноречивый из файлов, но я думаю, что я понял это. Что мне нужно из этого получить, так это таблица, которая показывает мне, какое количество клиент хочет получить от любой данной части за данную неделю. Есть 50+ частей и 12 недель, но я сократил их до 5 в надежде, что они могут быть размещены здесь, хорошо.

Я буду добавлять эту информацию в таблицу каждую неделю, а дата расписания - это дата расписания. Эта дата взята из этой строки (строка 11):

Номер детали, номер недели и количество немного сложнее. Номер детали взят из списка номеров

И номер недели и количество из этого раздела;

Где номер строки - это номер детали, а номер столбца - неделя. Значение является значением (и форматированным значением).

Я думал о том, чтобы создать две таблицы: справочную таблицу со всеми номерами деталей и номерами строк и вторую таблицу с номером строки, номером недели и количеством, а затем соединить их вместе в поле строки. Любая из таблиц может содержать дату расписания.

Может кто-нибудь, пожалуйста, дайте мне код, чтобы помочь мне достичь этого.

Я пробовал это в качестве отправной точки для создания первой таблицы номеров деталей, но это просто возвращает 0 записей:

Что еще хуже, есть два адреса доставки, а нижняя часть раздела (только одна часть) указана отдельно. Похоже, что они различаются только по адресам доставки и ничего больше. Я думаю, что я счастлив проигнорировать это пока, если это не легко обойти.

2 ответа

Я сохранил ваш XML-файл как 'e: \ Temp \ CrystalReport.xml'. Ваш XML имеет пространства имен, и они нуждаются в особой обработке. WITH XMLNAMESPACES предложение позаботится об этом.

Пожалуйста, смотрите ниже, как загрузить файл XML в таблицу БД.

Я не собираюсь читать целое и все из этого XML, но - чтобы показать вам принципы - я пошел довольно далеко :-)

Для разрешения CrossTabs я использую некоторые XQuery FLWOR, возвращающие значения в заново сгенерированном XML.

Подсказка: уберите пространство имен (и первую строку WITH), чтобы увидеть результат в более удобочитаемом формате.

Читайте также: