SQLXMLBULKLOAD won't work with my XSD

Question

This is due to this relationship, which doesn't appear to be supported:

I can't change the format of the XML, as this comes from a third-party. There are two sections in their XML that contain RateRegister, for example:

<wse:ImportRateRegisters>
  <wse:RateRegister Tier="0" Cumulative="3213.500" Advance="60.900" />
  <wse:RateRegister Tier="1" Cumulative="193.950" Advance="59.700" />
</wse:ImportRateRegisters>
<wse:ImportTierRegisters>
  <wse:RateRegister Tier="0" Cumulative="3251.600" />
  <wse:RateRegister Tier="1" Cumulative="351.000" />
</wse:ImportTierRegisters>

When I try to annotate an XSD to support this relationship I am stuck. I can define the relationship between ImportRateRegisters/ ImportTierRegisters and Device, but as soon as I try to add RateRegister to the mix it stops working.

I can add the relationship from RateRegister to either ImportRateRegisters or ImportTierRegisters but not both at the same time. I create both relationships:

<sql:relationship name="RateRegisterToImportRateRegisters" parent="ImportRateRegisters" parent-key="ImportRateRegisters_Id" child="RateRegister" child-key="ImportRateRegisters_Id" />
<sql:relationship name="RateRegisterToImportTierRegisters" parent="ImportTierRegisters" parent-key="ImportTierRegisters_Id" child="RateRegister" child-key="ImportTierRegisters_Id" />

But when I get to the RateRegister I can only add one of these relationships, e.g.:

  <xs:element name="RateRegister" msdata:Prefix="wse" sql:relation="RateRegister" sql:relationship="RateRegisterToImportRateRegisters">

The error I get with this is:

Schema: the parent/child table of the relationship on 'RateRegister' does not match

If I try and add both relationships I get a duplicate attribute error.

Am I wasting my time here, can the SQLXMLBULKLOAD utility even cope with this sort of hierarchy?

Answer 1

I finally managed to get this to work, so I am going to post my answer in the hope that it helps someone else with a similar issue. I'm not convinced this is the best solution, as it ends up creating "link tables" that I don't really want, but it works.

In the database I create tables to hold this part of the structure:

CREATE TABLE ImportRateRegisters (
    RRegisterId INT IDENTITY(1,1) PRIMARY KEY,
    DeviceId INT FOREIGN KEY REFERENCES Device(DeviceId));

CREATE TABLE ImportTierRegisters (
    TRegisterId INT IDENTITY(1,1) PRIMARY KEY,
    DeviceId INT FOREIGN KEY REFERENCES Device(DeviceId));

CREATE TABLE RRateRegister (
    Tier INT,
    Cumulative NUMERIC(19,2),
    Advance NUMERIC(19,2),
    RRegisterId INT FOREIGN KEY REFERENCES ImportRateRegisters(RRegisterId));

CREATE TABLE TRateRegister (
    Tier INT,
    Cumulative NUMERIC(19,2),
    Advance NUMERIC(19,2),
    TRegisterId INT FOREIGN KEY REFERENCES ImportTierRegisters(TRegisterId));

First I need to create an element for the RateRegister, hooking it up to either the RRateRegister or TRateRegister table, I override this further down in the XSD, but I need to have something here as a placeholder or I get an error when bulk loading.

  <xs:element name="RateRegister" msdata:Prefix="wse" sql:relation="RRateRegister" sql:relationship="RRateRegisterToImportRateRegisters">
    <xs:complexType>
      <xs:attribute name="Tier" form="unqualified" type="xs:string" sql:field="Tier" />
      <xs:attribute name="Cumulative" form="unqualified" type="xs:string" sql:field="Cumulative" />
      <xs:attribute name="Advance" form="unqualified" type="xs:string" sql:field="Advance" />
    </xs:complexType>
  </xs:element>

When I come to the part in the core XSD that caused issues I refer back to the generic RateRegister, but override the relation/ relationship:

      <xs:element name="ImportRateRegisters" msdata:Prefix="wse" minOccurs="0" maxOccurs="unbounded" sql:relation="ImportRateRegisters" sql:relationship="ImportRateRegistersToDevice">
        <xs:complexType>
          <xs:sequence>
            <xs:element ref="RateRegister" minOccurs="0" maxOccurs="unbounded" sql:relation="RRateRegister" sql:relationship="RRateRegisterToImportRateRegisters"/>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
      <xs:element name="ImportTierRegisters" msdata:Prefix="wse" minOccurs="0" maxOccurs="unbounded" sql:relation="ImportTierRegisters" sql:relationship="ImportTierRegistersToDevice">
        <xs:complexType>
          <xs:sequence>
            <xs:element ref="RateRegister" minOccurs="0" maxOccurs="unbounded" sql:relation="TRateRegister" sql:relationship="TRateRegisterToImportTierRegisters" />
          </xs:sequence>
        </xs:complexType>
      </xs:element>

...and that's about it. When I load the data it gets pushed to the correct RateRegister table, and it all appears to be linked together correctly.