x

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

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

   </p>

<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>

   </p>

<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>

 &lt;xsd:element name="PartClass" sql:relation="PartClass" 
              sql:key-fields="partClassId"&gt;
   &lt;xsd:complexType&gt;
     &lt;xsd:attribute name="partClassId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partClass" type="xsd:string" /&gt;
   &lt;/xsd:complexType&gt;
 &lt;/xsd:element&gt;      

 &lt;xsd:element name="PartCategory" sql:relation="PartCategory" 
              sql:key-fields="partCategoryId"&gt;
   &lt;xsd:complexType&gt;
     &lt;xsd:attribute name="partCategoryId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partCategory" type="xsd:string" /&gt;
   &lt;/xsd:complexType&gt;
 &lt;/xsd:element&gt;      

 &lt;xsd:element name="PartTypeParentLink"
              sql:relation="PartTypeParentLink"&gt;
   &lt;xsd:complexType&gt;
     &lt;xsd:attribute name="partTypeId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partTypeParentId" type="xsd:int" /&gt;
   &lt;/xsd:complexType&gt;
 &lt;/xsd:element&gt;

 &lt;xsd:element name="PartTypeParameter" 
              sql:relation="PartTypeParameter"
              sql:key-fields="partTypeParameterId"&gt;
   &lt;xsd:complexType&gt;
     &lt;xsd:attribute name="partTypeParameterId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partTypeTypeId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partTypeTypeParameter" type="xsd:string" /&gt;
   &lt;/xsd:complexType&gt;
 &lt;/xsd:element&gt;

 &lt;xsd:element name="PartTypeParameterValue" 
              sql:relation="PartTypeParameterValue"
              sql:key-fields="partTypeParameterValueId"&gt;
   &lt;xsd:complexType&gt;
     &lt;xsd:attribute name="partTypeParameterValueId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partTypeParameterId" type="xsd:int" /&gt;
     &lt;xsd:attribute name="partTypeTypeParameterValue" type="xsd:string" /&gt;
   &lt;/xsd:complexType&gt;
 &lt;/xsd:element&gt;

</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.

more ▼

asked Feb 26, 2010 at 09:07 AM in Default

avatar image

Dave Moss
13 1 1 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If a full backup/restore solution isnt applicable - you only want selected tables shipped to the other site - then I would recommend you review something like SQL Data Compare from Red Gate (14 day free trial here). It lets you script the database schema (or parts thereof) and the data in the objects selected into a folder on your system (network share/local HDD etc) and you could then zip + encrypt, email and then run SQL Data Compare at the other end to get the other location up to date.

Its not an ideal (read 'recommended') solution, transporting data by email has obvious risks and something managed by the SQL engine would be preferred - FTP replication etc but if they are not connected then it could be a struggle and bring its own risks. Check out http://www.replicationanswers.com/default.asp to see if you can get something sorted.

I would say bcp to import/export the data but you need to cope with schema changes too. Are they so frequent that the schema needs updating EVERY time?

more ▼

answered Mar 01, 2010 at 01:25 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

When you say they cannot see each other - how will the XML get from one to the other? Are you using a common network location to host the XML output? Are they on the same network/domain/subnet??

You could bcp, or use SSIS maybe?

You may be able to get a third party application like SQL Compare/SQL Data Compare from RedGate to do this for you, it depends on how disconnected the servers are ...

more ▼

answered Feb 26, 2010 at 10:49 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

The servers are on different networks and although not the ideal solution at the moment the schema and data xml are emailed between the companies.

I find it odd that SQL Server provides you with a solution to export the data and the schema in XML format but no easy way to import it.

Mar 01, 2010 at 12:27 PM Dave Moss

I'm adding a new answer so that you get it notified to you ...

Mar 01, 2010 at 01:15 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Another way to go about this is to use the command line versions of xSQL Schema Compare and xSQL Data Compare. If you download the desktop versions of both of these tools, you also get the command line versions. You can generate XML files which contain the configuration for the synchronization process and are used as arguments by the command line tools (to see how to do that check out this post). After this, you can create a batch file and schedule it to be executed periodically to keep the two databases synchronized.

DISCLAIMER: I'm affiliated to xSQL.

more ▼

answered May 22, 2017 at 09:39 AM

avatar image

endizhupani
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x204
x72
x44
x36

asked: Feb 26, 2010 at 09:07 AM

Seen: 3195 times

Last Updated: May 22, 2017 at 09:41 AM

Copyright 2018 Redgate Software. Privacy Policy