By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

reconstructing an XML tree from RDB data

lwwhite
P: 16
I'm not sure if this discussion is a better fit for the Access or XML forum and I don't want to double-post, so I'm starting in Access because you've been so helpful to me here.

I am preparing a presentation comparing using a relational database vs. a native XML database within a content management system to manage XML-based documentation. The point I am trying to make is how difficult it can be to reconstruct the XML hierarchy/tree once the data has been burst into an RDB structure. I am using a simple family tree as my example and I have built a sample database (in Access) with one table that records each family member's name and other statistics (age, etc.) and a second table that joins family members and relationships:

family
------
f_id
name
age

rships
------
r_id
f_id
f_id2
rship

(Where you choose two family members and the relationship between them. This is the most efficient way I could think of to represent this recursive data.)

Now, if I were to try to recreate the family tree in a XML-type tree view, how would I go about that? I don't want any specific code, just a general approach that a typical RDB might take to reconstruct the tree, if it is possible to explain that in any meaningful way without getting technical and specific. I know that it would be schema-dependent, complicated, and require maintenance should the family tree structure change, etc. That's exactly the point I'm trying to make.

And I should point out that I'm still feelin' the love for Access and RDBs in general...just trying to make the case that it's not necessarily the best tool for XML. I don't want to use this post to kick off a debate about NXD vs RDB. I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.
Jun 25 '07 #1
Share this Question
Share on Google+
15 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I'm not sure if this discussion is a better fit for the Access or XML forum and I don't want to double-post, so I'm starting in Access because you've been so helpful to me here.

I am preparing a presentation comparing using a relational database vs. a native XML database within a content management system to manage XML-based documentation. The point I am trying to make is how difficult it can be to reconstruct the XML hierarchy/tree once the data has been burst into an RDB structure. I am using a simple family tree as my example and I have built a sample database (in Access) with one table that records each family member's name and other statistics (age, etc.) and a second table that joins family members and relationships:

family
------
f_id
name
age

rships
------
r_id
f_id
f_id2
rship
Ok, I don't think you need the r_id here. The two f_id's are the primary key (composite Primary key) as follows:

rships
f_id1 (Compostite Primary key - foreign key referencing primary key of family)
f_id2 (Compostite Primary key - foreign key referencing primary key of family)
rship

This is actually a join table but instead of joining a many to many relationship between two different tables it is joining a many to many relationship with itself.

Now, if I were to try to recreate the family tree in a XML-type tree view, how would I go about that? I don't want any specific code, just a general approach that a typical RDB might take to reconstruct the tree, if it is possible to explain that in any meaningful way without getting technical and specific. I know that it would be schema-dependent, complicated, and require maintenance should the family tree structure change, etc. That's exactly the point I'm trying to make.

And I should point out that I'm still feelin' the love for Access and RDBs in general...just trying to make the case that it's not necessarily the best tool for XML. I don't want to use this post to kick off a debate about NXD vs RDB. I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.
Building a Tree View from a database point of view is something you would never do. However, a tree view for something like this would be difficult to implement. A tree is where you start with a parent and each parent can have one or more children and each of those children can themselves have one or more children. Your problem arises when you have partners, husbands, wives, etc. This make things extremely complicated.

However, I'm sure there is a way of programmatically representing this. I'm going to ask some of the experts from the other forums to have a look at this.
Jun 29 '07 #2

pbmods
Expert 5K+
P: 5,821
I think one problem, conceptually, with the situation is that there are no rules defining relationships. For example, if `f_id` 1 is the son of `f_id` 2, then `f_id` 2 is also `f_id` 1's father. And if `f_id` 3 is `f_id` 2's wife, then `f_id` 1 is also the son of `f_id` 3, even though `f_id` 1 and `f_id` 2 would represent one 'unit' if you were to create a visual representation of the family tree.

