Good software for opening a big XML file

The_B_Man

Hi,
i've an XML file thats half a million lines long, according to notepad++.
My laptop is too old to properly scroll through it. It takes about 30 seconds for it to refresh each time I scroll down. Basically, notepad++ is unusable for this file.

Can anyone recommend a good little free app just so I can open the XML and have a look at its structure? My intention is to convert it into a MySQL database, but I can't see the structure to decide on a DB schema. I know roughly the data thats in the file, but don't know what headings and fields etc are in it, hence the problem.

Or, if anyone knows of software that'll convert a well formed XML file straight into a DB, that would be perfect!

Any advice appreciated.

conor.hogan.2

I had to open my iTunes xml file in Vim, as pretty much every other app could not handle it. You can have a gui clone of vim if that helps too.

firefly08

http://stackoverflow.com/questions/159521/text-editor-to-open-big-giant-huge-large-text-files

Some good suggestions in there.

Malice

XMLQuire might help.

amen

Sorry why are you trying to read a file that is half million lines long ?

what exactly are you going to find by reading the file ?

you would be better creating an xml reader that reads the file and displays each section

duffman85

I've used First Object XML Editor (http://www.firstobject.com/dn_editor.htm) for fairly large (50MB) XML files.

Or GVim for a general purpose editor

The_B_Man

amen wrote: »

Sorry why are you trying to read a file that is half million lines long ?

what exactly are you going to find by reading the file ?

you would be better creating an xml reader that reads the file and displays each section

I'm trying to read it so i can see its structure so i can properly convert it to a MySQL database. I've since found a schema for it though.

Does anyone know any easy way, using the schema, to create a database and populate the tables with the XML data?

tetsujin1979

I use Geany - http://www.geany.org/ - for editing large XML files, works great

The_B_Man

Malice wrote: »

XMLQuire might help.

Thats terrible. Even worse than NotePAD++.

That first object xml editor is very good!

Malice

The_B_Man wrote: »

Thats terrible. Even worse than NotePAD++.

That first object xml editor is very good!

Fair enough, I haven't thrown large XML files at it. It did fulfill two of your requirements by being small and free though.

Feathers

conor.hogan.2 wrote: »

I had to open my iTunes xml file in Vim, as pretty much every other app could not handle it. You can have a gui clone of vim if that helps too.

Show-off! (Not on your vim use, on the vastness of your music collection)

conor.hogan.2

Feathers wrote: »

Show-off! (Not on your vim use, on the vastness of your music collection)

A lot of people could not open theirs either (part of a college project, marginally). XML gets unwieldy for most apps at iTunes library sizes.

That said I do have many mp3 encoded audio files and my apartment smells of rich mahogany.

BostonB

If this is a database export perhaps there's a more suitable and compact export format you could use other than XML.

[CrimsonGhost]

Check the opening lines of the file. See if a DTD or XSD is mentioned, or if you know of one that exists for it, trying looking at that to discern the best structure for your database. Alternatively just stick in it a BLOB

dropstar

I'd suggest a different format than XML, if possible. Hugely verbose and poor content to markup ratio. It was never intended as a data storage format, merely a data transmission format, for say, RPC or web service data exchange. JSON is a good alternative.

Boskowski

Microsofts SSIS (SQL 2008 component) does this quite easily and will convert it right into a table and load the data too. It even has a 'wizard' that will have all this done in matter of minutes. And memory requirements are somewhat benign compared to other methods. Have a look around MS SQL usually has a very cheap or even partly free 'student version'.

Pretty sure other ETL tools can do this too.

BostonB

I'm kinda curious how you end up with a massive XML file on an old laptop in the first place.

Feathers

dropstar wrote: »

I'd suggest a different format than XML, if possible. Hugely verbose and poor content to markup ratio. It was never intended as a data storage format, merely a data transmission format, for say, RPC or web service data exchange. JSON is a good alternative.

It sounds like the OP knows the downsides of huge XML files, which he's why he's trying to convert it & populate a SQL database. Think he's stuck with it rather than choosing to store his data in XML at the moment

The_B_Man

Exactly. The original file is in XML format and I want to convert it to a database. Its a huge piece of open data that I got from a government website and want to make use of it with some software I am going to write. Just need to extract the actual data into a usable format.

BostonB

Get someone with a better computer to do it for you, then give you the databack in a useful format, chunks.

croo

The_B_Man wrote: »

Exactly. The original file is in XML format and I want to convert it to a database. Its a huge piece of open data that I got from a government website and want to make use of it with some software I am going to write. Just need to extract the actual data into a usable format.

Why not use an ETL tool like Pentaho? It's open source so it won't cost you to try - other than your time. I've used it to convert CSV & XML files to database tables. It will extract the field names from the CSV/XML and even generate the SQL needed to create the necessary database table(s). This of course is a very rudimentary use of an ETL tool but like I said it doesn't cost so why not. Pentaho is the one open source ETL app I've used but Talend is another popular option.

If you decide to try, Pentaho is actually a suite of tools - the "Spoon" application specifically is what you want.
http://kettle.pentaho.com/ is what you want and it includes spoon.