import data from XML into DB

[eisbergstudio]

Hi All,

I'm new in XML. Today on one job-interview (job that I reaaaaaly want) gave me a homework :confused: from XML. I should import and then export some XML data from and to database.

Please do you know that where I should start? the DB type was not mentioned....

XML data are type of tree (level-1, level-2a, level n..). I think it is more about the logic...but becasue of I don't know about XML...I would really need some help.......

[scragar]

is there a language to work with, or are you just after psuedo code or what?

[eisbergstudio]

they mentioned that they just want to know how I think...so I think it is more about pseudo code?.....

Here is a part of the XML:

<XMLdata>
<struct id ="dataID">
<name> Example </name>
<node id="level 1" isvisible="true">
<name></name>
</node>
<node id="level 2-a" isvisible="true">
<name></name>
</node>
<node id="level 2-b" isvisible="true">
<name></name>
<node id="level n" isvisible="true">
<name></name>
.........<!--similar code - don't want to copy it here>
.........
.........
</node>
</node>
</struct>
<definenodes>
<definenode identifier="level 1" type=" " lanuch= " ">
<path href=""/>
</definenode>
<definenode identifier="level 2-a" type=" " lanuch= " ">
<path href=""/>
</definenode>
<definenode identifier="level 2-b" type=" " lanuch= " ">
<path href=""/>
</definenode>
.........<!--similar code - don't want to copy it here>
.........
.........
</definenodes>

</XMLdata>

[scragar]

that's what psuedo code is all about, the method and not the code.

most languages will have an XML library or 2 that you can use to read it all in, so something like:


include XML and DB libraries required
open XML file and load it's contents
parse the xml file so we get a structure
then, depending on what info you need to get/store
loop through the struct element with the id "dataID"
for each child insert the contents of it's id into the DB?


Of course you'll need to adjust this based on what you are after.

[eisbergstudio]

thank you VERY much for help....
please...I have one more question - does anybody know what can mean the "isvisible", type=" ", lanuch= " " ???? (in the before attached code)

[scragar]

The whole point of XML is that it can be used for lots of different types of data and should describe the data(that's the goal anyway), so as for what a given atribute means I can only guess.
I'm assuming isVisible would be true or false which the appropriate program would decide if the row is hidden or now, type would be a catagory and launch would be a program path or command, but the idea of XML is that you make your own rules.

[eisbergstudio]

millions of thanks..

[eisbergstudio]

one more question - what is the best way of processing of XML file (loading, parsing, database working). PHP, C++, Java,...? Or is it the same? I know that it depends on the machine and environemnt....but...

[scragar]

Depends entirely on what you want to do, almost any language I can think of with very few exceptions can read and parse XML, so I wouldn't say to use any language based on it's ability to access and read XML.

Since this is a web dev forum though I'd say go for PHP if you don't know anything already, everything is pretty well documented, there are tons of tutorials, it's free and has lots of pre built functions for almost any task you can imagine on the internet already.

[ss3]

Just to lead discussion to a valid end.
I am practically trying to import the xml file into DB
Just to illustrate my problem, this is what I am trying to do

I have 4different types of xml files (different structures) and they are about 100 xml files of each format. I am trying to import these files into DB file i.e 100 files into 1 Db file , the the resulting 4 Db (for each format) files to a single DB file.

I plan to have a web application which can query the database .

This is what I am planning, import all XML files into 1 or 4 database and use php for web development front end.
But I am a new bie to both PHP and DB, any suggestion how can I import 100 xml files into a single DB and some help on how to use this DB along with PHP would be appreciated.
I see whole bunch of xml parsers and DB tools but very confused which one is best for me.
Any pointers??
Thanks

[scragar]

XML with PHP is very easy:
$XML = simplexml_load_file('FILENAME');

echo '<pre>';
ParseLevel($XML);
echo '</pre>';

function ParseLevel($parent, $tabs=''){
echo "\n{$tabs}" . $parent->getName();// the tag name
if(count($parent->attributes())){// if there are attributes
echo "\n\t{$tabs}->Attributes\n";
foreach($parent->attributes() as $key=>$val)// for each attribute
echo "\t\t{$tabs}{$key} = {$val}";// show it's info
}
if(count($parent->children())){// if there are child nodes
echo "\n\t{$tabs}->Children\n";
foreach($parent->children() as $child)// for each child
ParseLevel($child, "\t{$tabs}");// do the same for the child
}
}
TBH I have no idea how you would expect to parse the XML file, you've not explained that yet. It's pretty easy to parse XML using simpleXML though, you can use foreach, Xpath and a whole bunch of other easy techniques to search them.

[ss3]

Hi,
I searched a bit and got to know use of xml wit php is easier but my dilemma remains

Do i need to have intermediate DB for aggregating xml files into single DB instead of accessing xml files directly in php?

Can some one illuminate which DB is best to interfacing with PHP

[scragar]

MySQL is easiest to use with PHP:
mysql_connect('HOST', 'USERNAME', 'PASSWORD');
mysql_select_db('DB NAME');

mysql_query('INSERT INTO
`tblName`(`fld1`, `fld2`)
VALUE("row1 val1", "row1 val2")');
mysql_query('INSERT INTO
`tblName`(`fld1`, `fld2`)
VALUE("row2 val1", "row2 val2"),
("row3 val1", "row3 val2"),
("row4 val1", "row4 val2")');

[ss3]

hi scragar,
Thanks a lot for code snippets and suggestion, As you suggested i am using SimpleXML to parse my file but I am looking to import a bunch of XMl files to single MySQL DB.
Lot of suggestions out they on web, but i am confused which is best one for me.
any suggestion how to import 100 XML files into single MySQL Database file (of course this involves parsing xml file and mapping them to DB tables, but a sweet example would really help)
Again thanks a lot for your suggestions.

[scragar]

I have no idea what your XML file would look like as a table in a database, if you can explain that a simple loop might work:
define('XML_DIR', '/DIR/WITH/YOUR/XML/FILES');
chdir(XML_DIR);
foreach(glob('*') as $fName){
if( ($XML = simplexml_load_file(XML_DIR.DIRECTORY_SEPARATOR.$fName)) === FALSE)
continue;
//... parse and add to DB
}