Tag Archives: PEMAIL

Linux

Reading XML in bash and importing in Mysql

Leave a comment

By garf0r

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.

Code:
#!/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:

Code:

20130409

1830
1900

bla
m00
street 10
0000/000000
subject
remarks

BARBRE5
TESTPERSON
TESTLOCATION
3702
45784139

20130412

1600
1730

bla2
m002
street 666
0000000000
andere

mailaddress2

BIRBRE5
TESTPERSON2
TESTLOCATION2
5802
4578487

From: http://www.unix.com/shell-programming-scripting/220839-reading-xml-bash-importing-mysql.html