Get XML file to MySQL-DB through Perl

A script shall be provided to import XML-Files with articles (see example in attachement or below) and insert or update multiple tables of a MySQL-Database. An xsl-template of the XML-File can be supplied but contains minor mistakes. See [login to view URL] for XML file structure One usual XML-File is about 1MB, it contains multiple articles, an article might contain a number of variants, a variant might have pictures. An XML might update article-attributes again but not contain variants, keep existing variants An XML might update articles and variant-attributes but not contain variant-pictures, keep existing pictures. Current database layout: - Articles PRIMARY KEY (pid), auto_increment - NOT the products_id from the XML! UNIQUE (products_id) - Variants PRIMARY KEY (pid, first_order, second_order), UNIQUE (products_id, first_order, second_order) - may get obsolete on your script - Variant-Pictures PRIMARY KEY (pid, first_order, second_order) UNIQUE (products_id, first_order, second_order, file, ending) - may get obsolete on your script Supportive MySQL-functions can be used (MySQL 5) [login to view URL] XML-Structure: <?xml version="1.0" encoding="ISO-8859-1"?> <articles> <article> <products_id>LKJ 297</products_id> <attribute_0>98797976</attribute_0> <dealer_number>9922</dealer_number> <dealer_name>John Doe Dealershop</dealer_name> <pkg>Box</pkg> <weight>0.1</weight> <pkg_units>2</pkg_units> <manufacturer_name>Hewlett Packard</manufacturer_name> <manufacturer_s>HP</manufacturer_s> <manufacturer_code>02716</manufacturer_code> <product_name>testname</product_name> <price>1314</price> <attribute_1>slkdfl</attribute_1> <another_attribute>That is a test</another_attribute> <category>Others</category> <matchcode_1>Printer supply</matchcode_1> <matchcode_id_1>010291</matchcode_id_1> <matchcode_2>HP</matchcode_2> <matchcode_id_2>010366</matchcode_id_2> <matchcode_3>.</matchcode_3> <matchcode_id_3>.</matchcode_id_3> <variants_num>5</variants_num> <variants> <variant> <first_order>1</first_order> <second_order>1</second_order> <ordercode>OIU789</ordercode> <name>option 5 name</name> <description>longer text</description> </variant> <variant> <first_order>2</first_order> <second_order>1</second_order> <ordercode>K7IOU O</ordercode> <name>option 5 name</name> <description>another longtext</description> </variant> <variant> <first_order>3</first_order> <second_order>1</second_order> <ordercode>OIU999</ordercode> <name>option 5 name</name> <description>another longtext</description> </variant> <variant> <first_order>4</first_order> <second_order>1</second_order> <ordercode>OUU777</ordercode> <name>option 5 name</name> <description>another longtext</description> <var_picture> <file>http://www.test.org/df/sdf2.j56</file> <name>variant Picture 1</name> <ending>jpg</ending> <resolution_x>20</resolution_x> <resolution_y>20</resolution_y> </var_picture> <var_picture> <file>http://www.test.org/df/sdf.j56</file> <ending>jpg</ending> <resolution_x>prelistening</resolution_x> <resolution_y>real</resolution_y> </var_picture> </variant> <variant> <first_order>5</first_order> <second_order>1</second_order> <ordercode>OIU899</ordercode> <name>option 5 name</name> <description>another longtext</description> </variant> </variants> <picture type="pkg" page="1"> <resolution_x>80</resolution_x> <resolution_y>60</resolution_y> <ending>jpg</ending> <filename>http://www.test.de/273/test.j34</filename> </picture> <variant_pic_exists>true</variant_pic_exists> <creation_date>2007-02-01</creation_date> <change_date>2007-05-01</change_date> </article> </articles>