XML to MySQL conversion & automation scripts

Our distributor provides inventory data via XML files with daily updates via FTP that we would like to make automatically available in a MySQL database on our webserver.

The project deliverables include:

- provide script to convert initial XML files into MySQL database

- provide script that downloads update files from an FTP server once a day automatically and add/update/delete data in MySQL database

- email notification to specified email account upon completion of script including any errors.

- fully commented source code

Data comes in ZIP files with the following naming convention:

<date>_<message_id>_<internal_key>.xml.z

<date>: 8-digit, YYYYMMDD

<message-id>: 8-digit, numeric, unique

<internal_key>: 4-digit, alpha

Detailed XML documentation, DTD and schema ([login to view URL] - Version 2.1) as well as testdata will be provided.

The resulting MySQL DB should include the following elements in a single table structure:

Product_Manufacturer:

in <product> <publisher>

where <b291> equals "01"

Product_Manufacturer is in <b081>

Product_Name:

in <product> <title>

where <b202> equals "01"

Product_Name is in <b203> and concatenated with ":"<space> and <b029> if filled

Product_Price:

in <product> <supplydetail> <price>

where <j148> equals "04" and <b251> equals "DE"

Product_Price is in <b151>

Product_Description:

in any <product> <othertext>

Product_Description is in any <d104>

concatenated with <CR><LF><CR><LF>

Author:

in <product> <contributor>

where <b035> equals "A01"

Author is in <b037>

Format:

in <product>

Format is in <b012>

Pages:

in <product>

Pages is in <b061>

Product_Model:

in <product> <productidentifier>

where <b221> equals "03"

Product_Manufacturer is in <b244>

strip all dashes

Product_Image:

in <product> <productidentifier>

where <b221> equals "01" and <b233> equals "KNV"

Product_Image is in <b244>

Weight:

in <product> <measure>

where <c093> equals "08" and <c095> equals "gr"

Weight is in <c094>

Records should be added, modified or deleted based on the <a001> record identifier in the <product> element. <a002> includes the type of record

<a002> equals "004" means a partial update of a record and the appropriate field of the original record should be overwritten with the new values.

<a002> equals "005" means the record should be deleted

all other <a002> values should create a new record if none exists already.

The daily update files are about 10-25 MB big. Attached is the beginning of one of the daily update files. One thing to mention is that not every product element will include all defined fields.

Please bid only if you are absolute comfortable that you can fulfill this request considering all the information above. All rights to the code and any related elements belong to the project poster.

******

<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE ONIXmessage SYSTEM "[login to view URL]">

<ONIXmessage release="2.1">

<header>

<m172>4026725000008</m172>

<m174>KNV</m174>

<m175>Angelika Rausch</m175>

<m283></m283>

<m182>200802090158</m182>

<m184>ger</m184>

<m186>EUR</m186>

</header>

<product>

<a001>KNV1972090700061</a001>

<a002>04</a002>

<a194>03</a194>

<a197>KNV</a197>

<productidentifier>

<b221>03</b221>

<b244>978-3-15-004391-2</b244>

</productidentifier>

<productidentifier>

<b221>01</b221>

<b233>KNV</b233>

<b244>596</b244>

</productidentifier>

<b012>BC</b012>

<productclassification>

<b274>01</b274>

<b275>49019900009</b275>

</productclassification>

<series>

<title>

<b202>01</b202>

<b203>Reclam Universal-Bibliothek</b203>

</title>

<b019>Nr.4391</b019>

</series>

<title>

<b202>01</b202>

<b203>Libussa</b203>

<b029>Trauerspiel in 5 Aufzügen</b029>

</title>

<title>

<b202>10</b202>

<b203>Reclam UB 04391 [login to view URL]</b203>

</title>

<contributor>

<b034>1</b034>

<b035>A01</b035>

<b037>Grillparzer, Franz</b037>

</contributor>

<mainsubject>

<b191>26</b191>

<b069>21500</b069>

</mainsubject>

<mainsubject>

<b191>26</b191>

<b068>2.0</b068>

<b069>21520</b069>

</mainsubject>

<publisher>

<b291>01</b291>

<b081>Reclam, Ditzingen</b081>

</publisher>

<measure>

<c093>08</c093>

<c094>50</c094>

<c095>gr</c095>

</measure>

<supplydetail>

<supplieridentifier>

<j345>06</j345>

<b244>4026725000008</b244>

</supplieridentifier>

<j137>KNV</j137>

<j292>04</j292>

<j396>99</j396>

<price>

<j148>04</j148>

<j267>32.5</j267>

<j151>2.6</j151>

<b251>DE</b251>

<j153>R</j153>

</price>

<price>

<j148>04</j148>

<j151>2.7</j151>

<b251>AT</b251>

</price>

<price>

<j148>04</j148>

<j151>4.8</j151>

<j152>CHF</j152>

<b251>CH</b251>

</price>

</supplydetail>

</product>

<product>

<a001>KNV1972091800035</a001>

<a002>04</a002>

<a194>03</a194>

<a197>KNV</a197>

<productidentifier>

<b221>03</b221>

<b244>978-3-15-003071-4</b244>

</productidentifier>

<productidentifier>

<b221>01</b221>

<b233>KNV</b233>

<b244>5167</b244>

</productidentifier>

