Question Exporting/Importing Schema and Data IN XML format to share info between 2 systems

davemoss

New member
Joined
Mar 1, 2010
Messages
2
Programming Experience
10+
Let me start by describing the situation.

We have 2 companies within the same organisation that need to share the schema and data for a small set of a DB.

We have been trying to come up with a way of shaing this data using XML as the 2 SQL Servers cannot see each other and never will.

We have generated the Schema XML and Data XML but when we use the XMLBulkLoad in .Net the tables are not created and therefore no data inserted but it throws no errors!!

Here is the Schema XML

<sql:relationship name="FK_PartTypeParameterValue_PartTypeParameter" parent="PartTypeParameter" child="PartTypeParameterValue" parent-key="partTypeParameterId" child-key="partTypeParameterId" /> <sql:relationship name="FK_PartTypeParentLink_PartType" parent="PartType" child="PartTypeParentLink" parent-key="partTypeId" child-key="partTypeId" /> <sql:relationship name="FK_PartTypeParentLink_PartType1" parent="PartType" child="PartTypeParentLink" parent-key="partTypeId" child-key="partTypeParentId" /> <sql:relationship name="FK_PartType_PartCategory" parent="PartCategory" child="PartType" parent-key="partCategoryId" child-key="partCategoryId" /> <sql:relationship name="FK_PartType_PartClass" parent="PartClass" child="PartType" parent-key="partClassId" child-key="partClassId" /></xsd:appinfo>

<xsd:element name="PartType" sql:relation="PartType" sql:key-fields="partTypeId"> <xsd:complexType> <xsd:attribute name="partTypeId" type="xsd:int" /> <xsd:attribute name="partType" type="xsd:string" /> <xsd:attribute name="partCategoryId" type="xsd:int" /> <xsd:attribute name="partClassId" type="xsd:int" /> </xsd:complexType> </xsd:element> <xsd:element name="PartClass" sql:relation="PartClass" sql:key-fields="partClassId"> <xsd:complexType> <xsd:attribute name="partClassId" type="xsd:int" /> <xsd:attribute name="partClass" type="xsd:string" /> </xsd:complexType> </xsd:element> <xsd:element name="PartCategory" sql:relation="PartCategory" sql:key-fields="partCategoryId"> <xsd:complexType> <xsd:attribute name="partCategoryId" type="xsd:int" /> <xsd:attribute name="partCategory" type="xsd:string" /> </xsd:complexType> </xsd:element> <xsd:element name="PartTypeParentLink" sql:relation="PartTypeParentLink"> <xsd:complexType> <xsd:attribute name="partTypeId" type="xsd:int" /> <xsd:attribute name="partTypeParentId" type="xsd:int" /> </xsd:complexType> </xsd:element> <xsd:element name="PartTypeParameter" sql:relation="PartTypeParameter" sql:key-fields="partTypeParameterId"> <xsd:complexType> <xsd:attribute name="partTypeParameterId" type="xsd:int" /> <xsd:attribute name="partTypeTypeId" type="xsd:int" /> <xsd:attribute name="partTypeTypeParameter" type="xsd:string" /> </xsd:complexType> </xsd:element> <xsd:element name="PartTypeParameterValue" sql:relation="PartTypeParameterValue" sql:key-fields="partTypeParameterValueId"> <xsd:complexType> <xsd:attribute name="partTypeParameterValueId" type="xsd:int" /> <xsd:attribute name="partTypeParameterId" type="xsd:int" /> <xsd:attribute name="partTypeTypeParameterValue" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:choice></xsd:complexType>
And here is the data XML

<ROOT><PartType partTypeId="1" partType="9100" partCategoryId="1" partClassId="3" /> <PartType partTypeId="3" partType="NMD 100 Dispenser" partCategoryId="3" partClassId="1" /> <PartType partTypeId="4" partType="NMD 100 Cassette" partCategoryId="6" partClassId="1" /> <PartCategory partCategoryId="1" partCategory="ATM" /> <PartCategory partCategoryId="5" partCategory="Receipt Roll" /> <PartCategory partCategoryId="6" partCategory="Cassette" /> <PartClass partClassId="1" partClass="Repairable" /> <PartClass partClassId="3" partClass="Renewable" /> <PartTypeParentLink partTypeId="1" /> <PartTypeParentLink partTypeId="3" partTypeParentId="1" /> <PartTypeParentLink partTypeId="4" partTypeParentId="3" /> <PartTypeParameter partTypeParameterId="1" partTypeId="4" partTypeParameter="Position" /> <PartTypeParameter partTypeParameterId="2" partTypeId="4" partTypeParameter="Denomination" /> <PartTypeParameterValue partTypeParameterValueId="1" partTypeParameterId="1" partTypeParameterValue="A" /> <PartTypeParameterValue partTypeParameterValueId="2" partTypeParameterId="1" partTypeParameterValue="B" /> <PartTypeParameterValue partTypeParameterValueId="3" partTypeParameterId="2" partTypeParameterValue="10" /> <PartTypeParameterValue partTypeParameterValueId="4" partTypeParameterId="2" partTypeParameterValue="20" /></ROOT>

Any help would be greatly appreciated as it is driving us nuts now, or is there an easier/better way to do this, remember that the 2 SQL Servers cannot see each other and never will. The Schema and data XML are currently shared via email.
 
Please use Code tags to make your data legible when posting.

I'm going to assume you haven't set SchemaGen to True.

Check out section E. in this msdn article: XML Bulk Load Examples (SQLXML 4.0)

XML Bulk Load can optionally generate the tables if they do not exist before bulk loading. Setting the SchemaGen property of the SQLXMLBulkLoad object to TRUE does this.
 
Sorry about the layout I was in a bit of a rush when posting.

We have the schema Gen set to true.

I have a feeling that there may be something wrong with the XML itself but can't seem to pinpoint it myself and as bulkload doesn't throw an error it makes finding the problem even more difficult.
 
Back
Top