Correct BCP syntax

Question

I am a first time BCP user. I have an XML file that I need to import into a new table in SQL Server. This is using SQL Server 2008 and BCP version 10.50.16. I read through the documentation but I get 370,000 errors! (This is a very large file). When I open the error log, I just see a bunch of question marks.

About this XML file: it does use a new line to delineate rows, however, it uses the space character to separate fields. So I'm not sure if that is what causes it to fail. The failure message is:

BCP copy in failedI wish it were more descriptive. Is there a way to get a more detailed error message?

Otherwise, here is my command:

d:\SQL Tables\data>bcp Development.dbo.wbl_zSkywardEnrollment2 in zSkywardEnroll
ment.xml -fSE_format.fmt -m50 -eseErrorLog -b100 -t0x20 -T -F107

All of these files are in this same directory. I know it says to specify full file path, but since they're all in this same directory and also I saw another example where they didn't specify directories I thought this would suffice.

I have tried this without the format file also, doing it interactively but get same errors.

This is my format file:

10.0
32
1       SQLINT              0       4       " "    1     SCHOOL_YEAR                                ""
2       SQLNCHAR            2       20      " "    2     ENTITY_ID                                  SQL_Latin1_General_CP1_CI_AS
3       SQLINT              0       4       " "    3     TERM_NBR                                   ""
4       SQLINT              1       4       " "    4     SCHD_HST_GRAD_YEAR                         ""
5       SQLNCHAR            2       60      " "    5     OTHER_ID                                   SQL_Latin1_General_CP1_CI_AS
6       SQLNUMERIC          1       19      " "    6     GRAD_YR                                    ""
7       SQLNCHAR            2       120     " "    7     LAST_NAME                                  SQL_Latin1_General_CP1_CI_AS
8       SQLNCHAR            2       60      " "    8     FIRST_NAME                                 SQL_Latin1_General_CP1_CI_AS
9       SQLNCHAR            2       60      " "    9     MIDDLE_NAME                                SQL_Latin1_General_CP1_CI_AS
10      SQLDATETIME         1       8       " "    10    BIRTHDATE                                  ""
11      SQLNCHAR            2       4       " "    11    GENDER                                     SQL_Latin1_General_CP1_CI_AS
12      SQLNCHAR            2       20      " "    12    RACE_CODE                                  SQL_Latin1_General_CP1_CI_AS
13      SQLNCHAR            2       40      " "    13    DISTRICT_CODE                              SQL_Latin1_General_CP1_CI_AS
14      SQLBIT              1       1       " "    14    X_SPECIAL_EDUCATION                        ""
15      SQLBIT              1       1       " "    15    X_GIFTED_TALENTED                          ""
16      SQLBIT              1       1       " "    16    X_SECTION_504                              ""
17      SQLBIT              1       1       " "    17    X_MIGRANT                                  ""
18      SQLBIT              1       1       " "    18    X_IEP                                      ""
19      SQLBIT              1       1       " "    19    X_IEP_ACCOM                                ""
20      SQLBIT              1       1       " "    20    X_ESL                                      ""
21      SQLNCHAR            2       4       " "    21    STUDENT_STATUS                             SQL_Latin1_General_CP1_CI_AS
22      SQLINT              0       4       " "    22    STUDENT_ID                                 ""
23      SQLINT              1       4       " "    23    ENG_PROF                                   ""
24      SQLNCHAR            2       60      " "    24    ALPHAKEY                                   SQL_Latin1_General_CP1_CI_AS
25      SQLNCHAR            2       20      " "    25    SCHOOL_ID                                  SQL_Latin1_General_CP1_CI_AS
26      SQLNUMERIC          1       19      " "    26    MN_EDE_NBR                                 ""
27      SQLNCHAR            2       20      " "    27    LANGUAGE_CODE                              SQL_Latin1_General_CP1_CI_AS
28      SQLINT              1       4       " "    28    ADVISOR                                    ""
29      SQLBIT              1       1       " "    29    MN_LIMITED_ENGLISH                         ""
30      SQLNCHAR            2       20      " "    30    TYPE_STUDENT_ID                            SQL_Latin1_General_CP1_CI_AS
31      SQLNCHAR            2       20      " "    31    CY_TEAM_SCHD_ID                            SQL_Latin1_General_CP1_CI_AS
32      SQLNCHAR            2       20      " "    32    HOMEROOM_NUMBER                            SQL_Latin1_General_CP1_CI_AS

This is my SQL table:

CREATE TABLE [dbo].[wbl_zSkywardEnrollment2](
    [SCHOOL_YEAR] [int] NOT NULL,
    [ENTITY_ID] [nvarchar](10) NOT NULL,
    [TERM_NBR] [int] NOT NULL,
    [SCHD_HST_GRAD_YEAR] [int] NULL,
    [OTHER_ID] [nvarchar](30) NULL,
    [GRAD_YR] numeric(19,0) NULL,
    [LAST_NAME] [nvarchar](60) NOT NULL,
    [FIRST_NAME] [nvarchar](30) NULL,
    [MIDDLE_NAME] [nvarchar](30) NULL,
    [BIRTHDATE] datetime NULL,
    [GENDER] [nvarchar](2) NULL,
    [RACE_CODE] [nvarchar](10) NULL,
    [DISTRICT_CODE] [nvarchar](20) NULL,
    [X_SPECIAL_EDUCATION] bit NULL,
    [X_GIFTED_TALENTED] bit NULL,
    [X_SECTION_504] bit NULL,
    [X_MIGRANT] bit NULL,
    [X_IEP] bit NULL,
    [X_IEP_ACCOM] bit NULL,
    [X_ESL] bit NULL,
    [STUDENT_STATUS] [nvarchar](2) NULL,
    [STUDENT_ID] [int] NOT NULL,
    [ENG_PROF] [int] NULL,
    [ALPHAKEY] [nvarchar](30) NOT NULL,
    [SCHOOL_ID] [nvarchar](10) NULL,
    [MN_EDE_NBR] [numeric](19, 0) NULL,
    [LANGUAGE_CODE] [nvarchar](10) NULL,
    [ADVISOR] [int] NULL,
    [MN_LIMITED_ENGLISH] bit NULL,
    [TYPE_STUDENT_ID] [nvarchar](10) NULL,
    [CY_TEAM_SCHD_ID] [nvarchar](10) NULL,
    [HOMEROOM_NUMBER] [nvarchar](10) NOT NULL
) ON [PRIMARY]

And I have tried this starting with row 1. I am curious which line should BCP start on? Line 1 of this XML starts with Schema and ElementType info. But on line 107 is where rs:data section starts.

The first part of this XML file:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'

    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'

    xmlns:rs='urn:schemas-microsoft-com:rowset'

    xmlns:z='#RowsetSchema'>

<s:Schema id='RowsetSchema'>

    <s:ElementType name='row' content='eltOnly'>

        <s:AttributeType name='c0' rs:name='SCHOOL_YEAR' rs:number='1'>

            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c1' rs:name='ENTITY_ID' rs:number='2'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c2' rs:name='TERM_NBR' rs:number='3'>

            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c3' rs:name='SCHD_HST_GRAD_YEAR' rs:number='4' rs:nullable='true'>

            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c4' rs:name='OTHER_ID' rs:number='5' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='24'/>

        </s:AttributeType>

        <s:AttributeType name='c5' rs:name='GRAD_YR' rs:number='6' rs:nullable='true'>

            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='15' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c6' rs:name='LAST_NAME' rs:number='7'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='60' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c7' rs:name='FIRST_NAME' rs:number='8' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='30'/>

        </s:AttributeType>

        <s:AttributeType name='c8' rs:name='MIDDLE_NAME' rs:number='9' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='30'/>

        </s:AttributeType>

        <s:AttributeType name='BIRTHDATE' rs:name='BIRTHDATE' rs:number='10' rs:nullable='true' rs:write='true'>

            <s:datatype dt:type='date' dt:maxLength='6' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='GENDER' rs:name='GENDER' rs:number='11' rs:nullable='true' rs:write='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='2'/>

        </s:AttributeType>

        <s:AttributeType name='c11' rs:name='RACE_CODE' rs:number='12' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6'/>

        </s:AttributeType>

        <s:AttributeType name='c12' rs:name='DISTRICT_CODE' rs:number='13' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='14'/>

        </s:AttributeType>

        <s:AttributeType name='c13' rs:name='X_SPECIAL_EDUCATION' rs:number='14' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c14' rs:name='X_GIFTED_TALENTED' rs:number='15' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c15' rs:name='X_SECTION_504' rs:number='16' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c16' rs:name='X_MIGRANT' rs:number='17' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c17' rs:name='X_IEP' rs:number='18' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c18' rs:name='X_IEP_ACCOM' rs:number='19' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c19' rs:name='X_ESL' rs:number='20' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c20' rs:name='STUDENT_STATUS' rs:number='21' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='2'/>

        </s:AttributeType>

        <s:AttributeType name='c21' rs:name='STUDENT_ID' rs:number='22'>

            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c22' rs:name='ENGL_PROF' rs:number='23' rs:nullable='true'>

            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='ALPHAKEY' rs:name='ALPHAKEY' rs:number='24' rs:write='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='22' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c24' rs:name='SCHOOL_ID' rs:number='25'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='c25' rs:name='MN_EDE_NBR' rs:number='26' rs:nullable='true'>

            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='15' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c26' rs:name='LANGUAGE_CODE' rs:number='27' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6'/>

        </s:AttributeType>

        <s:AttributeType name='c27' rs:name='ADVISOR' rs:number='28' rs:nullable='true' rs:write='true'>

            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c28' rs:name='MN_LIMITED_ENGLISH' rs:number='29' rs:nullable='true'>

            <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/>

        </s:AttributeType>

        <s:AttributeType name='c29' rs:name='TYPE_STUDENT_ID' rs:number='30' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6'/>

        </s:AttributeType>

        <s:AttributeType name='c30' rs:name='CY_TEAM_SCHD_ID' rs:number='31' rs:nullable='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6'/>

        </s:AttributeType>

        <s:AttributeType name='c31' rs:name='HOMEROOM_NUMBER' rs:number='32'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>

        </s:AttributeType>

        <s:extends type='rs:rowbase'/>

    </s:ElementType>

