Suppose I have a table like this:
CREATE TABLE [dbo].[TBL_XML]
(
[XmlFileID] [BIGINT] IDENTITY (1, 1) NOT NULL,
[FileName] [NVARCHAR](500) NULL,
[XmlData] [XML] NULL,
[DateCreated] [DATETIME] NOT NULL,
)
The method I am currently using to fill the table is this:
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = @"INSERT INTO [dbo].[TBL_XML]
( [XmlData] ,
[FileName] ,
[DateCreated]
)
VALUES (@XMLData, @FileName, GETDATE())";
using (var xmlReader = new XmlTextReader(new FileStream(item.XmlFileName, FileMode.Open)))
{
cmd.Parameters.Add("@FileName", SqlDbType.NVarChar, 500).Value = System.IO.Path.GetFileName(item.XmlFileName);
cmd.Parameters.Add(
new SqlParameter("@XMLData", SqlDbType.Xml)
{
Value = new SqlXml(xmlReader)
});
SetConnectionParameters(cmd);
cmd.ExecuteScalar());
}
}
But this will not work with very large XMLs because the whole file is loaded in memory and I get OutOfMemory exceptions.
What is the best approach to insert a large (>100MB) XML file into the XMLData column from a .net application running on a different machine than the server?
Bulk insert is out of the question since the SQL server will not have access to my XML file.
Below is one potential way to chunk this using only .NET. I have not tried executing this, but it should work.
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();
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With