Automated way to convert XML files to SQL database?

Question

We have a bunch of XML files, following a schema which is essentially a serialised database form:

<table1>
   <column1>value</column1>
   <column2>value</column2>
</table1>
<table1>
   <column1>another value</column1>
   <column2>another value</column2>
</table1>
...

Is there a really easy way to turn that into an SQL database? Obviously I can manually construct the schema, identify all tables, fields etc, and then write a script to import it. I just wonder if there are any tools that could automate some or all of that process?

Which SQL is it? Some, like Oracle, provide native support for importing XML into a table. — wallyk, Dec 12 '12 at 05:49
Oh. Either Postgres or MySQL - not fussed. (But nothing commercial.) — Steve Bennett, Dec 12 '12 at 05:57

score 16 · Accepted Answer · edited May 23 '17 at 10:29

For Mysql please see the LOAD XML Syntax^Docs.

It should work without any additional XML transformation for the XML you've provided, just specify the format and define the table inside the database firsthand with matching column names:

LOAD XML LOCAL INFILE 'table1.xml'
    INTO TABLE table1
    ROWS IDENTIFIED BY '<table1>';

There is also a related question:

How to import XMl file into MySQL database table using XML_LOAD(); function

For Postgresql I do not know.

score 2 · Answer 2 · edited May 23 '17 at 12:10

2

There is a project on CodeProject that makes it simple to convert an XML file to SQL Script. It uses XSLT. You could probably modify it to generate the DDL too.

And See this question too : Generating SQL using XML and XSLT

edited May 23 '17 at 12:10

Community

1
1

answered Dec 12 '12 at 05:48

Preet Sangha

64,563
18
145
216

Great, that's a really helpful starting point. Googling for the DDL part turns up this: http://xml2ddl.berlios.de/ – Steve Bennett Dec 12 '12 at 05:53
@steve that link is dead now, could you possibly point me to the current project, or anywhere you may have found a better solution. Thanks! – Jeff Puckett Sep 21 '16 at 01:29
2

@JeffPuckettII I'm steve but [I've found a fork of it on git hub](https://github.com/charlesnagy/xml2ddl) – Preet Sangha Sep 21 '16 at 02:05

score 1 · Answer 3 · edited Feb 12 '15 at 09:26

1

If there is XML file with 2 different tables then will:

LOAD XML LOCAL INFILE 'table1.xml' INTO TABLE table1 
LOAD XML LOCAL INFILE 'table1.xml' INTO TABLE table2

work

edited Feb 12 '15 at 09:26

rtruszk

3,902
13
36
53

answered Feb 12 '15 at 09:00

Well-Wisher

41
1
9

4

All these methods require you to manually construct the table first. – Steve Bennett Feb 13 '15 at 01:06

SRIRAM · Answer 4 · 2012-12-12T06:05:46.887

0

try this

http://www.ehow.com/how_6613143_convert-xml-code-sql.html

for downloading the tool http://www.xml-converter.com/

edited Dec 12 '12 at 06:05

answered Dec 12 '12 at 05:49

SRIRAM

1,888
2
17
17

Looks like that tool goes the opposite direction. The tutorial links to some other options. – Steve Bennett Dec 12 '12 at 05:56

Automated way to convert XML files to SQL database?

4 Answers4

Linked

Related