Reading XML in bash and importing in Mysql

Shell Programming and Scripting
1

Hi,
I'm not a pro bashscript writer but I'm learning and want to learn about my mistakes.
In the next script I have an error on rule 6 but I can't find what I'm doing wrong ...
I daily receive a file xml.xml and have to import it in an mysql database in a few existing tables.

#!/bin/bash
FILE="/transfer/store/xml.xml"
RESULT=$(xmlstarlet sel -t -v "count(//Appointment)" $FILE)
_DB_TABLE_PERSONS="persons"
_DB_TABLE_INVITATIONS="invitiations"
FOR (( i=0; i<$RESULT; i++ )) ; DO
 PFIRST_NAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data1 $FILE)"
 PLAST_NAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data2 $FILE)"
 PADDRESS="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data3 $FILE)"
 PTELEPHONE="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data4 $FILE)"
 PEMAIL="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/email $FILE)"
VALUES(NULL,'${FIRST_NAME}','${LAST_NAME}','${ADDRESS}','${TELEPHONE}','${EMAIL}');"
 QUERY="INSERT INTO ${_DB_TABLE_PERSONS}  ('id','first_name','last_name','address','telephone','email')  VALUES(NULL,'${PFIRST_NAME}','${PLAST_NAME}','${PADDRESS}','${PTELEPHONE}','${PEMAIL}');"
 mysql --host=10.1.12.69 --user=test --password=test --database=test < $QUERY
 ID_PERSONS=$(mysql --host=10.1.12.69 --user=test --password=test --database=test -se "SELECT LAST_INSERT_ID()")
 IREMARK="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data5 $FILE)"
 ELAST_NAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/transfer-info/name $FILE)"
 ID_EMPLOYEES=$(mysql --host=10.1.12.69 --user=test --password=test  --database=test -se "SELECT id FROM employees WHERE  last_name=${ELAST_NAME}")
 LNAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/transfer-info/locatiebenaming $FILE)"
 ID_LOCATIONS=$(mysql --host=10.1.12.69 --user=test --password=test  --database=test -se "SELECT id FROM locations WHERE UCASE(name) like  '%${ELAST_NAME}%'")
 ID_I_TEMP="$(xmlstarlet sel -t -v //Appointment[${i}+1]/Date $FILE)"
 IT_I_TEMP="$(xmlstarlet sel -t -v //Appointment[${i}+1]/Time/Begin $FILE)"
 IDATE_INVITATION=${ID_I_TEMP:0:4}-${ID_I_TEMP:4:2}-${ID_I_TEMP:6:2}
 IEXPIRATION_DATE=${IT_I_TEMP:0:2}-${IT_I_TEMP:2:2}
 QUERY="INSERT INTO ${_DB_TABLE_INVITATIONS}  ('id','person','employee','location','date_invitation','time_invitation','notification','remark')   VALUES(NULL,'${PFIRST_NAME}','${PLAST_NAME}','${PADDRESS}','${PTELEPHONE}','${PEMAIL}');"
 mysql --host=10.1.12.69 --user=test --password=test --database=test < $QUERY
DONE

XML:

<?xml version="1.0" encoding="utf-8"?>
<transfer-export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <Appointment>
   <Date>20130409</Date>
   <Time>
     <Begin>1830</Begin>
     <End>1900</End>
   </Time>
   <person-info>
     <data1>bla</data1>
     <data2>m00</data2>
     <data3>street 10</data3>
     <data4>0000/000000</data4>
     <data5>subject</data5>
     <info>remarks</info>
     <email></email>
     <sms></sms>
   </person-info>
   <transfer-info>
     <dcode>BARBRE5</dcode>
     <name>TESTPERSON</name>
     <locatiebenaming>TESTLOCATION</locatiebenaming>
     <locatieID>3702</locatieID>
     <entryid>45784139</entryid>
   </transfer-info>
 </Appointment>
 <Appointment>
   <Date>20130412</Date>
   <Time>
     <Begin>1600</Begin>
     <End>1730</End>
   </Time>
   <person-info>
     <data1>bla2</data1>
     <data2>m002</data2>
     <data3>street 666</data3>
     <data4>0000000000</data4>
     <data5>andere</data5>
     <info></info>
     <email>mailaddress2</email>
     <sms></sms>
   </person-info>
   <transfer-info>
     <dcode>BIRBRE5</dcode>
     <name>TESTPERSON2</name>
     <locatiebenaming>TESTLOCATION2</locatiebenaming>
     <locatieID>5802</locatieID>
     <entryid>4578487</entryid>
   </transfer-info>
 </Appointment>
</transfer-export>
2

Did you mean line 6? the for loop syntax in shell script should be in small letters

for ((...)); do
  ...
done

--ahamed

3

On top of what ahamed101 says, this

VALUES(NULL,'${FIRST_NAME}','${LAST_NAME}','${ADDRESS}','${TELEPHONE}','${EMAIL}');"

I don't expect to work, nor this

mysql . . .  < $QUERY

, at least with the assignment to QUERY that you did in the line before. Should you have intended to use "here strings", use <<<