<b012>BC</b012>

<productclassification>

<b274>01</b274>

<b275>49019900009</b275>

</productclassification>

<series>

<title>

<b202>01</b202>

<b203>Reclam Universal-Bibliothek</b203>

</title>

<b019>Nr.3071</b019>

</series>

<title>

<b202>01</b202>

<b203>Die Entstehung der Arten durch natürliche Zuchtwahl</b203>

<b029>Nachw. v. Gerh. Heberer</b029>

</title>

<title>

<b202>10</b202>

<b203>Reclam UB 03071 [login to view URL] Arten</b203>

</title>

<contributor>

<b034>1</b034>

<b035>A01</b035>

<b037>Darwin, Charles</b037>

</contributor>

<mainsubject>

<b191>26</b191>

<b069>26730</b069>

</mainsubject>

<mainsubject>

<b191>26</b191>

<b068>2.0</b068>

<b069>26710</b069>

</mainsubject>

<subject>

<b067>20</b067>

<b070>Art (Biologie)</b070>

</subject>

<subject>

<b067>20</b067>

<b070>Biologische Evolution</b070>

</subject>

<subject>

<b067>20</b067>

<b070>Stammesgeschichte</b070>

</subject>

<subject>

<b067>20</b067>

<b070>Zucht / züchten</b070>

</subject>

<subject>

<b067>24</b067>

<b171>KNV type of article</b171>

<b069>00</b069>

</subject>

<publisher>

<b291>01</b291>

<b081>Reclam, Ditzingen</b081>

</publisher>

<measure>

<c093>08</c093>

<c094>295</c094>

<c095>gr</c095>

</measure>

<supplydetail>

<supplieridentifier>

<j345>06</j345>

<b244>4026725000008</b244>

</supplieridentifier>

<j137>KNV</j137>

<j292>04</j292>

<j396>31</j396>

<price>

<j148>04</j148>

<j267>32.5</j267>

<j151>14.6</j151>

<b251>DE</b251>

<j153>R</j153>

</price>

<price>

<j148>04</j148>

<j151>15</j151>

<b251>AT</b251>

</price>

<price>

<j148>04</j148>

<j151>26.3</j151>

<j152>CHF</j152>

<b251>CH</b251>

</price>

</supplydetail>

</product>

<product>

<a001>KNV1972092200014</a001>

<a002>04</a002>

<a194>03</a194>

<a197>KNV</a197>

<productidentifier>

<b221>03</b221>

<b244>978-3-15-000062-5</b244>

</productidentifier>

<productidentifier>

<b221>01</b221>

<b233>KNV</b233>

<b244>7343</b244>

</productidentifier>

<b012>BC</b012>

<productclassification>

<b274>01</b274>

<b275>49019900009</b275>

</productclassification>

<series>

<title>

<b202>01</b202>

<b203>Reclam Universal-Bibliothek</b203>

</title>

<b019>Nr.62</b019>

</series>

<title>

<b202>01</b202>

<b203>König Richard III.</b203>

<b029>Übertr. v. August W. v. Schlegel. Hrsg. v. Dietrich Klose</b029>

</title>

<title>

<b202>10</b202>

<b203>Reclam UB 00062 [login to view URL] III</b203>

</title>

<contributor>

<b034>1</b034>

<b035>A01</b035>

<b037>Shakespeare, William</b037>

<b044>William Shakespeare (1564-1616) gilt als einer der größten Dichter und Dramatiker der Weltgeschichte. Er verfasste zahlreiche Dramen, Tragödien, Komödien und Gedichte, mit denen er schon zu Lebzeiten Anerkennung und Wohlstand errang. Aber erst in den folgenden Jahrhunderten wurde er zum Prototypen des literarischen Genies, ohne den die Entwicklung der neueren Literatur von Goethe über Brecht bis in die Gegenwart hinein undenkbar ist.</b044>

</contributor>

<b061>120</b061>

<b255>120</b255>

<mainsubject>

<b191>26</b191>

<b069>21500</b069>

</mainsubject>

<mainsubject>

<b191>26</b191>

<b068>2.0</b068>

<b069>21520</b069>

</mainsubject>

<subject>

<b067>20</b067>

<b070>Richard III.</b070>

</subject>

<subject>

<b067>24</b067>

<b171>KNV type of article</b171>

<b069>01</b069>

</subject>

<publisher>

<b291>01</b291>

<b081>Reclam, Ditzingen</b081>

</publisher>

<b087>1971</b087>

<measure>

<c093>08</c093>

<c094>62</c094>

<c095>gr</c095>

</measure>

<supplydetail>

<supplieridentifier>

<j345>06</j345>

<b244>4026725000008</b244>

</supplieridentifier>

<j137>KNV</j137>

<j292>04</j292>

<j396>99</j396>

<price>

<j148>04</j148>

<j267>32.5</j267>

<j151>3</j151>

<b251>DE</b251>

<j153>R</j153>

</price>

<price>

<j148>04</j148>

<j151>3.1</j151>

<b251>AT</b251>

</price>

<price>

<j148>04</j148>

<j151>5.6</j151>

<j152>CHF</j152>

<b251>CH</b251>

</price>

</supplydetail>

</product>

</ONIXmessage>

Apache MySQL XML

Project ID: #675757

About the project