Лучший способ вставки больших xml файлов в столбцы xml (на удаленном SQL-сервере)

Ответ 1

Лучший способ загрузить XML в базу данных - НЕ загружать XML в базу данных. Если вам действительно нужно это сделать (и вы, вероятно, не знаете), вы должны смотреть на решение noSQL, такое как Cassandra или Mongo.

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

Все, что сказано, вы можете использовать BCP или интерфейс OLEDB, который поддерживает потоковое вещание. Вот пример: Примеры массового импорта

Ответ 2

Ниже приведен один потенциальный способ обрезать это, используя только .NET. Я не пытался выполнить это, но он должен работать.

    public static ChunkedXmlInsert(XmlItem item)
    {
        int bufferSize = 65536;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            CreateTempTable(connection);

            int position = 0;
            using (StreamReader textStream = File.OpenText(item.XmlFileName))
            {
                char[] buffer = new char[bufferSize];
                int length = textStream.Read(buffer, position, buffer.Length);
                long id = InsertFirstBlock(connection, new string(buffer, 0, length));

                while (textStream.EndOfStream == false)
                {
                    position += length;
                    length = textStream.Read(buffer, position, buffer.Length);
                    AppendBlock(connection, id, new string(buffer, 0, length));
                }
            }

            CopyRecordFromTemp(connection, id);
        }
    }

    private static void CreateTempTable(SqlConnection connection)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"CREATE TABLE #TBL_XML (
                                                              [XmlFileID] [BIGINT] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
                                                              [FileName] [NVARCHAR](500) NULL,
                                                              [XmlData] [NVARCHAR(MAX)] NULL,
                                                              [DateCreated] [DATETIME] NOT NULL
                                                          )";
            command.ExecuteNonQuery();
        }
    }

    private static long InsertFirstBlock(SqlConnection connection, string text)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO #TBL_XML
                                                        ( [XmlData] , 
                                                          [FileName] , 
                                                          [DateCreated]
                                                        ) 
                                        VALUES (@XMLData, @FileName, GETDATE()); SELECT SCOPE_IDENTITY()";

            command.Parameters.AddWithValue("@FileName", System.IO.Path.GetFileName(item.XmlFileName));
            command.Parameters.AddWithValue("@XmlData", text);
            return (long)command.ExecuteScalar();
        }
    }

    private static void AppendBlock(SqlConnection connection, long id, string text)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"UPDATE #TBL_XML
                                            SET XmlData = XmlData + @xmlData
                                    WHERE XmlFileID = @XmlFileID";

            command.Parameters.AddWithValue("@XmlData", text);
            command.Parameters.AddWithValue("@XmlFileID", id);
            command.ExecuteNonQuery();
        }
    }

    private static long CopyRecordFromTemp(SqlConnection connection, long id)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO [dbo].[TBL_XML] ([XmlData], [FileName], [DateCreated])
                                    SELECT CONVERT(xml, [XmlData]), [FileName], [DateCreated]
                                    FROM #TBL_XML
                                    WHERE XmlFileID = @XmlFileID; SELECT SCOPE_IDENTITY()";
            return (long)command.ExecuteScalar();
        }
    }

Ответ 3

В этой статье приводится некоторая справочная информация для работы с LOB. Проверьте это

Я просто проверил синтаксис OPENROWSET BULK с полем xml, и он работает. Если ваш SQL-сервер имеет доступ к файлу, это один из вариантов (и я просто заметил, что это не вариант, поэтому я буду экспериментировать больше). В противном случае вам нужно будет попробовать решение для записи смещения. До сих пор я не могу получить этот метод .WRITE для работы с полем xml.

В этой статье указано, что вы можете использовать BCP для вставки XML.  Это потребует установки клиентских инструментов SQL Server.

Ответ 4

Поскольку вам нужно хранить данные на удаленном сервере SQL Server, вам необходимо передать весь контент файла через сеть. В общем, существует два способа:

• вы передаете данные на удаленный сервер;
• удаленный сервер считывает данные из вашего локального файла.

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

• реализация функции "upload", которая передает данные по частям, объединяет их на стороне сервера и сохраняет в БД;
• упаковка XML-содержимого на стороне клиента и распаковка и хранение на стороне сервера;
• сочетание вышеуказанных методов.

Ответ 5

@xmlstring вводится xml

INSERT INTO @Temptbl           
   SELECT CONVERT(varchar(12), e.query('./Name/text()')) LogNumber             
   FROM @xmlstring.nodes('//RootNode/Root') AS n(e)     

select Temptbl     

Ответ 6

Вы можете записать хранимую процедуру для вставки большого файла xml в базу данных. В этой хранимой процедуре вы можете отправить свой XML файл в виде nvarchar (max). Обратите внимание, что nvarchar (max) принимает до 8000 символов.

В вашем коде основной поток состоит в том, что вы передаете xml из потока файлов, что является очень дорогостоящим делом.

Если ваш файл имеет большой размер от nvarchar (max), вы можете использовать больше входных параметров в своей хранимой процедуре и затем объединить эти входные параметры, чтобы вставить одно значение в виде XML файла.

Ответ 7

Похоже, что тип параметра SqlXml принимает XmlReader в качестве входа.

Как читатель на основе потока, это должно устранить ограничение памяти, которое вы испытываете.

using (SqlCommand cmd = new SqlCommand())
{
    SqlParameter sqlParameter = cmd.CreateParameter();
    sqlParameter.Direction = System.Data.ParameterDirection.Input;
    sqlParameter.Value = new System.Data.SqlTypes.SqlXml(xmlReader);
    ...
}

Ответ 8

Я думаю, вам следует создать пакет SSIS для импорта ваших XML файлов в базу данных SQL Server. Затем, если вам нужно использовать С#, вы можете выполнить этот пакет программно. Он будет более эффективным, и он работает как прелесть.

Ответ 9

Храните XML файлы в FileTable.

Ответ 10

1) Создайте хранимую процедуру для вставки в таблицу.
2) Передайте свой xml как varchar (max) из кода С#.

ИЛИ

2) Разбейте файл в кусках.
3) Выполните SP с XML как параметр.

Ответ 11

Пожалуйста, используйте объемный загрузчик sql xml http://msdn.microsoft.com/en-us/library/ms171806.aspx