title: Best way to insert large xml files into xml columns (on remote SQL Server)
body: <p>Suppose I have a table like this:</p>
  
  <pre><code>CREATE TABLE [dbo].[TBL_XML]
  (
     [XmlFileID]       [BIGINT] IDENTITY (1, 1) NOT NULL,
     [FileName]        [NVARCHAR](500) NULL,   
     [XmlData]         [XML] NULL,
     [DateCreated]     [DATETIME] NOT NULL,
  )
  </code></pre>
  
  <p>The method I am currently using to fill the table is this:</p>
  
  <pre><code>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());
      }
  }
  </code></pre>
  
  <p>But this will not work with very large XMLs because the whole file is loaded in memory and I get OutOfMemory exceptions.</p>
  
  <p>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?</p>
  
  <p>Bulk insert is out of the question since the SQL server will not have access to my XML file.</p>