0

Is it possible to stream an XML file to a MS SQL Stored Procedure as a parameter from a C# application without having to load it into memory on the c# end?

The XML files i have are quite large, ~600MB with 2mil entries. Which as you can imagine, takes a bucket load of memory.

Once the SP on SQL inserts/updates values in the database from these files it has no further use for them and can be disposed.

SQL Example:

CREATE PROCEDURE [dbo].[AddAllFromList] 
(
    @XmlData XML
)
BEGIN
    Do something with @XmlData;
END;

C# Example

using (Stream XMLFileStream = new someFileInfo.OpenRead())
using (SqlConnection Connection = new SqlConnection(SQLConnectionString))
{
    var opensql = Connection.OpenAsync();
    SqlCommand sqlcommand = new SqlCommand("AddAllFromList", Connection)
    {
        CommandType = System.Data.CommandType.StoredProcedure,
        CommandTimeout = 360
    };
    sqlcommand.Parameters.Add("@XmlData", SqlDbType.Xml).Value = XMLFileStream;
try
{
    await sqlcommand.ExecuteNonQueryAsync()
}
catch
{
    Console.WriteLine("Failed");
}

Would something like this work? Does anyone have any success stories?

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Eddie Ted Crocombe
  • 110
  • 12
  • 1
    Iterate over the XML document and call the stored procedure multiple times, with the elements of the XML document? Store the file on a network location *(or even on the Database server)* and have your SP read it from there? Bulk load *(stage)* the XML file into a table and then have the SP read from the table? Or transform the XML to a multiple normalised structures, bulk load *(stage)* the data in normalised tables and have the SP read from there? But you can't stream data to the SP. – MatBailie Apr 17 '18 at 11:57
  • I'd suggest a different approach altogether - have your C# program place the XML file in a location that SQL Server can access, then kick off an SSIS package which processes it. Or configure the SSIS package to watch a folder location and trigger/process when the file appears. – alroc Apr 17 '18 at 13:12
  • @MatBailie, thanks for bringing up BulkCopy/BulkLoad, however unless taking say 100 items from the array at a time using a stream reader like XmlReader, the file still must be loaded into memory, however the footprint would be ALOT smaller. I wonder how BULKCOPY/BULKLOAD handles loading into multiple tables, from memory it does have a Mapping thingy-ma-bob (technical term used for describing something for which i have forgotten its name). – Eddie Ted Crocombe Apr 18 '18 at 03:44
  • @alroc, I have no experience with SSIS, is that a component of a licensed edition of SQL? I'm an amateur freeloader of SQL Express :) – Eddie Ted Crocombe Apr 18 '18 at 03:44
  • @EddieTedCrocombe yes, it requires something higher than Express Edition. If you're not using this for production work and your task otherwise fits the license, you can use Developer Edition which has SSIS. Otherwise, I agree w/ MatBailie that a bulk copy is likely your best bet for performance. – alroc Apr 18 '18 at 11:18

1 Answers1

2

With such a big load I'd suggest to do this in two steps anyway. First read this into a staging table, then do the rest from there.

If the file is located where SQL-Server has access you can load the XML from T-SQL directly.

This question asks for the upload of multiple XMLs (using a CURSOR), but you will see how to go.

This question addresses the export to XML, but there are several important things to know about the file's encoding.

This question covers some traps one might get into.

In general

  • C# uses unicode (2-byte-encoding) in any case for strings. Such a string can be passed over to SQL Server's NVARCHAR(MAX) type. If the XML is well-formed you can pass it directly to an XML typed variable.

  • SQL-Server is very limited in encodings. Very often such big files are stored with utf-8. The provided links give information about that.

  • SQL-Server is not running as you. If it is running on a different machine it will see other drives and paths as you see. And the rights may be different too.

  • Whether you load the XML with C# and pass it over to SQL-Server or if you use some command from T-SQL will be quite the same amout of data, which must be shipped from A to B.

  • If memory matters you might split this in parts.

Shnugo
  • 66,100
  • 9
  • 53
  • 114