I think a more manageable way to structure the database would be to add a `father_id` and a `mother_id` field to the `family` table, since we know each person has exactly one of those (except for `f_id` 0 and `f_id` 1; we'll call them... um, Sadam and Steve).

I could see, if you wanted to create a more btree-like setup, creating a `siblings` view where you would match up `f_id`s with the same `father_id` and `mother_id`.
Jun 29 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I think one problem, conceptually, with the situation is that there are no rules defining relationships. For example, if `f_id` 1 is the son of `f_id` 2, then `f_id` 2 is also `f_id` 1's father. And if `f_id` 3 is `f_id` 2's wife, then `f_id` 1 is also the son of `f_id` 3, even though `f_id` 1 and `f_id` 2 would represent one 'unit' if you were to create a visual representation of the family tree.

I think a more manageable way to structure the database would be to add a `father_id` and a `mother_id` field to the `family` table, since we know each person has exactly one of those (except for `f_id` 0 and `f_id` 1; we'll call them... um, Sadam and Steve).

I could see, if you wanted to create a more btree-like setup, creating a `siblings` view where you would match up `f_id`s with the same `father_id` and `mother_id`.
You also have the issue where siblings can have the same father but different mother.

Essentially database wise this would be one table

tblPerson
PersonID (Primary key)
FatherID (Foreign key referencing the Primary key of this table)
MotherID (Foreign key referencing the Primary key of this table)

Using this you would never actually record a sibling relationship. Instead siblings would be worked out using queries on the data.
Jun 29 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You also have the issue where siblings can have the same father but different mother.

Essentially database wise this would be one table

tblPerson
PersonID (Primary key)
FatherID (Foreign key referencing the Primary key of this table)
MotherID (Foreign key referencing the Primary key of this table)

Using this you would never actually record a sibling relationship. Instead siblings would be worked out using queries on the data.
Looking at this the problem with it from a query point of view is you would have to create a new instance of the table in the query for each parent child relationship.
Expand|Select|Wrap|Line Numbers
  1. SELECT P1.PersonID, P2.PersonID As FatherOf, P3.PersonID As MotherOf
  2. FROM ((tblPerson P1 INNER JOIN tblPerson P2
  3. ON P1.PersonID = P2.FatherID)
  4. INNER JOIN tblPerson P3
  5. ON P1.PersonID = P3.MotherID)
  6.  
And this is just a basic one level relationship. After this it would get pretty complicated.
Jun 29 '07 #5

Expert 10K+
P: 11,448
Relational databases and trees don't like each other. You need at least an
extension to SQL to handle this matter. Have a look how Oracle did it.

kind regards,

Jos
Jun 29 '07 #6

danp129
Expert 100+
P: 321
I don't like XML much and don't use it alot so I'm having a hard time picturing how the mother/father (or father/father adoption heh) would look like. Do you have an example XML file for a family tree starting from grandparents on down that your goal is to rebuild from the RDB?
Jun 29 '07 #7

Purple
Expert 100+
P: 404
Hi all,

Just to add a MSSQL perspective;

take a look at this which details microsoft's best practice on XML in SQL Server 2005..

Regards Purple
Jun 29 '07 #8

Motoma
Expert 2.5K+
P: 3,235
I'm not sure if this discussion is a better fit for the Access or XML forum and I don't want to double-post, so I'm starting in Access because you've been so helpful to me here.

I am preparing a presentation comparing using a relational database vs. a native XML database within a content management system to manage XML-based documentation. The point I am trying to make is how difficult it can be to reconstruct the XML hierarchy/tree once the data has been burst into an RDB structure. I am using a simple family tree as my example and I have built a sample database (in Access) with one table that records each family member's name and other statistics (age, etc.) and a second table that joins family members and relationships:

family
------
f_id
name
age

rships
------
r_id
f_id
f_id2
rship

(Where you choose two family members and the relationship between them. This is the most efficient way I could think of to represent this recursive data.)

Now, if I were to try to recreate the family tree in a XML-type tree view, how would I go about that? I don't want any specific code, just a general approach that a typical RDB might take to reconstruct the tree, if it is possible to explain that in any meaningful way without getting technical and specific. I know that it would be schema-dependent, complicated, and require maintenance should the family tree structure change, etc. That's exactly the point I'm trying to make.

And I should point out that I'm still feelin' the love for Access and RDBs in general...just trying to make the case that it's not necessarily the best tool for XML. I don't want to use this post to kick off a debate about NXD vs RDB. I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.
Perhaps I have entirely missed the point of your question, but if I were to try and structure an XML document from the dataset you have givien, it would be identical to the way it was represented in the database:

Expand|Select|Wrap|Line Numbers
  1. <xml>
  2.   <family>
  3.     <member f_id=1 name="Mom" age=43 />
  4.     <member f_id=2 name="Dad" age=59 />
  5.     <member f_id=3 name="Child" age=24 />
  6.   </family>
  7.   <rships>
  8.     <rship f_id=1 f_id2=2 rtype=1 />
  9.     <rship f_id=1 f_id2=3 rtype=2 />
  10.     <rship f_id=2 f_id2=3 rtype=2 />
  11.   </rships>
  12. </xml>
  13.  
Did I miss something?
Jun 29 '07 #9

Dököll
Expert 100+
P: 2,364
I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.
Hello, lwwhite!

You might find this interesting, new post. I am not sure exactly how you are attempting to ditinguish XML and Access databases. If you can do a little reading, please get an idea here:

http://www.thescripts.com/forum/thread672308.html

Any questions, stay tuned, we're here to help...
Jul 4 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Hello, lwwhite!

You might find this interesting, new post. I am not sure exactly how you are attempting to ditinguish XML and Access databases. If you can do a little reading, please get an idea here:

http://www.thescripts.com/forum/thread672308.html

Any questions, stay tuned, we're here to help...
Should that not be in the Articles sections ?
Jul 4 '07 #11

Dököll
Expert 100+
P: 2,364
Should that not be in the Articles sections ?
I am trying to be careful:-)
Jul 4 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
I am trying to be careful:-)
I think that one is a pure article.
Jul 4 '07 #13

Dököll
Expert 100+
P: 2,364
I think that one is a pure article.
Looks like our Articles portion are off limits for postings, has anything changed?
Jul 4 '07 #14

Dököll
Expert 100+
P: 2,364
Looks like our Articles portion are off limits for postings, has anything changed?
Please disregard, I figured it out. No questions but ideas/projects yes!

Has been moved, thanks:-)
Jul 4 '07 #15

Dököll
Expert 100+
P: 2,364

Post your reply

Sign in to post your reply or Sign up for a free account.