SQLXML bulkload - parent-child relationship and autogeneration of IDs. IDs not inherited.

I am trying to get a bulk-load statement to work using SQLXML.

I can insert the rows into the parent table fine, and the DB generates a value for the PK coulmn (using the Default).

I cannot get the child rows to be inserted because the FK column can't be null.  MS say this is by design (great) and provide no solution.

My tables have a constraint (FK) between them.

My source XML contains no ID.



However, an alternative I have done is to edit the XMl at runtime and insert a new node to make an ID for each parent.
The problem still exists, and it crashing saying that it cannot find a source for the child's FK column.

 
What I really want is for it to throw the parent data at the DB and have an ID auto-generated (using the DEFAULT) for the parent ID, and then have this propgate down to be the FK for the children.  However SQLXML is unable to do this (microsoft document this), but it is (apparently) able to propogate the FK down if the parent's ID is provided in the source data.  I'm trying this approach but it's failing.


Please advise

SCHEMA:
<?xml version="1.0" encoding="utf-8" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
      <xsd:annotation>
            <xsd:appinfo>
                  <sql:relationship name="FK_tbl_SailingSchedule_RoutingDetails_tbl_SailingSchedule"
                        parent="tbl_SailingSchedule"
                        parent-key="SailingSchedule_id"
                        child="tbl_SailingSchedule_RoutingDetails"
                        child-key="RoutingDetails_SailingId" />

            </xsd:appinfo>
      </xsd:annotation>

      <xsd:element name="ScheduleDetails" sql:relation="tbl_SailingSchedule" sql:key-fields="SailingSchedule_id" >
            <xsd:complexType>
                  <xsd:sequence>
                        <xsd:element name="SailingSchedule_id"        type="xsd:string" />
                        <xsd:element name="VesselVoyageID"        type="xsd:string" />
                        <xsd:element name="VesselName"        type="xsd:string" />
                        <xsd:element name="IMONumber"        type="xsd:string" />
                        <xsd:element name="Voyage"        type="xsd:string" />
                        <xsd:element name="CarrierSCAC"        type="xsd:string" />
                        <xsd:element name="SCAC"        type="xsd:string" />
                        <xsd:element name="AmsFlag"        type="xsd:string" />
                        <xsd:element name="CFSOrigin"        type="xsd:string" />
                        <xsd:element name="CFSDestination"        type="xsd:string" />
                         
                         <xsd:element name="RoutingDetails"
                              sql:relationship="FK_tbl_SailingSchedule_RoutingDetails_tbl_SailingSchedule"
                              sql:relation="tbl_SailingSchedule_RoutingDetails" sql:key-fields="RoutingDetails_id"
                         >
                         
                         
                              <xsd:complexType>
                                    <xsd:sequence maxOccurs="unbounded">                                    
                                          
                                          <xsd:element name="StageQualifier"        type="xsd:string" />
                                          <xsd:element name="Transportmode"        type="xsd:string" />
                                          <xsd:element name="TransportName"        type="xsd:string" />
                                          <xsd:element name="IMONumber"        type="xsd:string" />
                                          <xsd:element name="Origin"        type="xsd:string" />
                                          <xsd:element name="ETD"        type="xsd:string" />
                                          <xsd:element name="Destination"        type="xsd:string" />
                                          <xsd:element name="ETA"        type="xsd:string" />
                                    </xsd:sequence>
                              </xsd:complexType>
                        </xsd:element>
                         
                  </xsd:sequence>
            </xsd:complexType>
      </xsd:element>
 
</xsd:schema>




====================================================================

TABLES:


CREATE TABLE [tbl_SailingSchedule] (
     [SailingSchedule_id]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_tbl_SailingSchedule_SailingSchedule_id] DEFAULT (newid()),
     [VesselVoyageID] [int] NULL ,
     [VesselName] [nvarchar] (255) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [IMONumber] [int] NULL ,
     [Voyage] [nvarchar] (10) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [CarrierSCAC] [char] (4) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [SCAC] [char] (4) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [AmsFlag] [char] (1) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [CFSOrigin] [char] (5) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [CFSDestination] [char] (5) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
      PRIMARY KEY  CLUSTERED
     (
          [SailingSchedule_id]
     )  ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [tbl_SailingSchedule_RoutingDetails] (
     [RoutingDetails_id]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_tbl_SailingSchedule_RoutingDetials_RoutingDetials_id] DEFAULT (newid()),
     [RoutingDetails_SailingId] [uniqueidentifier] NOT NULL ,
     [StageQualifier] [int] NULL ,
     [Transportmode] [int] NULL ,
     [TransportName] [nvarchar] (255) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [IMONumber] [int] NULL ,
     [Origin] [char] (5) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [Destination] [char] (5) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
     [ETD] [datetime] NULL ,
     [ETA] [datetime] NULL ,
      PRIMARY KEY  CLUSTERED
     (
          [RoutingDetails_id]
     )  ON [PRIMARY] ,
     CONSTRAINT [FK_tbl_SailingSchedule_RoutingDetails_tbl_SailingSchedule] FOREIGN KEY
     (
          [RoutingDetails_SailingId]
     ) REFERENCES [tbl_SailingSchedule] (
          [SailingSchedule_id]
     ) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY]
GO



====================================================================





SOURCE DATA:



<Schedule  >
  <!--  <ScheduleEnvelope>
        <SenderID>NACA</SenderID>
        <ReceiverID>KNPROD</ReceiverID>
        <Password>TradingPartners</Password>
        <Type>SCHEDULE</Type>
        <Version>1.0.7</Version>
        <EnvelopeID>NACA-10708</EnvelopeID>
    </ScheduleEnvelope>
    -->
    <ScheduleDetails>
        <VesselVoyageID>11499412</VesselVoyageID>
        <VesselName>KOOKABURRA 1</VesselName>
        <IMONumber>9319571</IMONumber>
        <Voyage>611</Voyage>
        <CarrierSCAC>USLB</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>AUADL</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>2</Transportmode>
            <TransportName>RAIL</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-10-23</ETD>
            <Destination>USLAX</Destination>
            <ETA>2006-10-31</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>3</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>KOOKABURRA 1</TransportName>
            <IMONumber>9319571</IMONumber>
            <Origin>USLAX</Origin>
            <ETD>2006-11-02</ETD>
            <Destination>AUMEL</Destination>
            <ETA>2006-11-22</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>5</StageQualifier>
            <Transportmode>5</Transportmode>
            <TransportName>TBN</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>AUMEL</Origin>
            <ETD>2006-11-22</ETD>
            <Destination>AUADL</Destination>
            <ETA>2006-11-25</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>AUADL</Origin>
            <ETD>2006-11-25</ETD>
            <Destination>AUADL</Destination>
            <ETA>2006-11-25</ETA>
        </RoutingDetails>
    </ScheduleDetails>
    <ScheduleDetails>
        <VesselVoyageID>11499786</VesselVoyageID>
        <VesselName>MAERSK DALE</VesselName>
        <IMONumber>9232577</IMONumber>
        <Voyage>608</Voyage>
        <CarrierSCAC>HLCU</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>NZAKL</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-10-23</ETD>
            <Destination>USSAV</Destination>
            <ETA>2006-10-28</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>2</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>MAERSK DALE</TransportName>
            <IMONumber>9232577</IMONumber>
            <Origin>USSAV</Origin>
            <ETD>2006-10-28</ETD>
            <Destination>NZAKL</Destination>
            <ETA>2006-11-17</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>NZAKL</Origin>
            <ETD>2006-11-17</ETD>
            <Destination>NZAKL</Destination>
            <ETA>2006-11-17</ETA>
        </RoutingDetails>
    </ScheduleDetails>
</Schedule>



 

====================================================================

CODE
           


            static private void InsertData()
            {
                  //JAS_Generated_GUID
                  System.Xml.XmlDocument soDoc = new System.Xml.XmlDocument();
                  soDoc.Load(@"..\..\SMALL_SAMPLE_SAIL_SCHED2WithData.XML");
                  System.Xml.XmlNodeList soDetailsNodes = soDoc.DocumentElement.SelectNodes("ScheduleDetails");
                  foreach (System.Xml.XmlNode soNode in soDetailsNodes)
                  {
                        System.Xml.XmlNode soNewNode = soDoc.CreateElement("SailingSchedule_id");
                        soNewNode.InnerText= System.Guid.NewGuid().ToString();
                        soNode.AppendChild(soNewNode);      
                  }
                  soDoc.Save(@"..\..\SMALL_SAMPLE_SAIL_SCHED2WithData_WITHID.XML");
                  


                  try
                  {
                        SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
                        objBL.ConnectionString = "Provider=sqloledb;server=sql.jaslon.local;database=MLW3;integrated security=SSPI";
                        objBL.ErrorLogFile = "error.xml";
                        objBL.KeepIdentity = true;
                        objBL.Execute (@"..\..\SailingsheduleToJasDb_Mapping.xml",  @"..\..\SMALL_SAMPLE_SAIL_SCHED2WithData.XML");
                        
                  }
                  catch(Exception e)
                  {
                        Console.WriteLine(e.ToString());
                  }



            }





===================================================================================

ERROR:

System.Runtime.InteropServices.COMException (0x80004005): No data was provided for column 'RoutingDetails_SailingId' on table 'tbl_SailingSchedule_RoutingDetails', and this column cannot contain NULL values.
   at SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute(String bstrSchemaFile, Obje
ct vDataFile)
   at SailingSchedule.Class1.InsertData() in d:\daniel documents and work\visual
 studio projects\nacalogistics\sailingschedule\class1.cs:line 66