Re: need some help with a stored procedure to import XML into a table

[Gregory Tompkins]

Hi,

How do I attach the actual .sh file? I don't see the option in this form to attach an actual file.

I modified my .sh file and it looks like the following now. I still don't see in my procedure where it actually CREATES the temp table based on the XML file? Is this right?

Thanks for your help, I am pretty newbie at scripting and MySQL SPs. Thanks!

#!/bin/bash
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
xmlFILENAME="TEDhouseXML-$NOW.xml"
if [ -f $xmlFILENAME ]
then
echo "the file exists";
else
echo "the file does not exist"
wget --append-output=secslogfile.log --output-document=XML/$xmlFILENAME "http://ted5000/history/secondhistory.xml?MTU=0";
echo "its there now!, have a nice day :)"
fi
cat <<ZZZ >sql.txt
use ted5000;
set @xml :=(XML/$xmlFILENAME);
delimiter //
drop procedure if exists p_temp
//
create procedure p_temp(p_xml text)
begin
declare v_row_index int unsigned default 0;
declare v_row_count int unsigned;
declare v_xpath_row varchar(255);
-- calculate the number of row elements.
set v_row_count := extractValue(p_xml,'count(/History/SECOND)');
-- loop through all the row elements
while v_row_index < v_row_count do
set v_row_index := v_row_index +1;
set v_xpath_row := concat(
'/History/SECOND['
, v_row_index
, ']'
);
insert into temp values (
extractValue(p_xml,concat(v_xpath_row,'/SECOND[1]/text()'))
extractValue(p_xml,concat(v_xpath_row,'/SECOND[2]/text()'))
extractValue(p_xml,concat(v_xpath_row,'/SECOND[3]/text()'))
extractValue(p_xml,concat(v_xpath_row,'/SECOND[4]/text()'))
extractValue(p_xml,concat(v_xpath_row,'/SECOND[5]/text()'))
);
end while;
end;
ZZZ
mysql -u root -ppassword -D ted5000 -X <sql.txt