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


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


    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.


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

    链接地址: http://www.djcxy.com/p/11388.html

    上一篇: 检测文件夹级别的更改

    下一篇: XML到SQL的转换