8

I have a large XML file in the following format and I need to convert this file into a MySQL table. Please let me know how can I accomplish this?

~~~~~~~~~~

<host starttime="1392325468" endtime="1392325486"><status state="up" reason="user-set"/>
<address addr="192.168.0.2" addrtype="ipv4"/>
<hostnames>
</hostnames>
<ports><extraports state="filtered" count="2">
<extrareasons reason="no-responses" count="2"/>
</extraports>
<port protocol="udp" portid="22"><state state="open|filtered" reason="no-response" reason_ttl="0"/><service name="ssh" method="table" conf="3"/></port>
<port protocol="udp" portid="123"><state state="open|filtered" reason="no-response" reason_ttl="0"/><service name="ntp" method="table" conf="3"/></port>
</ports>
</host>

~~~~~~~~~~

4

For MySQL, you might use use the ExtractData function.

If you are also using Windows (it needs Net Framework) then this old and outdated tool might still do the job.

http://xmltodb.sourceforge.net/

I have used it in the past and it converts the XML file to plain INSERT commands. For simple XML files it actually works. Of course the INSERT commmands will need some tweaking (change " or ') according to the db flavor but 90% of the actual job is done with the tool.

Commercial tools like XMLSpy http://www.altova.com/xmlspy/database-xml.html have a similar functionality (you may try the trial editions).

You can even try importing the file to MS Excel and then export to CSV for importing into the MySQL database. MS Excel has a nice feature XML maps that can map XML attributes and entities to columns. I tested it and it will easily work with a well formatted XML like yours.

Not the answer you're looking for? Browse other questions tagged or ask your own question.