convert/Import specific XML + XSD into a database automatically?

Question

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

:)

in mysql you could use load xml. 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
– amdixon, Commented May 9, 2015 at 3:16
so thats your xml definition - could you also post a sample of your xml data for one of your tables — amdixon
– amdixon, Commented May 9, 2015 at 14:49
could you add those links to the question and mention commands/etc. that you used to generate/create that info — amdixon
– amdixon, Commented May 9, 2015 at 15:11
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
– iloveregex, Commented May 9, 2015 at 16:44
through this question i was hoping to find out a universal answer/universal tools. I also posted as a specific question here: stackoverflow.com/questions/21521011/… — iloveregex
– iloveregex, Commented May 9, 2015 at 16:44

Martijn van der Jagt · Accepted Answer · 2015-05-15 09:28:36Z

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.

answered May 15, 2015 at 9:28

Martijn van der Jagt

5143 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iloveregex Over a year ago

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

Collectives™ on Stack Overflow

convert/Import specific XML + XSD into a database automatically?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related