Импорт xml в ms sql

У меня есть файл, который структурирован так:

Я не совсем уверен, что это действительно XML, но мне нужно импортировать его в SQL Server, но все, что я пытаюсь, похоже, не работает.

Пожалуйста, может ли кто-нибудь указать мне в правильном направлении либо с некоторым примером кода, либо с рекомендацией о том, какой метод использовать?

В идеале я хотел бы получить необработанные данные в плоскую таблицу по строкам:

Наконец, это происходит из довольно больших файлов, и мне нужно будет регулярно их импортировать.

И, конечно, вы можете легко сделать

чтобы вставить эти данные в реляционную таблицу.

Обновление: при условии, что у вас есть ваш XML файл, вы можете использовать этот код для загрузки XML файла в переменную XML в SQL Server:

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

Обновить # 2: , если вам нужны параметры, тоже — используйте этот оператор XQuery:

Вы делаете это, создавая таблицу назначения, а затем файл сопоставления схемы, который отображает элементы xml в столбцы таблицы.

Ваш может выглядеть примерно так:

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

Если вам нужно сделать это без переменной XML (из строки в табличной функции)

Таким образом, у вас может быть функция типа

Если вы пытаетесь импортировать XML как "чистое" поле XML, вы должны создать такую ​​таблицу (очевидно, со многими другими полями, как вы хотите):

Затем вы можете легко вставить свой XML в виде строки:

Затем для запроса начать с MSDN t-SQL XML

Если вы предпочитаете хранить его как строку, используйте varchar (max) вместо типа столбца [XML] и той же вставки. Но если вы хотите легко запросить, я предлагаю тип [XML]. При использовании плоской струны вам потребуется много работы, если вы не будете внедрять какой-либо код приложения для его анализа и хранения в плоской таблице. Хорошим подходом может быть хранение XML в "сжатии" TABLE и VIEW для получения данных с плоской настройкой поля.

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Можно выполнить массовый импорт XML-документов в базу данных SQL Server SQL Server или осуществить массовый экспорт XML-документов из базы данных SQL Server SQL Server . You can bulk import XML documents into a SQL Server SQL Server database or bulk export them from a SQL Server SQL Server database. В этом разделе приведены примеры и того, и другого. This topic provides examples of both.

Для выполнения массового импорта данных из файла в таблицу SQL Server SQL Server или несекционированное представление могут использоваться следующие средства. To bulk import data from a data file into a SQL Server SQL Server table or non-partitioned view, you can use the following:

Программаbcp bcp utility
Программа bcp может выполнять экспорт везде в базе данных SQL Server SQL Server , где работает инструкция SELECT, включая секционированные представления. You can also use the bcp utility to export data from anywhere in a SQL Server SQL Server database that a SELECT statement works, including partitioned views.

BULK INSERT BULK INSERT

Инструкции INSERT . SELECT * FROM OPENROWSET(BULK. ). INSERT . SELECT * FROM OPENROWSET(BULK. )

Дополнительные сведения см. в следующих разделах: For more information, see the following topics.

Примеры Examples

Далее следуют примеры. The examples are the following:

Массовый импорт XML-данных в виде двоичного байтового потока Bulk importing XML data as a binary byte stream

При массовом импорте XML-данных из файла, содержащего объявление кодировки, которое необходимо применить, нужно указать параметр SINGLE_BLOB в предложении OPENROWSET(BULK…). When you bulk import XML data from a file that contains an encoding declaration that you want to apply, specify the SINGLE_BLOB option in the OPENROWSET(BULK. ) clause. Параметр SINGLE_BLOB гарантирует, что средство синтаксического анализа XML в SQL Server SQL Server произведет импорт данных в соответствии со схемой кодирования, указанной в XML-объявлении. The SINGLE_BLOB option ensures that the XML parser in SQL Server SQL Server imports the data according to the encoding scheme specified in the XML declaration.

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

Для проверки представленного ниже примера A необходимо создать образец таблицы T . To test example A below, create sample table T .

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

Перед запуском примера А необходимо создать файл в кодировке UTF-8 ( C:SampleFolderSampleData3.txt ), содержащий следующий образец, который определяет схему в кодировке UTF-8 . Before you can run example A, you must create a UTF-8 encoded file ( C:SampleFolderSampleData3.txt ) that contains the following sample instance that specifies the UTF-8 encoding scheme.

Пример A Example A

