title: Importing Large XML file into SQL 2.5Gb
body: <p>Hi I am trying to import a large XML file into a table on my sql server (2014)</p>
  
  <p>I have used the code below for smaller files and thought it would be ok as this is a once off,  I kicked it off yesterday and the query was still running when I came into work today so this is obviously the wrong route.</p>
  
  <p>here is the code.</p>
  
  <pre><code>CREATE TABLE files_index_bulk
  (
  Id INT IDENTITY PRIMARY KEY,
  XMLData XML,
  LoadedDateTime DATETIME
  )
  
  
  INSERT INTO files_index_bulk(XMLData, LoadedDateTime)
  SELECT CONVERT(XML, BulkColumn, 2) AS BulkColumn, GETDATE() 
  FROM OPENROWSET(BULK 'c:\scripts\icecat\files.index.xml', SINGLE_BLOB) AS x;
  
  
  SELECT * FROM files_index_bulk
  </code></pre>
  
  <p>Can anyone point out another way of doing this please ive looked around at importing large files and it keeps coming back to using bulk. which I already am.</p>
  
  <p>thanks in advance.</p>
  
  <p>here is the table I am using I want to pull all the data into. </p>
  
  <pre><code>USE [ICECATtesting]
  GO
  
  /****** Object:  Table [dbo].[files_index]    Script Date: 28/04/2017 20:10:44 
  ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  SET ANSI_PADDING ON
  GO
  
  CREATE TABLE [dbo].[files_index](
      [Product_ID] [int] NULL,
      [path] [varchar](100) NULL,
      [Updated] [varchar](50) NULL,
      [Quality] [varchar](50) NULL,
      [Supplier_id] [int] NULL,
      [Prod_ID] [varchar](1) NULL,
      [Catid] [int] NULL,
      [On_Market] [int] NULL,
      [Model_Name] [varchar](250) NULL,
      [Product_View] [int] NULL,
      [HighPic] [varchar](1) NULL,
      [HighPicSize] [int] NULL,
      [HighPicWidth] [int] NULL,
      [HighPicHeight] [int] NULL,
      [Date_Added] [varchar](150) NULL
  ) ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  </code></pre>
  
  <p>and here is a snippit of the xml file.</p>
  
  <p>
  </p>
  
  <pre><code>&lt;ICECAT-interface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://data.icecat.biz/xsd/files.index.xsd"&gt;
    &lt;files.index Generated="20170427010009"&gt;
    &lt;file path="export/level4/EN/11.xml" Product_ID="11" Updated="20170329110432" Quality="SUPPLIER" Supplier_id="2" Prod_ID="PS300E-03YNL-DU" Catid="151" On_Market="0" Model_Name="Satellite 3000-400" Product_View="587591" HighPic="" HighPicSize="0" HighPicWidth="0" HighPicHeight="0" Date_Added="20050627000000"&gt;
    &lt;/file&gt;
    &lt;file path="export/level4/EN/12.xml" Product_ID="12" Updated="20170329110432" Quality="ICECAT" Supplier_id="7" Prod_ID="91.42R01.32H" Catid="151" On_Market="0" Model_Name="TravelMate  740LF" Product_View="40042" HighPic="http://images.icecat.biz/img/norm/high/12-31699.jpg" HighPicSize="19384" HighPicWidth="170" HighPicHeight="192" Date_Added="20050627000000"&gt;
    &lt;/file&gt;
    &lt;file path="export/level4/EN/13.xml" Product_ID="13" Updated="20170329110432" Quality="SUPPLIER" Supplier_id="2" Prod_ID="PP722E-H390W-NL" Catid="151" On_Market="0" Model_Name="Portégé 7220CT / NW2" Product_View="37021" HighPic="http://images.icecat.biz/img/norm/high/13-31699.jpg" HighPicSize="27152" HighPicWidth="280" HighPicHeight="280" Date_Added="20050627000000"&gt;
    &lt;/file&gt;
  </code></pre>