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-mic
rosoft-com
:mapping-s
chema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="FK_tbl_SailingSchedu
le_Routing
Details_tb
l_SailingS
chedule"
parent="tbl_SailingSchedul
e"
parent-key="SailingSchedul
e_id"
child="tbl_SailingSchedule
_RoutingDe
tails"
child-key="RoutingDetails_
SailingId"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ScheduleDetails" sql:relation="tbl_SailingS
chedule" sql:key-fields="SailingSch
edule_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_S
ailingSche
dule_Routi
ngDetails_
tbl_Sailin
gSchedule"
sql:relation="tbl_SailingS
chedule_Ro
utingDetai
ls" sql:key-fields="RoutingDet
ails_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_Sa
ilingSched
ule_id] DEFAULT (newid()),
[VesselVoyageID] [int] NULL ,
[VesselName] [nvarchar] (255) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[IMONumber] [int] NULL ,
[Voyage] [nvarchar] (10) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[CarrierSCAC] [char] (4) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[SCAC] [char] (4) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[AmsFlag] [char] (1) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[CFSOrigin] [char] (5) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[CFSDestination] [char] (5) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[SailingSchedule_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tbl_SailingSchedule_Routi
ngDetails]
(
[RoutingDetails_id] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_tbl_SailingSchedule_Ro
utingDetia
ls_Routing
Detials_id
] DEFAULT (newid()),
[RoutingDetails_SailingId]
[uniqueidentifier] NOT NULL ,
[StageQualifier] [int] NULL ,
[Transportmode] [int] NULL ,
[TransportName] [nvarchar] (255) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[IMONumber] [int] NULL ,
[Origin] [char] (5) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[Destination] [char] (5) COLLATE SQL_Latin1_General_Pref_CP
1_CI_AS NULL ,
[ETD] [datetime] NULL ,
[ETA] [datetime] NULL ,
PRIMARY KEY CLUSTERED
(
[RoutingDetails_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_tbl_SailingSchedule_Ro
utingDetai
ls_tbl_Sai
lingSchedu
le] 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</Receiv
erID>
<Password>TradingPartners<
/Password>
<Type>SCHEDULE</Type>
<Version>1.0.7</Version>
<EnvelopeID>NACA-10708</En
velopeID>
</ScheduleEnvelope>
-->
<ScheduleDetails>
<VesselVoyageID>11499412</
VesselVoya
geID>
<VesselName>KOOKABURRA 1</VesselName>
<IMONumber>9319571</IMONum
ber>
<Voyage>611</Voyage>
<CarrierSCAC>USLB</Carrier
SCAC>
<SCAC>NAQA</SCAC>
<AmsFlag>N</AmsFlag>
<CFSOrigin>USATL</CFSOrigi
n>
<CFSDestination>AUADL</CFS
Destinatio
n>
<RoutingDetails>
<StageQualifier>1</StageQu
alifier>
<Transportmode>2</Transpor
tmode>
<TransportName>RAIL</Trans
portName>
<IMONumber>
</IMONumber>
<Origin>USATL</Origin>
<ETD>2006-10-23</ETD>
<Destination>USLAX</Destin
ation>
<ETA>2006-10-31</ETA>
</RoutingDetails>
<RoutingDetails>
<StageQualifier>3</StageQu
alifier>
<Transportmode>4</Transpor
tmode>
<TransportName>KOOKABURRA 1</TransportName>
<IMONumber>9319571</IMONum
ber>
<Origin>USLAX</Origin>
<ETD>2006-11-02</ETD>
<Destination>AUMEL</Destin
ation>
<ETA>2006-11-22</ETA>
</RoutingDetails>
<RoutingDetails>
<StageQualifier>5</StageQu
alifier>
<Transportmode>5</Transpor
tmode>
<TransportName>TBN</Transp
ortName>
<IMONumber>
</IMONumber>
<Origin>AUMEL</Origin>
<ETD>2006-11-22</ETD>
<Destination>AUADL</Destin
ation>
<ETA>2006-11-25</ETA>
</RoutingDetails>
<RoutingDetails>
<StageQualifier>6</StageQu
alifier>
<Transportmode>1</Transpor
tmode>
<TransportName>TRUCK</Tran
sportName>
<IMONumber>
</IMONumber>
<Origin>AUADL</Origin>
<ETD>2006-11-25</ETD>
<Destination>AUADL</Destin
ation>
<ETA>2006-11-25</ETA>
</RoutingDetails>
</ScheduleDetails>
<ScheduleDetails>
<VesselVoyageID>11499786</
VesselVoya
geID>
<VesselName>MAERSK DALE</VesselName>
<IMONumber>9232577</IMONum
ber>
<Voyage>608</Voyage>
<CarrierSCAC>HLCU</Carrier
SCAC>
<SCAC>NAQA</SCAC>
<AmsFlag>N</AmsFlag>
<CFSOrigin>USATL</CFSOrigi
n>
<CFSDestination>NZAKL</CFS
Destinatio
n>
<RoutingDetails>
<StageQualifier>1</StageQu
alifier>
<Transportmode>1</Transpor
tmode>
<TransportName>TRUCK</Tran
sportName>
<IMONumber>
</IMONumber>
<Origin>USATL</Origin>
<ETD>2006-10-23</ETD>
<Destination>USSAV</Destin
ation>
<ETA>2006-10-28</ETA>
</RoutingDetails>
<RoutingDetails>
<StageQualifier>2</StageQu
alifier>
<Transportmode>4</Transpor
tmode>
<TransportName>MAERSK DALE</TransportName>
<IMONumber>9232577</IMONum
ber>
<Origin>USSAV</Origin>
<ETD>2006-10-28</ETD>
<Destination>NZAKL</Destin
ation>
<ETA>2006-11-17</ETA>
</RoutingDetails>
<RoutingDetails>
<StageQualifier>6</StageQu
alifier>
<Transportmode>1</Transpor
tmode>
<TransportName>TRUCK</Tran
sportName>
<IMONumber>
</IMONumber>
<Origin>NZAKL</Origin>
<ETD>2006-11-17</ETD>
<Destination>NZAKL</Destin
ation>
<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_S
AMPLE_SAIL
_SCHED2Wit
hData.XML"
);
System.Xml.XmlNodeList soDetailsNodes = soDoc.DocumentElement.Sele
ctNodes("S
cheduleDet
ails");
foreach (System.Xml.XmlNode soNode in soDetailsNodes)
{
System.Xml.XmlNode soNewNode = soDoc.CreateElement("Saili
ngSchedule
_id");
soNewNode.InnerText= System.Guid.NewGuid().ToSt
ring();
soNode.AppendChild(soNewNo
de);
}
soDoc.Save(@"..\..\SMALL_S
AMPLE_SAIL
_SCHED2Wit
hData_WITH
ID.XML");
try
{
SQLXMLBULKLOADLib.SQLXMLBu
lkLoad3Cla
ss objBL = new SQLXMLBULKLOADLib.SQLXMLBu
lkLoad3Cla
ss();
objBL.ConnectionString = "Provider=sqloledb;server=
sql.jaslon
.local;dat
abase=MLW3
;integrate
d security=SSPI";
objBL.ErrorLogFile = "error.xml";
objBL.KeepIdentity = true;
objBL.Execute (@"..\..\SailingsheduleToJ
asDb_Mappi
ng.xml", @"..\..\SMALL_SAMPLE_SAIL_
SCHED2With
Data.XML")
;
}
catch(Exception e)
{
Console.WriteLine(e.ToStri
ng());
}
}
==========================
==========
==========
==========
==========
==========
=======
ERROR:
System.Runtime.InteropServ
ices.COMEx
ception (0x80004005): No data was provided for column 'RoutingDetails_SailingId'
on table 'tbl_SailingSchedule_Routi
ngDetails'
, and this column cannot contain NULL values.
at SQLXMLBULKLOADLib.SQLXMLBu
lkLoad3Cla
ss.Execute
(String bstrSchemaFile, Obje
ct vDataFile)
at SailingSchedule.Class1.Ins
ertData() in d:\daniel documents and work\visual
studio projects\nacalogistics\sai
lingschedu
le\class1.
cs:line 66