title: SQL Server bulk insert XML format file
body: <p>I am trying to load a fixed width text file using <code>Bulk Insert</code> and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.</p>
  
  <p>Error</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>Msg 4857, Level 16, State 1, Line 16
  Line 4 in format file "\\PATHC\addr.xml": Attribute "type" could not be specified for this type.</code></pre>
  </div>
  </div>
  </p>
  
  <p>SQL Server Table</p>
  
  <pre class="lang-sql prettyprint-override"><code>create table [dbo].[raw_addr](
      address_number  varchar(max),
      addr_linel  varchar(max),
      addr_line2  varchar(max),
      street_no   varchar(max),
      street_name varchar(max),
      street_type varchar(max),
      locality    varchar(max),
      [state]     varchar(max),
      country     varchar(max),
      postcode    varchar(max)
  );
  </code></pre>
  
  <p>SQL Server Bulk Insert</p>
  
  <pre class="lang-sql prettyprint-override"><code>BULK INSERT [dbo].[raw_addr] FROM '\\PATH\addr.txt'
  WITH (
      FORMATFILE = '\\PATH\addr.xml', 
      ROWTERMINATOR='\r\n');
  </code></pre>
  
  <p>XML Code:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><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="CharFixed" LENGTH="9"  /&gt;
      &lt;FIELD ID="2"  xsi:type="CharFixed" LENGTH="50" /&gt;
      &lt;FIELD ID="3"  xsi:type="CharFixed" LENGTH="50" /&gt;
      &lt;FIELD ID="4"  xsi:type="CharFixed" LENGTH="10" /&gt;
      &lt;FIELD ID="5"  xsi:type="CharFixed" LENGTH="50" /&gt;
      &lt;FIELD ID="6"  xsi:type="CharFixed" LENGTH="10" /&gt;
      &lt;FIELD ID="7"  xsi:type="CharFixed" LENGTH="30" /&gt;
      &lt;FIELD ID="8"  xsi:type="CharFixed" LENGTH="3"  /&gt;
      &lt;FIELD ID="9"  xsi:type="CharFixed" LENGTH="30" /&gt;
      &lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\r\n" LENGTH="4" /&gt;
  &lt;/RECORD&gt;
  &lt;ROW&gt;
      &lt;COLUMN SOURCE="1"  NAME="address_number"   xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="2"  NAME="addr_linel"       xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="3"  NAME="addr_line2"       xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="4"  NAME="street_no"        xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="5"  NAME="street_name"      xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="6"  NAME="street_type"      xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="7"  NAME="locality"         xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="8"  NAME="state"            xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="9"  NAME="country"          xsi:type="SQLNVARCHAR" /&gt;
      &lt;COLUMN SOURCE="10" NAME="postcode"         xsi:type="SQLNVARCHAR" /&gt;
  &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>Example TXT extract</p>
  
  <pre class="lang-txt prettyprint-override"><code>001044057C/- XXXXXX XXXXXXX                                                                                                                                                                                                                       0000
  001295978XXXX                                                                                                                                                                                                                                     0000
  0013974311                                                                                                                                                                                                                                        0000
  00124485712                                                                                                                                                                                                                                       0000
  0012390352                                                                                                                                                                                                                                        0000
  0014720345                                                                                                                                                                                                                                        0000
  0014792876                                                                                                                                                                                                                                        0000
  000986525                                                                                                              ARABANOO                                                    GRENFELL                      NSW                              2810
  000986589                                                  PO BOX XX                                                                                                               KEMPSEY                       NSW                              2440
  000740594                                                  'RUSSLEY'                                                                                                               ABERDEEN                      NSW                              2336
  000311516                                                                                                              BUNYARA                                                     ABERDEEN                      NSW                              2336
  000298796                                                                                                              CAMBRIA                                                     ABERDEEN                      NSW                              2336
  000540611                                                                                                              HALCOMBE                                                    ABERDEEN                      NSW                              2336
  000513112                                                  'LARK HILL'                                                                                                             ABERDEEN                      NSW                              2336
  000612955                                                                                                              FAN                                               HILL      ABERMAIN                      NSW                              2326
  001109439                                                                                                              BRAYWOOD                                                    ADAMINABY                     NSW                              2630
  000460864                                                                                                              TARA                                                        ADAMINABY                     NSW                              2630
  000315297                                                                                                                                                                          ADAMSTOWN                     NSW                              2289
  000470057                                                                                                              COORUMBENE                                                  ADELONG                       NSW                              2729
  000491941                                                                                                              NACKI                                                       ADELONG                       NSW                              2729
  </code></pre>