2

what is today the easiest, most automated way to import complex XML (external from an API including an .xsd scheme) into a relational or any database? - I understand there should be a (semi)automatic way to import this for every database, i just yet didnt find it? *

This also comes from the question why use XML complification for relational data? Why isnt API data that comes from a relational database and shall end up in one at most users of the API usually also transfered in rows? Table VS xml / json / yaml - table requires less storage if data is any related? more efficient than compression

:)

Community
  • 1
  • 1
iloveregex
  • 57
  • 2
  • 12
  • 1
    in mysql you could use [`load xml`](https://dev.mysql.com/doc/refman/5.5/en/load-xml.html). this requires that the xml is formatted according same as the mysql xml extract ( if necessary you can use some xslt or equivalent to shape your xml this way ) – amdixon May 09 '15 at 03:16
  • 1
    so thats your xml definition - could you also post a sample of your xml data for one of your tables – amdixon May 09 '15 at 14:49
  • thanks! xml: http://pastebin.com/C5yjSjCf scheme: http://awis.amazonaws.com/AWSAlexa/AWSAlexa.xsd does that work automated or else why did noone make it yet? Is there any fast implementation of xlst / xquery ? – iloveregex May 09 '15 at 14:51
  • 1
    could you add those links to the question and mention commands/etc. that you used to generate/create that info – amdixon May 09 '15 at 15:11
  • 1
    Essentially the xml is one "complete" API response representing one row (google.com) from a table "websites":BasicInfo,Rank,RankPerPlace. XSD includes datatypes for the table. Import does not require restructering (of course XML lines 44-102 just refer to related sites in the same table and 125-1744 to country/citys, which could make a locations/relation table) The api response is recorded once a week = spatial data = drawing graphs for each pair site/location (or location/site). I dont care yet if relational-,newsql-,graph-,triplestore-,nosql-database - just closest to automatic import! – iloveregex May 09 '15 at 16:44
  • 1
    through this question i was hoping to find out a universal answer/universal tools. I also posted as a specific question here: http://stackoverflow.com/questions/21521011/query-xml-json-collection-like-a-relational-database – iloveregex May 09 '15 at 16:44
  • 1
    finally, since amazon's Alexa API does not provide any other data format - which is complification here - i tried to raise a question(/discussion/point) against XML and other unstructured dataformats strangely employed for related data: http://stackoverflow.com/questions/22950986/table-vs-xml-json-yaml-table-requires-less-storage-if-data-is-any-related (but that's too wide for stackoverflow?/Is there a more appropriate place?) – iloveregex May 09 '15 at 16:45

1 Answers1

1

You're looking for a universal tool. However the concept of XML is fundamentally incompatible with the concept of a (relational) database.

The universal tool you're looking for should at least cover three fundamtal type of operations:

  • a) The XML is defined as a projection of a table/row/field concept
<xml>
   <table name='myTable'>
      <row id='1'>
         <field name='myField1' type='string'>myValue1</field>
         <field name='myField2' type='date'>01-01-1901</field>
         <field name='myField3' type='number'>123456</field>
      </row>
   </table>
</xml>
  • b) The XML is to be stored in one XML field in one row of a table
Id  Name    Date        XML
--  ----    ----------  -------------------------
1   MyEx    01-01-2001  <myObject>
                            <myAttribute name='class'>example</myAttribute>
                        </myObject>
  • c) The XML is a projection of a parent/child relationship in the database
<xml>
   <order number='123'>
      <customer id='1001'>myCustomer</customer>
      <orderDate>01-01-2001</orderDate>
      <address>wherever to go</address>
      <orderDetails>
         <orderProduct code='P01'>
            <name>myProduct</name>
            <amount>15</amount>
            <listPrice>$14.00</listPrice>
         </orderProduct>
      </orderDetails>    
   </order>
</xml>

In each and every case the tool must specify if you are allowed to import one of such objects or more, and the tool must be able to transform the presenation of values to acceptable storage formats.

All of this is not impossible, but important to check which of these functionalities your selected tool will support.

Martijn van der Jagt
  • 524
  • 3
  • 13
  • thanks:) i know, but for related data there aren't unlimited amounts of different XML pathes. So each can have a column in a table. Furthermore a database modell (tables and keys n:1 n:m) could be assumed and manually correct as need just like all database models in DBMS – iloveregex May 16 '15 at 03:55