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

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

more ▼

asked Feb 26 '10 at 09:07 AM in Default

Dave Moss gravatar image

Dave Moss
13 1 1 1

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

2 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 '10 at 01:25 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

Thanks, I will have a look at the FTP replication. The schema should not change very often. It is so annoying that this is not a simple task using XML, we have tried with a simple set of xml that was gathered from an example on the net but we just can't seem to get ours to work.
Mar 02 '10 at 05:55 AM Dave Moss
(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 '10 at 10:49 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x136
x62
x33
x27

asked: Feb 26 '10 at 09:07 AM

Seen: 2352 times

Last Updated: Feb 26 '10 at 09:07 AM