XML to SQL conversion

Question

I know this is not a very straightforward question, but how would you design a SQL database in order to store data coming from a XML file?, (you have no XML schema, just a plain XML with a lots of different tagnames, attributes and nested elements).

This is more conceptual than technical. How we go from hierarchical data model (XML) to relational model (SQL).

Answer 1

If you have no schema, and want to use a traditional RDBMS, the traditional relational way, you're basically screwed.

But you can go for the XML datatypes (in Oracle (>9i), in MS SQL (>2005), in Postgres, in DB2 ) that are present in all major database systems. That allows you to handle XML content using XPath expressions, which is quite neat.

Also recommended reading:

• Microsoft SQL Server 2005/2008: XML vs text/varchar data type

Or, you can skip having to convert your hierarchical model to relational, as this seems to be the prefect use case for a NoSQL DB like Cassandra or MongoDB.

(Posted as comment initially, but I think it might be worthy to be an answer...)