XML to MySQL

[Sayth]

Hey all,

My buddy runs tech for a small hardware store and was asked if he can get an XML report file easily imported to a MySQL database. You see he's been bragging about how having a MySQL database is so much better than the crappy little flat-file java database he's been using. I agree, but am not sure how to load this XML.

I've used SimpleXML with PHP and it works for my XML files, but I am nowhere near understanding exactly how exactly it works... It won't parse the XML file he gave me though.

The XML is like the following, I used fake data, but this is the structure:

<?xml version="1.0" encoding="utf-8"?>
<IMPORT Source="Shared Report">
  <RECORD Name="UserReport123456" Reporting_Company="WidgetCo" Reporting_Company_Report_Number="099878" Reporting_User="John Smith" Source_Contact="John@WidgetCoSmith.COM" Synopsis="Report For Purposes of acount tracking (RPAT)" Date_Received="2013-01-01">
    <!--Expected Information-->
    <ONLINEID Name="BobDoaley98@sweetwidgetsco.com" OnlineIdType="Email Address" Role="User">
      <GRADE Name="Middle" RankVerified="In Process" />
    </ONLINEID>
    <ONLINEID Name="BobDoale" OnlineIdType="Screen Name" Classification="Supervisor">
      <GRADE Name="Uses" LinkQuality="Unclassified" />
    </ONLINEID>
    <!--Notes-->
    <NOTE Reserved for future notes />
    <NOTE Reserved for future notes />
  </RECORD>
</IMPORT>

I'll have a simple Db set up with a record for each element, probably me a table just for Company name, etc...

Can anyone point me in the right direction? Even just to parse the data into variables I can use with the MySQL INSERT commands.

I can't even get this to work. This is the code I use for my XML files:

<?php
	

$xml = simplexml_load_file('354249.xml');

print'<pre>';

print $xml->IMPORT->RECORD;

?>

Thanks!
-S

 

[PTNL]

I'm not familiar with that ingest tool, but try processing that sample without these lines:

<!--Suspect Information-->
<!--Notes-->
<NOTE Reserved for future notes />
<NOTE Reserved for future notes />

It would be better if you were provided a XSD to validate against.

 

[Sayth]

haha my english is not all that good, when I was retyping it was supposed to be expected information. I'll edit my post and then try your suggestion.

 

[int]

https://dev.mysql.com/doc/refman/5.5/en/load-xml.html might be an option.