Geek Answers Handbook

Convert XML to SQL

I know this is not a very simple question, but how would you create an SQL database to store data coming from an XML file? (you don’t have an XML schema, just plain XML with many different tags, attributes, and nested elements).

This is more conceptual than technical. As we move from a hierarchical data model (XML) to a relational model (SQL).

+7
source share
3 answers

If you don’t have a scheme and want to use a traditional DBMS, a traditional relational method, you are mostly screwed.

But you can use XML data types ( Oracle (> 9i), in MS SQL (> 2005), in Postgres , in DB2 ), which are present in all major database systems. This allows you to process XML content using XPath expressions, which is pretty neat.

It is also recommended to read:

Or you can skip converting a hierarchical model to a relational model, as this is apparently a prefix option for NoSQL DB like Cassandra or MongoDB .

(Originally posted as a comment, but I think it would be worthy to be the answer ...)

+2
source

Well, what's the problem? The concept of trees as relationships is simple.

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

The keys and connections between the relationships are obvious, I hope. This is ugly and strongly typed, of course, but this is what you get if you want to preserve arbitrary XML.

+1
source

There are several ingenious tree coding schemes in SQL. Again, even smart tree coding is inferior to a properly designed database schema.

0
source

More articles:


All Articles