I've been working with xsd for all of 3 weeks now and I am finally starting to understand the basics of how it works but I have found a problem in either the SQLXMLBulkLoad process or the way the xsd is structured.
<xsd:complexType name="AccountEntityType">
<xsd:sequence>
<xsd:element name="AccountEntityUniqueId" type="xsd:int"/>
<xsd:element name="FinancialTransactionEntity" type="FinancialTransactionEntityType"
minOccurs="0" maxOccurs="unbounded" sql:relation="FinancialTransactionEntity"
sql:relationship="AccountEntity_FinancialTransactionEntity"/>
</xsd:complexType>
<xsd:complexType name="FinancialTransactionEntityType">
<xsd:sequence>
<xsd:element name="FinancialTransactionEntityUniqueId" type="xsd:int"/>
<xsd:element name="FinancialTransaction_5000" type="FinancialTransaction_5000Type"
sql:relation="FinancialTransaction_5000"
sql:relationship="FinancialTransactionEntity_FinancialTransaction_5000"/>
<xsd:element name="PassengerTransportEntity" type="PassengerTransportEntityType"
minOccurs="0" maxOccurs="unbounded" sql:relation="PassengerTransportEntity"
sql:relationship="FinancialTransactionEntity_PassengerTransportEntity"/>
</xsd:complexType>
So there are several tables represented here AccountEntity, FinancialTransactionEntity, FinancialTransaction_5000, and PassengerTransportEntity.
The
AccountEntityUniqueId
is a field in all the tables and by the rules of SQLXMLBulkLoad and xsd is created when the AccountEntity is found and then is supposed to be passed into each of it's children who are supposed to pass it along to their children, etc, etc, etc. Tying all these records together. I have discovered that my value isn't being propagated properly, the top level, AccountEntity starts at 1 and passes that to its child FinancialTransactionEntity. But, FinancialTransactionEntity passes (value - 1) to FinancialTransaction_5000 and PassengerTransportEntity. I see the same thing crop up inside PassengerTransportEntity because it reduces the FinancialTransactionEntityUniqueId it sends to its children by one as well.
Has anyone else seen this and is there a solution? I really don't want to have to say
PassengerTransportEntity.FinancialTransactionEntityUniqueId = ChildTable.FinancialTransactionEntityUniqueId + 1
in my WHERE clauses when I shouldn't have to.
Yes, the relationships all include these UniqueId fields in both the parent and child keys all the way down the nesting.