В этом примере используется параметр SINGLE_BLOB в инструкции INSERT . SELECT * FROM OPENROWSET(BULK. ) для импорта данных из файла с именем SampleData3.txt и вставки экземпляра данных XML в таблицу с одним столбцом, образец таблицы T . This example uses the SINGLE_BLOB option in an INSERT . SELECT * FROM OPENROWSET(BULK. ) statement to import data from a file named SampleData3.txt and insert an XML instance in the single-column table, sample table T .

Remarks Remarks

С помощью параметра SINGLE_BLOB можно избежать несоответствия между кодировкой XML-документа (указанной в объявлении кодировки XML) и кодовой страницей строки, используемой сервером. By using SINGLE_BLOB in this case, you can avoid a mismatch between the encoding of the XML document (as specified by the XML encoding declaration) and the string codepage implied by the server.

Если при использовании типов данных NCLOB или CLOB возникает конфликт кодовой страницы или кодировки, необходимо выполнить одно из следующих действий. If you use NCLOB or CLOB data types and run into a codepage or encoding conflict, you must do one of the following:

Удалить XML-декларацию, чтобы успешно импортировать содержимое XML-файла данных. Remove the XML declaration to successfully import the contents of the XML data file.

Указать кодовую страницу в параметре CODEPAGE запроса, который соответствует схеме кодирования, используемой в XML-декларации. Specify a code page in the CODEPAGE option of the query that matches the encoding scheme that is used in the XML declaration.

Подобрать настройки параметров сортировки баз данных для схемы кодирования XML-данных, отличной от кодировки Юникод. Match, or resolve, the database collation settings with a non-Unicode XML encoding scheme.

Массовый импорт XML-данных в существующую строку Bulk importing XML data in an existing row

В этом примере при помощи поставщика массового набора строк OPENROWSET в существующую строку или строки образца таблицы T добавляются инструкции XML. This example uses the OPENROWSET bulk rowset provider to add an XML instance to an existing row or rows in sample table T .

Чтобы выполнить этот пример, вначале необходимо выполнить скрипт проверки в примере А. В этом примере сначала создается таблица tempdb.dbo.T ; затем в нее проводится массовый импорт данных из файла SampleData3.txt . To run this example, you must first complete the test script provided in example A. That example creates the tempdb.dbo.T table and bulk imports data from SampleData3.txt .

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

В примере Б используется измененная версия образца файла данных SampleData3.txt из предыдущего примера. Example B uses a modified version of the SampleData3.txt sample data file from the preceding example. Для запуска этого примера нужно изменить содержимое этого файла следующим образом: To run this example, modify the content of this file as follows:

Пример B-адреса Example B

Массовый импорт XML-данных из файла, содержащего DTD Bulk importing XML data from a file that contains a DTD

Включать поддержку для определений типов документов (DTD) не рекомендуется, если только это не является неотъемлемой частью среды XML. We recommended that you not enable support for Document Type Definitions (DTDs) if it is not required in your XML environment. Включение поддержки DTD увеличивает уязвимую контактную зону сервера и может привести к атаке типа «отказ в обслуживании». Turning on DTD support increases the attackable surface area of your server, and may expose it to a denial-of-service attack. При необходимости включения поддержки DTD снизить риск для этой опасности можно с помощью обработки только доверенных XML-документов. If you must enable DTD support, you can reduce this security risk by processing only trusted XML documents.

При попытке использования команды bcp для импорта XML-данных из файла, содержащего DTD, может возникнуть одна из следующих ошибок: During an attempt to use a bcp command to import XML data from a file that contains a DTD, an error similar to the following can occur:

SQLState = 42000, NativeError = 6359 "SQLState = 42000, NativeError = 6359"

«Error = [Microsoft][SQL Server Native Client][ SQL Server]Разбор XML при помощи встроенного DTD не допускается. "Error = [Microsoft][SQL Server Native Client][ SQL Server]Parsing XML with internal subset DTDs not allowed. Используйте CONVERT с параметром стиля 2 для включения ограниченной поддержки встроенного DTD.» Use CONVERT with style option 2 to enable limited internal subset DTD support."

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

Чтобы избежать этой проблемы, можно импортировать XML-данные из файла, содержащего DTD, при помощи функции OPENROWSET(BULK. ) , а затем указать параметр CONVERT в предложении SELECT . To work around this problem, you can import XML data from a data file that contains a DTD by using the OPENROWSET(BULK. ) function and then specifying the CONVERT option in the SELECT clause of the command. Базовым синтаксисом команды является: The basic syntax for the command is:

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

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

