question

Dave Moss avatar image
Dave Moss asked

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

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

xmlimport-dataschemaexport-data
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Fatherjack avatar image
Fatherjack answered

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?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Moss avatar image Dave Moss commented ·
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.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
I'm adding a new answer so that you get it notified to you ...
0 Likes 0 ·
endizhupani avatar image
endizhupani answered
Another way to go about this is to use the command line versions of [xSQL Schema Compare][1] and [xSQL Data Compare][2]. 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][3]). 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. [1]: http://www.xsql.com/products/sql_server_schema_compare/?utm_source=pragmatic&utm_medium=articles&utm_campaign=xsql [2]: http://www.xsql.com/products/sql_server_data_compare/?utm_source=pragmatic&utm_medium=articles&utm_campaign=xsql [3]: http://blog.xsql.com/2016/12/database-synchronization-in-software.html#CommandLine
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.