Converting xml schema to relational model

Question

Do you guys know, how we can convert XML Schema to relational model? What are the steps involved? More precisely, How can we figure out what the table names, columns names and the relationships on those tables, going to be like on relational model by looking only at the XML Schema?

If you have any tutorial links, examples then please share it with me. I hightly appreciate your help on this.

THanks

Answer 1

The two are inherently different. The relational model is set-based; XML is hierarchical.

One way might be to make the tag name the name of the table IF the tag has multiple child with leaf grandchildren. The child tag name would be the column, and the grandchild text the value.

If the children have non-leaf grandchildren, model these as one-to-many relationships between parent and child table.

There are several papers to be had if you Google "XML schema to relational schema", including these:

1. http://www.cobase.cs.ucla.edu/tech-docs/dongwon/ecaiot02.pdf
2. http://msdn.microsoft.com/en-us/library/aa905914%28SQL.80%29.aspx
3. http://msdn.microsoft.com/en-us/library/bfdchewb%28VS.71%29.aspx

Answer 2

How complex is the schema? Depending on the structure defined by the schema, you may find there is no convenient translation to relational tables.

You haven't said how you intend to use your data, but some relational databases support xml-typed columns. For example, in Microsoft SQL Server 2005 and later, you could store your xml documents in a column of type xml. Your queries could then use built-in methods defined for the xml data type.

Answer 3

Without an example of the XML format, it would be hard to give an answer. A very simple XML format will be easy to convert, but a more complex structure or heirarchy will not be easy and will take some thought. As such, a tool really won't work for you.

In general, you're also getting into a conversation of using an object database (OODBMS) versus a relational database (RDBMS). Some research into object databases might be in order, but if you have to go with a relational database (they're more mature, by far), you'll have to do the mapping work yourself.

Answer 4

This is pretty simple with .NET.

Create a DataSet.

Do DataSet.ReadXML() to read in your XML file.

Cycle through the list of DataSet.Tables() and for each Table cycle through the columns to get the field names.

I had a test app to do that ... I'll see if I can find the code.

Ron

P.S. - couldn't find it .. so I wrote one at http://dot-dash-dot.com/files/wtfxml.zip

WTFXMLSetup_1_6_0.msi ... the installer for windows boxes ...