</s:Schema>

<rs:data>

    <z:row c0='2004' c1='057' c2='0' c3='2010' c4='671590' c5='2010' c6='AANENSON' c7='SHELLI' c8='A' BIRTHDATE='1992-07-01'

         GENDER='F' c11='5' c12='' c13='False' c14='True' c15='False' c16='False' c17='False' c18='False' c19='False'

         c20='I' c21='12' c22='7' ALPHAKEY='AANENSHE000' c24='057' c25='624000671590' c26='011' ADVISOR='0' c28='False'

         c29='R' c30='' c31=''/>

    <z:row c0='2004' c1='057' c2='0' c3='2010' c4='671590' c5='2010' c6='AANENSON' c7='SHELLI' c8='A' BIRTHDATE='1992-07-01'

         GENDER='F' c11='5' c12='' c13='False' c14='True' c15='False' c16='False' c17='False' c18='False' c19='False'

         c20='I' c21='12' c22='7' ALPHAKEY='AANENSHE000' c24='057' c25='624000671590' c26='011' ADVISOR='0' c28='False'

         c29='R' c30='' c31=''/>

    <z:row c0='2004' c1='057' c2='0' c3='2010' c4='671590' c5='2010' c6='AANENSON' c7='SHELLI' c8='A' BIRTHDATE='1992-07-01'

         GENDER='F' c11='5' c12='' c13='False' c14='True' c15='False' c16='False' c17='False' c18='False' c19='False'

         c20='I' c21='12' c22='7' ALPHAKEY='AANENSHE000' c24='057' c25='624000671590' c26='011' ADVISOR='0' c28='False'

         c29='R' c30='' c31=''/>

    <z:row c0='2004' c1='057' c2='0' c3='2010' c4='671590' c5='2010' c6='AANENSON' c7='SHELLI' c8='A' BIRTHDATE='1992-07-01'

         GENDER='F' c11='5' c12='' c13='False' c14='True' c15='False' c16='False' c17='False' c18='False' c19='False'

         c20='I' c21='12' c22='7' ALPHAKEY='AANENSHE000' c24='057' c25='624000671590' c26='011' ADVISOR='0' c28='False'

         c29='R' c30='' c31=''/>

    <z:row c0='2004' c1='057' c2='0' c3='2010' c4='671590' c5='2010' c6='AANENSON' c7='SHELLI' c8='A' BIRTHDATE='1992-07-01'

         GENDER='F' c11='5' c12='' c13='False' c14='True' c15='False' c16='False' c17='False' c18='False' c19='False'

         c20='I' c21='12' c22='7' ALPHAKEY='AANENSHE000' c24='057' c25='624000671590' c26='011' ADVISOR='0' c28='False'

         c29='R' c30='' c31=''/>

    <z:row c0='2004' c1='057' c2='0' c3='2010' c4='671590' c5='2010' c6='AANENSON' c7='SHELLI' c8='A' BIRTHDATE='1992-07-01'

         GENDER='F' c11='5' c12='' c13='False' c14='True' c15='False' c16='False' c17='False' c18='False' c19='False'

         c20='I' c21='12' c22='7' ALPHAKEY='AANENSHE000' c24='057' c25='624000671590' c26='011' ADVISOR='0' c28='False'

         c29='R' c30='' c31=''/>

Answer 1

You can't use bcp for this. The best that bcp can give you is an import from a file with comma/tab/etc. separated values. All references that you see to xml in the bcp documentation refer to the format file, not to the actual data to import.

There are actually two ways of doing this. The easiest is to use SQL Server Integration Services for this. This can help you further.

You could also do this by reading the xml file into SQL server. Then use sp_xml_preparedocument to parse the file. Finally, use OPENXML in combination with INSERT to import the data.