Перед проверкой этого примера массового импорта создайте файл ( C: empDtdfile.xml ), содержащий следующий образец данных: Before you can test this bulk import example, create a file ( C: empDtdfile.xml ) that contains the following sample instance:

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

В примере В используется образец таблицы T1 , созданный следующей инструкцией CREATE TABLE : Example C uses the T1 sample table that is created by the following CREATE TABLE statement:

Пример В Example C

В этом примере используется OPENROWSET(BULK. ) и в предложении CONVERT указывается параметр SELECT для импорта XML-данных из Dtdfile.xml в образец таблицы T1 . This example uses OPENROWSET(BULK. ) and specifies the CONVERT option in the SELECT clause to import the XML data from Dtdfile.xml into sample table T1 .

После выполнения инструкции INSERT определение DTD исключается из XML и хранится в таблице T1 . After the INSERT statement executes, the DTD is stripped from the XML and stored in the T1 table.

Указание признаков конца поля явным образом при помощи файла форматирования Specifying the field terminator explicitly using a format file

В следующем примере демонстрируется, как выполнить массовый импорт XML-документа Xmltable.dat . The following example shows how to bulk import the following XML document, Xmltable.dat .

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

Документ из Xmltable.dat содержит два значения XML, по одному в каждой строке. The document in Xmltable.dat contains two XML values, one for each row. Первое значение XML имеет кодировку UTF-16, второе — UTF-8. The first XML value is encoded with UTF-16, and the second value is encoded with UTF-8.

Содержимое этого файла данных показано в следующем шестнадцатеричном дампе: The contents of this data file are shown in the following Hex dump:

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

При выполнении массового импорта или экспорта XML-документа следует использовать признаки конца поля , которые не могут присутствовать в каком-либо документе, например последовательность из четырех значений NULL ( ), заканчивающаяся буквой z : z . When you bulk import or export an XML document, you should use a field terminator that cannot possibly appear in any of the documents; for example, a series of four nulls ( ) followed by the letter z : z .

В этом примере показано, как использовать эти признаки конца поля в образце таблицы xTable . This example shows how to use this field terminator for the xTable sample table. Чтобы создать этот образец таблицы, используйте следующую инструкцию CREATE TABLE : To create this sample table, use the following CREATE TABLE statement:

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

Признак конца поля должен быть указан в файле форматирования. The field terminator must be specified in the format file. В примере Г создается файл форматирования Xmltable.fmt в формате, отличном от XML, который содержит следующее: Example D uses a non-XML format file named Xmltable.fmt that contains the following:

Этот файл форматирования можно использовать для массового импорта XML-документов в таблицу xTable при помощи команды bcp или инструкции BULK INSERT или INSERT . SELECT * FROM OPENROWSET(BULK. ) . You can use this format file to bulk import XML documents into the xTable table by using a bcp command or a BULK INSERT or INSERT . SELECT * FROM OPENROWSET(BULK. ) statement.

Пример Г Example D

В этом примере используется файл форматирования Xmltable.fmt в инструкции BULK INSERT для импорта содержимого файла XML-данных с именем Xmltable.dat . This example uses the Xmltable.fmt format file in a BULK INSERT statement to import the contents of an XML data file named Xmltable.dat .

Массовый экспорт XML-данных Bulk exporting XML data

В следующем примере для выполнения массового экспорта XML-данных из таблицы, созданной в предыдущем примере при помощи того же XML-файла форматирования, используется программа bcp . The following example uses bcp to bulk export XML data from the table that is created in the preceding example by using the same XML format file. В следующей команде bcp и являются заполнителями, которые должны быть заменены соответствующими значениями: In the following bcp command, and represent placeholders that must be replaced with appropriate values:

SQL Server SQL Server не сохраняет кодировку XML, если XML-данные постоянно хранятся в базе данных. does not save the XML encoding when XML data is persisted in the database. Поэтому оригинальная кодировка полей XML недоступна при экспорте XML-данных. Therefore, the original encoding of XML fields is not available when XML data is exported. SQL Server SQL Server использует для экспорта XML-данных кодировку UTF-16. uses UTF-16 encoding when exporting XML data.

Я 5 больших XML-файлов который я очень хочу проанализировать. Все они слишком большие, чтобы открыть в текстовом редакторе и поэтому я не знаю их схем XML.

Я попытался импортировать их в SQL server, однако процесс дал мне ошибку, хотя я уверен, что они действительны, поскольку они были получены из очень авторитетных программистов.

