BULK import of binary in xml column

• mssqlnoob

  Ten Centuries

  Points: 1245

  More actions

  November 11, 2013 at 12:38 am

  #286130

  from http://technet.microsoft.com/en-us/library/ms191184.aspx

  D. Specifying the field terminator explicitly using a format file

  Sample Data File

  FF FE 3C 00 3F 00 78 00-6D 00 6C 00 20 00 76 00 *..<.?.x.m.l. .v.*

  65 00 72 00 73 00 69 00-6F 00 6E 00 3D 00 22 00 *e.r.s.i.o.n.=.".*

  31 00 2E 00 30 00 22 00-20 00 65 00 6E 00 63 00 *1...0.". .e.n.c.*

  6F 00 64 00 69 00 6E 00-67 00 3D 00 22 00 75 00 *o.d.i.n.g.=.".u.*

  74 00 66 00 2D 00 31 00-36 00 22 00 3F 00 3E 00 *t.f.-.1.6.".?.>.*

  3C 00 72 00 6F 00 6F 00-74 00 3E 00 A2 4F 9C 76 *<.r.o.o.t.>..O.v*

  0C FA 77 E4 80 00 89 00-00 06 90 06 91 2E 9B 2E *..w.............*

  99 34 A2 34 86 00 83 02-92 20 7F 02 4E C5 E4 A3 *.4.4..... ..N...*

  34 B2 B7 B3 B7 FE F8 FF-F8 00 3C 00 2F 00 72 00 *4.........<./.r.*

  6F 00 6F 00 74 00 3E 00-00 00 00 00 7A EF BB BF *o.o.t.>.....z...*

  3C 3F 78 6D 6C 20 76 65-72 73 69 6F 6E 3D 22 31 *<?xml version="1*

  2E 30 22 20 65 6E 63 6F-64 69 6E 67 3D 22 75 74 *.0" encoding="ut*

  66 2D 38 22 3F 3E 3C 72-6F 6F 74 3E E4 BE A2 E7 *f-8"?><root>....*

  9A 9C EF A8 8C EE 91 B7-C2 80 C2 89 D8 80 DA 90 *................*

  E2 BA 91 E2 BA 9B E3 92-99 E3 92 A2 C2 86 CA 83 *................*

  E2 82 92 C9 BF EC 95 8E-EA 8F A4 EB 88 B4 EB 8E *................*

  B7 EF BA B7 EF BF B8 C3-B8 3C 2F 72 6F 6F 74 3E *.........</root>*

  00 00 00 00 7A

  Sample Table

  USE practise;

  CREATE TABLE xTable (xCol xml);

  GO

  Sample Format File

  9.0

  1

  1 SQLBINARY 0 0 "\0\0\0\0z" 1 xCol ""

  BULK INSERT xTable

  FROM 'E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_features\db_features\bulk_import\examples_of_bulk\Xmltable.dat'

  WITH (FORMATFILE = 'E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_features\db_features\bulk_import\examples_of_bulk\Xmltable.fmt');

  GO

  I encounter the following error:

  Msg 4832, Level 16, State 1, Line 1

  Bulk load: An unexpected end of file was encountered in the data file.

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7330, Level 16, State 2, Line 1

  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  I try to use the bcp to generate the format file but it's still error.

  Here's how I generate the format file using bcp

  E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_featu

  res\db_features\bulk_import\examples_of_bulk>bcp practise..xTable format nul -f

  SampleData3_generated.txt -T -S user-PC\sqlexpress -t "\0\0\0\0z"

  Enter the file storage type of field xCol [nvarchar(max)]: varbin

  Invalid column type. Valid types are:

  <cr>: same type as DataServer column.

  c : varchar(max)

  T : text

  i : int

  s : smallint

  t : tinyint

  f : float

  m : money

  b : bit

  d : datetime

  x : varbinary(max)

  I : image

  D : smalldatetime

  r : real

  M : smallmoney

  n : numeric

  e : decimal

  w : nvarchar(max)

  W : ntext

  u : uniqueidentifier

  B : bigint

  V : sql_variant

  U : udt

  de : date

  te : time

  d2 : datetime2

  do : datetimeoffset

  Try again:

  Enter the file storage type of field xCol [nvarchar(max)]: x

  Enter prefix-length of field xCol [8]: 0

  Enter length of field xCol [0]:

  Enter field terminator [none]: \0\0\0\0z

  E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_featu

  res\db_features\bulk_import\examples_of_bulk>

  generated file looks like the following

  11.0

  1

  1 SQLBINARY 0 0 "\0\0\0\0z" 1 xCol ""

  BULK INSERT xTable

  FROM 'E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_features\db_features\bulk_import\examples_of_bulk\Xmltable.dat'

  WITH (FORMATFILE = 'E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_features\db_features\bulk_import\examples_of_bulk\SampleData3_generated.txt');

  GO

  Msg 4832, Level 16, State 1, Line 1

  Bulk load: An unexpected end of file was encountered in the data file.

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7330, Level 16, State 2, Line 1

  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  how should I resolve the error?

  thanks