title: Sql Bulk insert XML format file with double quotes in terminator
body: <p>I'm trying to insert some data into a table from a csv document which has all of the fields delimited with ""</p>
  
  <p>ie.</p>
  
  <pre><code> APPLICANTID,NAME,CONTACT,PHONENO,MOBILENO,FAXNO,EMAIL,ADDR1,ADDR2,ADDR3,STATE,POSTCODE
   "3","Snoop Dogg","Snoop Dogg","411","","","","411 High Street","USA 
   ","","USA", "1111" "4","LL Cool J","LL Cool J","","","","","5 King
   Street","","","USA","1111"
  </code></pre>
  
  <p>I am using an xml format file to try and overcome the "" delimiters as I believe I would have to update the data again after importing to remove the inital " if it did not.</p>
  
  <p>My format file looks like the following:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
   &lt;RECORD&gt;
    &lt;FIELD ID="1" xsi:type="NCharTerm" TERMINATOR='",' MAX_LENGTH="12"/&gt;
    &lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="9" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="11" xsi:type="CharTerm" TERMINATOR=',"' COLLATION="Latin1_General_CI_AS"/&gt;
    &lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r\n" COLLATION="Latin1_General_CI_AS"/&gt;
   &lt;/RECORD&gt;
   &lt;ROW&gt;
    &lt;COLUMN SOURCE="1" NAME="APPLICANTID" xsi:type="SQLINT"/&gt;
    &lt;COLUMN SOURCE="2" NAME="NAME" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="3" NAME="CONTACT" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="4" NAME="PHONENO" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="5" NAME="MOBILENO" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="6" NAME="FAXNO" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="7" NAME="EMAIL" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="8" NAME="ADDR1" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="9" NAME="ADDR2" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="10" NAME="ADDR3" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="11" NAME="STATE" xsi:type="SQLNVARCHAR"/&gt;
    &lt;COLUMN SOURCE="12" NAME="POSTCODE" xsi:type="SQLCHAR"/&gt;
   &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;
  </code></pre>
  
  <p>and I am running the import with the following:</p>
  
  <pre><code>BULK INSERT [PracticalDB].dbo.applicant 
  FROM 'C:\temp.csv'
  WITH (KEEPIDENTITY, FORMATFILE='C:\temp.xml', FIRSTROW = 2)
  </code></pre>
  
  <p>I am getting the error:</p>
  
  <blockquote>
    <p>Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error
    (type mismatch or invalid character for the specified codepage) for
    row 2, column 1 (APPLICANTID).</p>
  </blockquote>
  
  <p>for all of the rows.</p>
  
  <p>I have tried various different combinations for the terminator including using:</p>
  
  <pre><code>TERMINATOR="&amp;quot;,"
  TERMINATOR="\","
  TERMINATOR='","
  TERMINATOR='\","
  </code></pre>
  
  <p>and none of them seem to work.</p>
  
  <p>Is there a correct way to escape the " so that it will be parsed correctly, assuming that that is my problem here.</p>