General discussion

Locked

Edit an XML doc's hierarchical structure

By TechSupportSpecialist ·
I am not an expert with XML, so please bare with me here. I have an XML document that has data exported from a program.

It is relational database that has been normalized and then exported. Once it gets exported to the XML file however, it gets stored in a hierarchical fashion so that child tables get stored as tags within the parent tags. At the same time, the foreign key field in the child table gets removed.

For example, we have a table called CUSTOMER and another called ORDER. The primary key field in CUSTOMER is 'Customer_ID' and this is the foreign key in ORDER that links the 2 tables. 1 customer can have many orders, but an order can only have 1 customer. When the XML document gets created, it removes the Customer_ID field from ORDER and just indents ORDER within CUSTOMER (like an outline).

This data becomes useless when imported into Access or any other program because we don't get the Customer_ID field in ORDER.

In the XML document, how can we get that Customer_ID field to propagate down to each child ORDER record and unindent it? If there's a software package we need, please let us know. Thank you.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Edit an XML doc's hierarchical structure

by RDWilson2 In reply to Edit an XML doc's hierarc ...

What you are probably doing is exporting the entire database and this is resulting in the records from related tables being "nested" within the parent table.

I think what you probably will have to do is export each of the tables individually.
Alternatively, you could develop (or have developed) an application that processes the XML as you have it and generates the equivalent inserting SQL statements for you to use.

The approach you take should bear in mind things like whether you areusing auto-increment fields as your unique ID for, for instance, you Customer table.

Depending on the database you are exporting from, you may have a third option which would be to dump the data into SQL statmenets and to dump the DDL statements that would recreate the tables.

Collapse -

Edit an XML doc's hierarchical structure

This is a good explanation, but we don't have any control over the application that creates the XML document. Once the XML document is created with the 'nested' tables, we need to have some methodology for 'unnesting' it with the foreign key being included in the child table.

Collapse -

Edit an XML doc's hierarchical structure

by RDWilson2 In reply to Edit an XML doc's hierarc ...

I repeat the second part of my initial response:
"Alternatively, you could develop (or have developed) an application that processes the XML as you have it and generates the equivalent inserting SQL statements for you to use."

From what I have seen of XML (and my experience in dealing with it), that is probably going to be your only alternative based upon the additional information you have provided.

Collapse -

Edit an XML doc's hierarchical structure

I already know this. I need to know what application(s) or SQL code will solve this problem. There must be some logic that will go into the XML file and populate each child record with the primary key of the parent record.

I appreciate your time and explanation, but since I need to have an specific answer that will solve this problem, I cannot award the points. Thank you anyway.

Collapse -

Edit an XML doc's hierarchical structure

by RDWilson2 In reply to Edit an XML doc's hierarc ...

Okay, I'll try to be more specific.

An application will need to be created that reads the XML data in a manner that allows it to, in effect, retain the association between the parent records and their child records. The application will then have to create (or re-create, if you will) the parent's foreign key field information in the child records before inserting them into the new database.

This could probably be done in Delphi or even VB; however, it will be an application specific program and, without the specifics of the tables involved, cannot be more specifically described than this.

Eseentially, from my experience, there _is_ no "off-the-shelf" application or SQL code that will solve this problem. Your statement that "There must be some logic that will go into the XML file and populate each child record with the primary key of the parent record." is correct; however, a program to do so will have to be created specifically for the situation you are facing . . . there is, to my knowledge, no "general solution" for this sort of problem. (After all, how is would a "general solution" know which field in the parent record is the key to be inserted in the child record?)

I know this is NOT the answer you wanted but I am afraid it is as good as the answer gets.

Collapse -

Edit an XML doc's hierarchical structure

Alright. I appreciate your time and explanation. If you happen to come up with anything else that will help me, please send me an e-mail with the information: jmp@datanetri.com
Thank you.

Collapse -

Edit an XML doc's hierarchical structure

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums