XML -> MySQL

[Wojtek]

Hello there boys and girls,

I have a big problem. I'm completely clueless how should I convert xml files into a nice juicy mysql database :)

I got 28 .xml files with 10k entries each.

The syntax is something similar


<recipe>
<title>Blinis</title>
<by>Susan Woodward</by>
<course>Light meals and snacks</course>
<main>Bread & Cakes</main>
<serves>8-10 blinis</serves>
<cooktime>10 to 30 mins</cooktime>
<preptime>less than 30 mins</preptime>
<veg>yes</veg>
<quick>yes</quick>
<ingredients>
280g/10oz strong white flour<br />180ml/6fl oz milk<br />
1 tbsp dried yeast<br />2 eggs, separated<br />
300ml/10fl oz double cream<br />1 tsp salt<br />
30g/1oz butter for frying
</ingredients>
<method>
1. Heat the milk in a saucepan until lukewarm. Pour on to theyeast and leave for 10 minutes until the mixture is frothy.<br />
2. Sift the flour and salt into a large bowl. Mix the eggs yolks, cream and yeast mixture into the flour. Set the batter aside for approximately 1 hour, until it has doubled in size.<br />
3. Whisk the egg whites until they form soft peaks and fold into the batter. Add a little more milk, if necessary, so that the batter is of a thick pouring consistency.<br />
4. Heat the butter in a frying pan over a medium heat. When it is hot, spoon small amounts of batter into the pan.<br />
5. Fry the blinis until they are golden brown on both sides and serve at once.
</method>
</recipe>


How would I go to extract info from each tag, and classing it into a mysql database.


I'v google that subject and came with 3 answers:
- Navicat
- CodeCharge
- Propel (php5 cant try)

So I got both demos of navicat and codecharge.
Fired up navicat, setted db connection info, Import -> XML, selected an .xml file, ok. It froze. ctrl alt del, close, re-open app, same thing.

CodeCharge, can't find anything about xml->mysql in there...


Anyone could suggest me a good solution?
I got 567Mb of xml I gotta parse into sql. Doing so manually is a big no-no.

*maybe* if I could loop thruh all the entries one at a time and sort them or something it could be done. Anyone got some simple demo php/sql code I could expand to use?

[link92]

Parse into an arrary using http://uk.php.net/manual/en/function.xml-parse-into-struct.php

and then dump from the array into the DB...

[Wojtek]

Getting help from a 12yr old heh :)

Thanks Link, I'll try doing something with what you gave me.

[link92]

Here's an example, XML: http://f1unleashed.com/2004.xml (messy because it's exported from Excel) and then it get's parsed into arrarys (http://f1unleashed.com/points2.php) and outputted at http://f1unleashed.com/championship.php

This is the same idea, just instead of it putting the data into MySQL queries, it echos them...



Also, I'm in the top 30 posters of CodingForums.com :p Your not the only one I'm helping ;)

[link92]

On a side note, I'm keeping the code of that closed... for the time being...

[Wojtek]

Parse into an arrary using http://uk.php.net/manual/en/function.xml-parse-into-struct.php

and then dump from the array into the DB...
Parsing into arrays... Completed :)

But I can't figure out how to play with those arrays and feed them to mysql...

<recipe>
[...]
<ingredients>
<ingredient>
<quantity>2</quantity>
<unit>cup</unit>
<name>goat milk yogurt</name>
</ingredient>
<ingredient>
<quantity>10</quantity>
<unit>oz</unit>
<name>cooked spinach leaves finely chopped</name>
</ingredient>
</ingrediants>
[...]
</recipe>

As you see there's 2+ <ingredient> thingies around the main <ingrediantS>.
I'd like to have my mysql field called <ingrediants> and have every <ingrediant> on a seperate line when I'll be puling info from the database (user output), Any ideas how to accomplish this?

[link92]

Can you post the arrary?

[link92]

Couldn't you have another table of ingredient?

And can you add the .txt extension to your XML file and upload it here as an attachment?

[Wojtek]

Here's a little sample of the xml structure in the array.

http://www.cookingpages.com/xml/test5.php

the xml's are over 550Mb and I'm not upping all that :)

[link92]

OK, I'll type up some code later...

[link92]

Sorry, I'm really, really, really (x 1000) busy... I might just be able to do it tomorrow...