harrisloris
New Member
I asked this question recently at Flattening XML Data into a database schema with XMLSpy, but I'm still struggling with it. It's also not really an XMLSpy question, so I'm reposting it in a different way.What is the best or most standard way to map XML elements to database tables when the element can occur in multiple places in the hierarchy?For example:\[code\]<?xml version="1.0" encoding="UTF-8"?><a> quux <b> corge <c> grault <d> garply <e> waldo <f>foo</f> </e> </d> <e> fred <f>bar</f> </e> </c> <e> plugh <f>baz</f> </e> </b> <e> xyzzy <f>qux</f> </e></a>\[/code\]Note that the \[code\]<e>\[/code\] element (which is complex, since it contains an \[code\]<f>\[/code\] element) can be a child of \[code\]<a>\[/code\], \[code\]<b>\[/code\], \[code\]<c>\[/code\], or \[code\]<d>\[/code\].It's impractical to map this to a table structure of:
- A: (a_pk, a)
- B: (b_pk, a_fk, b)
- C: (c_pk, b_fk, c)
- D: (d_pk, c_fk, d)
- E: (e_pk, ?_fk, e, f)
- A: (a_pk, a)
- E_A: (e_pk, a_fk, e, f)
- B: (b_pk, a_fk, b)
- E_B: (e_pk, b_fk, e, f)
- C: (c_pk, b_fk, c)
- E_C: (e_pk, c_fk, e, f)
- D: (d_pk, c_fk, d)
- E_D: (e_pk, d_fk, e, f)
- A: (a_pk, a)
- B: (b_pk, a_fk, b)
- C: (c_pk, b_fk, c)
- D: (d_pk, c_fk, d)
- E: (e_pk, a_fk, b_fk, c_fk, d_fk, e, f)
- A: (a_pk, a, e_fk)
- B: (b_pk, a_fk, b, e_fk)
- C: (c_pk, b_fk, c, e_fk)
- D: (d_pk, c_fk, d, e_fk)
- E: (e_pk, e, f)
- A: (a_pk, a)
- B: (b_pk, a_fk, b)
- C: (c_pk, b_fk, c)
- D: (d_pk, c_fk, d)
- E: (e_pk, parent_table, fk, e, f)
- A: (a_pk, a)
- A_E: (a_fk, e_fk)
- B: (b_pk, a_fk, b)
- B_E: (b_fk, e_fk)
- C: (c_pk, b_fk, c)
- C_E: (c_fk, e_fk)
- D: (d_pk, c_fk, d)
- D_E: (d_fk, e_fk)
- E: (e_pk, e, f)