CODEWITHSUNDEEP
sqlxmlrelational-database
Roger W.
Roger W.

Reputation: 337

XML to SQL conversion

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).

Upvotes: 7

Views: 5829

Answers (3)

Tegiri Nenashi
Tegiri Nenashi

Reputation: 3086

There are some ingenious tree encoding schemes in SQL. Again, even clever tree encoding is inferior to properly designed database schema.

Upvotes: 0

ppeterka
ppeterka

Reputation: 20726

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:

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...)

Upvotes: 2

Joker_vD
Joker_vD

Reputation: 3765

Well, what's the problem? Representing trees as relations is simple. 

NODE ( id, tag-name, text )
ATTR ( id, attr-name, attr-value )
NODEATRR ( node-id, attr-id )
NODENODE ( node-id, child-node-id )

Keys and connections between relations are obvious, I hope. It's ugly and stringly-typed, sure, but that's what you get if you want to store an arbitrary XML.

Upvotes: 1

Related Questions