Я также пробовал другие методы, но каждый борется с большие размеры файлов (в MySQL) или указать, что файлы содержат недопустимые символы XML (Доступ И Excel).

Как я могу чтение и вставка данных программно? можно ли это сделать с помощью SQL-запроса?

11 ответов

по состоянию на 2013 год.

единственный вариант экономии времени, на мой взгляд, для загрузки больших / огромных XML-файлов в SQL Server (как кто-то ранее кратко упоминал), чтобы использовать SQLXML 4.0 библиотека.

Это решение, которое я принял для загрузки огромных XML-файлов (7Гб в размере) на ежедневной основе. Предыдущий процесс, который использовал манипуляции C# в задаче скрипта, занял несколько часов. Использование sqlxml 4.0 занимает 15-20 минут. Как установить SQLXML 4.0. шаг за шагом!—15—>здесь. Для практических примеров в том, как это сделать end to end следуйте по этой ссылке MSDN.

мой XML также имеет вложенные элементы, поэтому он довольно сложный, в результате получается 10 таблиц с 2,5 до 4 миллионами строк каждая (ежедневный файл иногда больше 7 ГБ). Моя работа была основана исключительно на информации, которую я узнал из двух ссылок выше.

преимущества:

• создание XSD может занять некоторое время и требует некоторых знаний. Когда я сделал это, я узнал что-то новое, так что это не было настоящим недостатком для меня.
• видя, насколько прост пакет служб SSIS, ваш менеджер будет иметь впечатление, что вы не сделали никакой работы.

для просмотра больших файлов использовать Просмотр Больших Текстовых Файлов, милый маленький драгоценный камень.

Примечание: вопрос довольно старый, но "проблема" остается горячей. Я добавил этот пост для разработчиков, которые Google, как массовая загрузка XML-файлов в SSIS и земли здесь.

Он предназначен для предоставления вам SQL-подобный доступ к большим текстовым файлам, включая XML. Что-то вроде

. должен работать, чтобы вы начали. Кроме того, будьте осторожны, что документация появится в вашем меню "Пуск" вместе с исполняемым файлом после установки-я не думаю, что есть хороший копия на линии.

вы должны знать схему. Попробуйте загрузить TextPad или что-то подобное для просмотра файлов.

когда вы знаете схему, вы можете сделать несколько вещей, чтобы получить их в SQL. Одним из подходов было бы использование OpenXML http://msdn.microsoft.com/en-us/library/ms186918.aspx.

Я широко тестировал синтаксический анализатор MSSQL xml, bcp.exe утилита отлично работает для этого. Трюк придумывает правый Терминатор строки, так как это должно быть значение, которое не может возникнуть в вашем документе. Например, вы можете сделать следующее:

Ceate простой текстовый файл, содержащий только выбранный разделитель. Например, поместите эту строку в delim.txt:

затем объедините это до конца экземпляра документа, из командной строки:

копировать myFile.xml + delim.txt вне.xml / b

после этого вы можете BCP его в базу данных, как:

Если документ UTF-16, то замените переключатель-c на-w

первое, что я сделал, это получить первые X байтов (например, первый 1 МБ) XML-файлов, чтобы я мог взглянуть на них с помощью редактора по моему выбору.

Если у вас Cygwin установлен у вас уже есть хорошая утилита GNU, чтобы достичь этого:глава

кроме того, можно найти родной порт самой утилиты GNU здесь: http://unxutils.sourceforge.net/

для просмотра очень больших файлов, я нашел V просмотрщик файлов чтобы быть отличным.

Я использовал его для файлов размером до 8 ГБ. Для файлов с фиксированной длиной записи очень легко перемещаться по размеру блока, поскольку он основан на диске.

обратите внимание, что нет возможности редактирования.

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

вы пробовали использовать OPENROWSET импортировать большие XML-файлы в таблицу SQL Server?

поскольку у меня нет файлов 5GB под рукой, я не могу проверить это сам.

есть еще один способ решить эту проблему: потоковая передача Linq-to-Xml. Проверьте это блоге где Джеймс Ньютон-Кинг показывает, как читать XElement один за другим, и серия из двух частей здесь и здесь по той же теме командой Microsoft XML блог.

вы должны загрузить свой XML в база данных XML, например,Berkeley DB XML или Xindice

кроме того, я не уверен, что он может масштабироваться до 850mb, но первый объект XML Editor, и библиотека парсера, на которой она построена, может обрабатывать довольно большие файлы.

и baretail с должен отображать ваши файлы, не нарушая пота.