relational data from xml

Posted by Beta033 on Stack Overflow See other posts from Stack Overflow or by Beta033
Published on 2010-04-14T22:55:39Z Indexed on 2010/04/15 2:03 UTC
Read the original article Hit count: 515

Our problem is this,

we have a relational database to store objects in tables. As any relational database could, several of these tables have multiple foreign keys pointing to other tables (all pretty normal stuff)

We've been trying to identify a solution to allow export of this relational data, ideally, only 1 of the objects in the model, to some sort of file (xml, text, ??). So it wouldn't be simple enough to just export 1 table as data stored in other tables would contribute to the complete model of the object. Something like the following picture:

alt text

Toward this, i've written a routine to export the structure by following the foreign key paths which exports something similar to the following.

<Tables>
  <TableA PK="1", val1, val2, val3>
  <TableC PK="1", FK_A="1", Val1, val2, val3>
  <TableC PK="2", FK_A="1", val1, val2, val3>
  <TableB PK="1", FK_A="1", FK_C="1", val1, val2, val3>
  <TableB PK="2", FK_A="1", FK_C="2", val1, val2, val3>
  <TableD PK="1", FK_B="1", FK_C="1", val1>
  <TableD PK="2", FK_B="2", FK_C="1", val1>
  </Tables>

However, given this structure, it cannot be placed into a heirarchial format (ie D2 is a child of C1 and B2; and B2 is a child of C2) Which in turn, makes my life very difficult when trying to identify a methodology to reimport (and reKey) these objects.

Has anybody done anything like this? how do you do it? are there tools or documentation on how this is best accomplished?

Thanks for your help.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about mapping