I'm using vbscript to perform an XMLBulkLoad into SQL Server 2008. My upload is triggered by the following:
objBL.execute "schedules.xsd", "scheduletest.xml"
This is triggering correctly. However, when I try to upload with my schema as is, I get the error:
XML SAX Parser: Data mapping to column 'ActivityID' was already found in the data. Make sure that no two schema definitions map to the same column.
Data goes in if I change the schema to use the <xs:choice> tags, but then anything within those tags does not go into the table and only two rows are generated with no ActivityIDs, no Start and no End - all are NULL.
I need anything within those tags to generate a new row in the database, with the same values as before it. So with my current test xml, it would generate:
booking-date, employee-id, level, planning-unit-id, activity-id, time-end, time-start,
2015-05-02 00:00:00, 1345, 8000, 1001, 2, 2015-05-02 20:30:00, 2015-05-02 13:00:00
2015-05-02 00:00:00, 1345, 8000, 1001, 1053, 2015-05-02 12:00:00, 2015-05-02 07:00:00
2015-05-01 00:00:00, 1350, 8000, 1001, 1053, 2015-05-01 11:00:00, 2015-05-01 07:00:00
I think I need to alter my XSD to allow this, but I simply can't see how. Any suggestions?
My XSD is:
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="schedules" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="schedule" maxOccurs="unbounded" minOccurs="0" sql:relation="Merge_Schedule">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:dateTime" name="booking-date" sql:field="bookingdate" sql:datatype="dateTime"/>
<xs:element type="xs:integer" name="employee-id" sql:field="EmployeeID" sql:datatype="int"/>
<xs:element type="xs:integer" name="level" sql:field="LevelID" sql:datatype="int"/>
<xs:element type="xs:integer" name="planning-unit-id" sql:field="PlanningUnitID" sql:datatype="int"/>
<xs:element name="schedule-blocks" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="schedule-block" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<!-- <xs:choice maxOccurs="unbounded" minOccurs="0"> -->
<xs:element type="xs:integer" name="activity-id" sql:field="ActivityID" sql:datatype="int"/>
<xs:element type="xs:dateTime" name="time-end" sql:field="finish" sql:datatype="dateTime"/>
<xs:element type="xs:dateTime" name="time-start" sql:field="start" sql:datatype="dateTime"/>
<!-- </xs:choice> -->
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
My XML is:
<?xml version="1.0" encoding="UTF-8"?>
<schedules type="array">
<schedule>
<booking-date type="date">2015-05-02 00:00:00</booking-date>
<employee-id type="integer">1345</employee-id>
<level type="integer">8000</level>
<planning-unit-id type="integer">1001</planning-unit-id>
<schedule-blocks type="array">
<schedule-block>
<activity-id>2</activity-id>
<time-end>2015-05-02 20:30:00</time-end>
<time-start>2015-05-02 13:00:00</time-start>
</schedule-block>
<schedule-block>
<activity-id>1053</activity-id>
<time-end>2015-05-02 12:00:00</time-end>
<time-start>2015-05-02 07:00:00</time-start>
</schedule-block>
</schedule-blocks>
</schedule>
<schedule>
<booking-date type="date">2015-05-01 00:00:00</booking-date>
<employee-id type="integer">1350</employee-id>
<level type="integer">8000</level>
<planning-unit-id type="integer">1001</planning-unit-id>
<schedule-blocks type="array">
<schedule-block>
<activity-id>1053</activity-id>
<time-end>2015-05-01 11:00:00</time-end>
<time-start>2015-05-01 07:00:00</time-start>
</schedule-block>
</schedule-blocks>
</schedule>
</schedules>
