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

Question

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

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.

Answer 1

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?

Answer 2

0

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

answered Feb 26 '10 at 10:49 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

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 '10 at 12:27 PM Dave Moss

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

Mar 01 '10 at 01:15 PM Fatherjack ♦♦

(comments are locked)

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

Follow this question

Related Questions