none
bcp to xml file failing with column name that has spaces RRS feed

  • Question

  • If I have a Customers database with a column name of "First Name" that has a space in it, so it looks like this Customers.[First Name], the bcp export to an xml file is failing with this message:

    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.

    BCP call:

    C:\...>"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" MyDatabase
    .dbo.Customers format nul -T -N -x -f Customers.xml

    Does anybody know how to get this to work?
    Wednesday, December 23, 2009 5:58 PM

All replies

  • Could you show part of your format file, including the "First Name" column?

    RLF
    Wednesday, December 23, 2009 6:16 PM
  • Your comment has led me to dig a little deeper so I will try to present the problem as simply as possible. 

    Table 1:

    CREATE TABLE [dbo].[Customers]
    (
         [CustomerId] [int] IDENTITY(1,1) NOT NULL,
         [Name] [varchar](255) NOT NULL,
         [First Name] [varchar](255) NOT NULL
    ) ON [PRIMARY]

    And then

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" MyDatabase.dbo.Customers format nul -T -N -x -f Customers.xml

    will succeed but check out the xml and for column and field 3, instead of the name being "First Name", it only stores the First in column and sticks the Name in field 3 under collation.

    <?xml version="1.0"?>
    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <RECORD>
      <FIELD ID="1" xsi:type="NativeFixed" LENGTH="4"/>
      <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Name"/>
     </RECORD>
     <ROW>
      <COLUMN SOURCE="1" NAME="CustomerId" xsi:type="SQLINT"/>
      <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="3" NAME="First" xsi:type="SQLVARYCHAR"/>
     </ROW>
    </BCPFORMAT>

    If you delete and recreate with additional columns after the column name with a space in like this:

    CREATE TABLE [dbo].[Customers]
    (
         [CustomerId] [int] IDENTITY(1,1) NOT NULL,
         [Name] [varchar](255) NOT NULL,
         [First Name] [varchar](255) NOT NULL,
         [LastName] [varchar](255) NOT NULL
    ) ON [PRIMARY]

    And then

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" MyDatabase.dbo.Customers format nul -T -N -x -f Customers.xml

    You'll get this
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 4 in xml format file.

    Wednesday, December 23, 2009 8:28 PM
  • Can anyone assist?
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Saturday, January 9, 2010 8:23 PM
    Answerer
  • Hi everybody,

    This topic is a little old but if anyone wants to know what to do if you get this error,

    well I can give you an answer, first of all you have to understand that Bulk Copy Program (BCP)

    was made for READ EXTERNAL FILES, the XML is a result of data types of the columns of your

    destination table, so in any case you will have to open it and edit it, (except if the File you are

    going to read has the same structure than your table) so the solution it would be this:

    *MAKE A COPY OF YOUR DESTINATION TABLE WITHOUT SPACES IN THE COLUMNS

    *EXECUTE BCP AIMING TO YOUR COPY TABLE

    *DROP THE COPY TABLE

    -Greetings and hope this be useful

    Wednesday, June 10, 2015 3:06 PM
  • Thanks man, I'd manually imported a text file w/o headers so all the columns were titled [Column 1] etc. Scripted table as/create to/ new query window, removed the spaces, ran the script. Pointed the bcp at the new table and we're all good. Thanks

    ===== Thad

    Thursday, May 18, 2017 11:44 AM