I do not quite understand why my Bulk Load fails when I run it.  Here is all my information...

I handle the XMLBulkLoad object using VB.NET and here is the code...

Dim objBL As Object = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")

objBL.ConnectionString = "provider=SQLOLEDB;data source=server;database=db;integrated security=SSPI"

objBL.ErrorLogFile = "c:\error.log"

objBL.CheckConstraints = True

objBL.KeepIdentity = False

objBL.SGDropTables = True

objBL.SchemaGen = True

objBL.Execute(C:\schema.xsd", "C:\data.xml")

objBL = Nothing

Here is a sample of my XSD...This sample begins immediately below my annotations/appinfo section, which describes the relationships being created.

<xs:element name="Package" sql:is-constant="1">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="PackageHeader" sql:relation="FE_PackageHeader">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="PackageName" type="xs:string" />
              <xs:element minOccurs="0" name="Universe" type="xs:string" />
              <xs:element minOccurs="0" name="AsOfDate" type="xs:date" sql:datatype="dateTime" />
              <xs:element minOccurs="0" name="Version" type="xs:decimal" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element minOccurs="0" name="PackageBody" sql:relation="FE_PackageBody">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="InvestmentVehicle" sql:relation="FE_InvestmentVehicle" sql:relationship="PackageBody-InvestmentVehicle">
                <xs:complexType>
                  <xs:sequence>
                    <xs:choice maxOccurs="unbounded">
                      <xs:element minOccurs="0" name="Operation" sql:relation="FE_Operation" sql:relationship="InvestmentVehicle-Operation">
                        <xs:complexType>
                          <xs:sequence>
                            <xs:choice maxOccurs="unbounded">
                              <xs:element minOccurs="0" name="InvestmentVehicleName" type="xs:string" />
                              <xs:element minOccurs="0" name="ShareClassId" type="xs:string" />
                              <xs:element minOccurs="0" name="LegalType" type="xs:string" />
                              <xs:element minOccurs="0" name="OldestShareClass" sql:relation="FE_OldestShareClass" sql:relationship="Operation-OldestShareClass">
                                <xs:complexType>
                                  <xs:sequence>
                                    <xs:element minOccurs="0" name="Name" type="xs:string" />
                                    <xs:element minOccurs="0" name="Id" type="xs:string" sql:field="OldestShareClassId"/>
                                  </xs:sequence>
                                </xs:complexType>
                              </xs:element>

The Bulk Load operation runs fine up until it sees the "xs:choice" element.  Everything outside the xs:choice element gets imported just fine.  Everything nested within the "choice" element gets ignored by Bulk Upload.  If I comment out the choice element, I get an invalid cast error because XMLBulkLoad tries to do a sequential data import which means that input data is directed to the wrong table.

There has to be a way to get this to work!  Please help!

Submitted under: Microsoft SQL Server · XML · BCP ·  ·