Как открыть xml файл в sql server
Обновлено: 03.07.2024
Изучение задач, связанных с программированием баз данных на платформе MS SQL Server.
понедельник, 12 мая 2014 г.
Загрузка XML-документов больших объемов
Как известно для загрузки XML-документов в таблицы базы данных можно использовать функцию openxml в сочетании с хранимыми процедурами sp_xml_preparedocument и sp_xml_removedocument. Эти процедуры используют объектную модель DOM. При этом XML-документ полностью загружается в оперативную память. Тестирование показывает, что когда объем XML-данных становится громадным, то данная методика напрягает оперативную память и работает очень медленно. Более того, если объем XML большой по сравнению с имеющейся памятью, то метод может отработать с ошибками.
В этой статье мы расскажем об ином подходе к загрузке XML-данных в таблицу SQL Server. Можно воспользоваться компонентом SQL XML Bulk Load. Компонент SQL XML Bulk Load не напрягает оперативную память. Он разбивает большой XML-документ на меньшие фрагменты и обрабатывает их независимо.
Компонент SQL XML Bulk Load является компонентом COM. Таким образом его можно применять в любом языке, поддерживающем OLE-автоматизацию. Я сравниваю его с инструкцией bulk insert, которая также используется при загрузке в базу больших объемов данных из файлов. Когда создается экземпляр SQLXMLBulkLoad, то у этого объекта есть несколько свойств. Наиболее важные среди них такие. Свойство ConnectionString используется для соединения с сервером. Свойство ErrorLogFile указывает файл, в котором будет собираться информация о возможных ошибках, например, данные о поврежденном XML-файле или данные об ошибках на стороне сервера. Для блокирования таблицы можно применять свойство ForceTableLock. Когда оно включено, то таблицу нельзя прочесть с nolock в ходе массовой загрузки. Также есть много других свойств, похожих на те, которые есть у инструкции bilk insert. Например, можно установить свойства, которые позволяют отключать триггеры, проверку уникальности, проверку пустых полей и прочие ограничения целостности.
Загрузка данных в таблицу начинается с вызова метода Execute, который имеет 2 параметра. Первый параметр это имя XML-документа, содержащего схему, которая описывает структуру документа и данные мэппинге между узлами XML и объектами базы данных. Второй параметр это имя файла с данными. Метод Execute выполняет нелогируемую высокоскоростную операцию загрузки.
Есть еще одно замечательное свойство этого компонента. Несмотря на то, что таблица может быть заблокирована для других соединений в процессе работы SQL XML, можно создать несколько соединений, которые одновременно наполняют таблицу из одного или нескольких XML-файлов. Они не будут друг друга блокировать и загрузка может стать еще более быстрой. Для управления параллельными соединениями можно использовать Service Broker. Пример этого есть в одной из моих предыдущих статей: Многопоточные операции и компонент Service Broker.
Ниже мы будут созданы фрагменты кода для загрузки xml с помощью SQLXMLBulkLoad. Также приведем сравнительные данные о скорости работы SQLXMLBulkLoad и функции openxml.
Перед началом работы на серверном компьютере необходимо установить компонент SQLXML. После этого для создания объекта воспользуется процедурами sp_OA.
@src varchar ( 300 ), @desc varchar ( 300 ), @step nvarchar ( 100 ), @Err nvarchar ( max )
begin try
set @step = N' Объект '
exec sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload.4.0' , @han out
if @res <> 0 raiserror ( @step , 16 , 1 )
exec @res = sp_OASetProperty @han , 'ErrorLogFile' , 'C:\ Документы \ Временные \test\log.log'
if @res <> 0 raiserror ( @step , 16 , 1 )
set @step = N' Блокировка '
exec @res = sp_OASetProperty @han , 'ForceTableLock' , 1
if @res <> 0 raiserror ( @step , 16 , 1 )
set @step = N' Строка подклоючения '
exec @res = sp_OASetProperty @han , 'ConnectionString' ,
'Provider=SQLNCLI10;Server=EDYNAK\SHADOW;Database=TestClr;Trusted_Connection=Yes'
if @res <> 0 raiserror ( @step , 16 , 1 )
exec @res = sp_OAMethod @han , 'Execute' , null, @logfile , @filename
if @res <> 0 raiserror ( @step , 16 , 1 )
end try
begin catch
if error_message () = @step
begin
exec sp_OAGetErrorInfo @han , @src out , @desc out
set @Err = N'Ошибка в работе компонента. Операция: ' + @step + N'. Номер: ' +
cast ( @res as nvarchar ( 100 ) ) + '; описание: ' + isnull ( @desc ,
N'неизвестно' ) + N'; источник: ' + isnull ( @src , N'неизвестно' )
end
else
begin
set @Err = N'Ошибка в работе сервера. Номер : ' + isnull ( cast ( error_number ()
as nvarchar ( 100 ) ), N'' ) + '; описание : ' + isnull ( error_message (),
N' неизвестно ' ) + N'; номер строки : ' + isnull ( cast ( error_line () as
nvarchar ( 100 ) ), N' неизвестно ' )
end
end catch
Я 5 больших XML-файлов который я очень хочу проанализировать. Все они слишком большие, чтобы открыть в текстовом редакторе и поэтому я не знаю их схем XML.
Я попытался импортировать их в SQL server, однако процесс дал мне ошибку, хотя я уверен, что они действительны, поскольку они были получены из очень авторитетных программистов.
Я также пробовал другие методы, но каждый борется с большие размеры файлов (в MySQL) или указать, что файлы содержат недопустимые символы XML (Доступ И Excel).
Как я могу чтение и вставка данных программно? можно ли это сделать с помощью SQL-запроса?
по состоянию на 2013 год.
единственный вариант экономии времени, на мой взгляд, для загрузки больших / огромных XML-файлов в SQL Server (как кто-то ранее кратко упоминал), чтобы использовать SQLXML 4.0 библиотека.
мой XML также имеет вложенные элементы, поэтому он довольно сложный, в результате получается 10 таблиц с 2,5 до 4 миллионами строк каждая (ежедневный файл иногда больше 7 ГБ). Моя работа была основана исключительно на информации, которую я узнал из двух ссылок выше.
преимущества:
недостатки
- создание XSD может занять некоторое время и требует некоторых знаний. Когда я сделал это, я узнал что-то новое, так что это не было настоящим недостатком для меня.
- видя, насколько прост пакет служб SSIS, ваш менеджер будет иметь впечатление, что вы не сделали никакой работы.
для просмотра больших файлов использовать Просмотр Больших Текстовых Файлов, милый маленький драгоценный камень.
Примечание: вопрос довольно старый, но "проблема" остается горячей. Я добавил этот пост для разработчиков, которые Google, как массовая загрузка XML-файлов в SSIS и земли здесь.
Он предназначен для предоставления вам SQL-подобный доступ к большим текстовым файлам, включая XML. Что-то вроде
. должен работать, чтобы вы начали. Кроме того, будьте осторожны, что документация появится в вашем меню "Пуск" вместе с исполняемым файлом после установки-я не думаю, что есть хороший копия на линии.
вы должны знать схему. Попробуйте загрузить TextPad или что-то подобное для просмотра файлов.
Я спрашивал об этом ранее около 6 недель назад, но мне было не совсем ясно, с точки зрения того, что я написал, что я ожидал и как этого можно достичь. Несмотря на терпение некоторых пользователей, я не смог решить свою проблему. Я вернулся и попытался узнать немного больше и, надеюсь, теперь я могу опубликовать что-то, что имеет больше смысла.
Клиент отправляет свои графики доставки в формате файла XML. Это не самый красноречивый из файлов, но я думаю, что я понял это. Что мне нужно из этого получить, так это таблица, которая показывает мне, какое количество клиент хочет получить от любой данной части за данную неделю. Есть 50+ частей и 12 недель, но я сократил их до 5 в надежде, что они могут быть размещены здесь, хорошо.
Я буду добавлять эту информацию в таблицу каждую неделю, а дата расписания - это дата расписания. Эта дата взята из этой строки (строка 11):
Номер детали, номер недели и количество немного сложнее. Номер детали взят из списка номеров
И номер недели и количество из этого раздела;
Где номер строки - это номер детали, а номер столбца - неделя. Значение является значением (и форматированным значением).
Я думал о том, чтобы создать две таблицы: справочную таблицу со всеми номерами деталей и номерами строк и вторую таблицу с номером строки, номером недели и количеством, а затем соединить их вместе в поле строки. Любая из таблиц может содержать дату расписания.
Может кто-нибудь, пожалуйста, дайте мне код, чтобы помочь мне достичь этого.
Я пробовал это в качестве отправной точки для создания первой таблицы номеров деталей, но это просто возвращает 0 записей:
Что еще хуже, есть два адреса доставки, а нижняя часть раздела (только одна часть) указана отдельно. Похоже, что они различаются только по адресам доставки и ничего больше. Я думаю, что я счастлив проигнорировать это пока, если это не легко обойти.
2 ответа
Я сохранил ваш XML-файл как 'e: \ Temp \ CrystalReport.xml'. Ваш XML имеет пространства имен, и они нуждаются в особой обработке. WITH XMLNAMESPACES предложение позаботится об этом.
Пожалуйста, смотрите ниже, как загрузить файл XML в таблицу БД.
Я не собираюсь читать целое и все из этого XML, но - чтобы показать вам принципы - я пошел довольно далеко :-)
Для разрешения CrossTabs я использую некоторые XQuery FLWOR, возвращающие значения в заново сгенерированном XML.
Подсказка: уберите пространство имен (и первую строку WITH), чтобы увидеть результат в более удобочитаемом формате.
Как-то давным-давно, кажется – в прошлую пятницу, обходя окрестности офиса, озаботилось окаянное начальство тем, что я провожу время в праздности и созерцании котиков и кошечек.
— А не загрузить ли тебе ФИАС, друг ситный! – сказало начальство. – Ибо процесс его загрузки что-то не нравится нашим бизнес-подразделениям. Долго, говорят грузится, грузит продуктовый сервер, да и чувак, который писал процесс загрузки, намедни уволился, года как три уже.
К тому же, все там давно нужно переделать, так что ты возьми, создай себе базу и обеспечь периодическую заливку ФИАСА. Всё, как говорится, не задерживаю!
Тут надо сказать, что к программированию я имею отдаленное отношение, т.к. являюсь, скорее, DBA. Хотя, с другой то стороны, загрузка больших массивов предварительно отпрепарированной информации – как раз задача DBA, nest pa?
— Да ладно… Щас сделаем – сказал я начальству, и ринулся на сайт ФИАСА, засучив рукава.
«О! Да тут есть dbf!» — подумал я, радостно потирая руки, параллельно подивившись отсутствию стандартного «де-факто» zip архива, и, наоборот, присутствию давно почившего в бозе arj и проприетарному пардон, открытому, конечно, 7zip [но который всё равно нельзя разжать с помощью powershell Expand-Archive]. Т.е. чистым powershell’ом это не скачаешь и не распакуешь. Придётся громоздить на сервер всякую хрень. Ну да ладно.
Инструментарий по массовой параллельной загрузке dbf файлов у меня написан лет уже как несколько, так что проблем возникнуть не должно.
Я распаковал dbf-ки, запустил программу загрузки, и пока данные грузились, набросал скриптик, который склеивал отдельные «почти-одноименные» таблички в одну, по принадлежности.
Большие таблицы грузились нормально, а маленькие – содержали кракозябры.
И так мне от всего этого стало грустно и тоскливо, что я мужественно взял себя в руки и занялся прокрастинацией и своими прямыми обязанностями. Возиться с битыми dbf-ками – жутко не хотелось.
Прокрастинировал я дня два, до тех пор, пока заявки не кончились, и на горизонте снова не замаячило начальство, с сакраментальным вопросом «А чё это мы сачкуем?».
И, так как ответить было нечего, а возиться с dbf – по-прежнему не хотелось, я решил загрузить ФИАС из xml, тем более что, как говориться, стильно, модно, молодёжно, и «dbf – умирающий формат».
На этом, разрешите затянувшийся вводный монолог закончить, и перейти к делу.
Итак, грузить было решено c помощью SQLXMLBULKLOAD – замечательной библиотеки, как раз и предназначенной для массовой (bulk) заливки структурированных xml файлов.
Для того, чтобы ее использовать, нужно скачать и установить библиотеку SqlXml 4.0 Service Pack 1 (SP1).
В случае ФИАСа, однако, «структурированность» не особо востребована. Т.к. файлы там не то, что не xml… они, конечно, xml, но, по сути – это плоские таблицы с данными, в каждом файле – одна таблица.
Вот слегка модифицированный ее вариант:
Однако, для осуществления массовой загрузки xml с помощью этой библиотеки необходимы аннотированные xsd схемы, в которых, собственно, и указывается, каким образом и куда что грузится.
Упоминания о том, что такие схемы есть, «но только старые» — я нашел аж на десятке сайтов, но нигде не нашел самих схем. И разозлился.
Модифицировать имеющуюся на сайте ФИАСа схему для импорта данных вручную – не сложно.
Но… в совокупности – там 271 поле! Это ж сколько сидеть и тупить надо!
Поэтому я решил модифицировать эти схемы автоматически, заодно и создав целевые таблицы в БД.
SQLXMLBULKLOAD умеет автоматически создавать таблицы для загружаемых данных из аннотированной схемы, но, с другой стороны, если я эту схему буду делать, то почему бы, попутно не сделать эти таблицы самому, так, как мне нужно?
Я скачал xsd схемы с сайта ФИАС и проанализировал их чисто визуально.
К счастью, они все – однотипные, поэтому создать целевую БД и модифицировать сами схемы для загрузки можно с помощью всего нескольких не очень сложных запросов.
1. Создадим пустую базу FIAS2.
Почему «2»? Ну, потому что «1» — была база из dbf-ок. Возможно, мы поговорим о ней позже.
2. Создадим в этой базе пару табличек.
Первая табличка будет содержать xsd схемы, а вторая – будет представлять из себя фактически схему данных, полученную из этих xsd схем.
xsd- схемы – это, вообще-то обычные xml файлы, так что и работать с ними можно как с обычными xml-ями.
Подробнее по запросам:
Первый – загружает xsd схемы в таблицу на сервере, попутно снабжая каждую схему аннотацией в поле [table] – именем таблицы, в которую я хочу грузить данные из соответствующей таблицы.
Безусловно, можно было-бы сделать так, что каждый раз, когда схемы поменяются, их можно было бы скачивать, подсовывать в какую-нибудь папку и пересоздавать по ним структуру целевых таблиц заново и автоматически, и заново же и автоматически модифицировать схемы, но т.к. структура меняется крайне редко, последний раз менялась – аж в 16 году – делать такой автомат крайне лениво. Лучше потратить 30 секунд на CTRL+C – CTRL+V.
Поэтому загрузка в табличку со схемами так захардкожена, и пути к загружаемым файлам, а также имена таблиц – прописаны руками.
Второй запрос – вытаскивает из схем информацию о структуре таблиц. Я не стал возиться с 3-нф, а развернул ее как одну таблицу.
Обратите, кстати, внимание на вот этот кусочек (+)
Довольно часто новички задают вопрос: можно ли вычислить какое-то сложное выражение в запросе 1 раз, а потом многократно использовать его в нескольких местах. Да, можно. Вот так, например:
Работает с некоторыми ограничениями, конечно. Но, начав пользоваться, вы быстро поймете, в чем они заключаются.
3. Создадим скрипты, создающие таблицы ФИАС, а потом запустим их, и создадим сами таблицы:
Кому лень всё это проделывать – скрипты на создание таблиц и аннотированные схемы ФИАС будут приложены в конце статьи.
4. Коль скоро в xsd есть нормальное описание таблиц и полей в них – используем его, и создадим описание структуры в расширенных свойствах таблиц и их полей:
Вот код, который получился и выполнился в результате работы5. Модифицируем сами схемы:
6. И, наконец, выгрузим xsd схемы на диск для дальнейшего использования:
Теперь осталось только вызывать процедуру загрузки для каждого xml файла с соответствующей схемой (сами распакованные файлы хранятся в папке e:\tmp, а схемы — мы выгрузили в c:\files\FIAS):
Ну вот, теперь наконец-то совсем всё.
В тестовой среде загрузка у меня длилась без малого 5 часов (sic. ). И это еще без индексации таблиц.
При этом загрузка системы ввода-вывода была на уровне 3 Мб/с, и было задействовано всего пара ядер.
Загрузка адресных объектов шла со скоростью не более 3 тысяч записей в секунду, загрузка домов — со скоростью не более 8 тысяч в секунду. А их, на минуточку, 60 миллионов.
С чем это связано — я не могу сказать, придется еще поковыряться с кодом и, видимо, с настройкой ВМ.
Обратите внимание, что загрузка производится в кучи. А уже потом предполагается создать кластерные индексы по всяким их AOID.
Т.к. они представляют из себя GUIDы, лучше сделать именно так, потому, что если создать кластерные индексы ДО загрузки — на выходе получится таблица, фрагментированная где-то процентов на 50, не меньше. Ну и индексы лучше тоже создавать после загрузки, потому что вставка в индексы делает загрузку еще медленнее.
Именно поэтому имена таблиц у меня начинаются с подчерка ( _ ). Предполагалось, что я сначала загружу данные в них, потом воссоздам индексы целевых таблиц, и, затем, спокойно сделаю Alter table switch.
Также обратите внимание на не совсем оптимальные типы данных для полей таблиц ФИАС.
Например, использование int, там где достаточно tinyint, или nvarchar в полях, где предполагается хранение только числовых значений, типа кода региона или ОКАТО.
Так в схемах :-(.
Однако, ничего не мешает вам, после создания таблицы метаданных, [dbo].[_FIAS], поковыряться в ней руками и произвести тюнинг, а уже потом, создать новые загрузочные схемы на основе откорректированных метаданных.
Ну, а если изжить такую медленную загрузку всё же не удастся, видимо, придется вернуться к варианту с загрузкой из dbf, и расковырять, наконец, кракозябры в маленьких dbf. И написать еще одну статью.
dbf в 8 потоков нормально загрузилось за 1 час с копейками, и еще примерно час ушел на слияние отдельных таблиц в целевые, и построение индексов по ним.
… Ну, или, наконец, вытащить руки из жжж… эээ… афедрона, и наконец сделать нормальный data tier application, и грузить уже данные, как все нормальные люди, через SSIS.
Читайте также: