We are planning for a database migration from MS SQL Server to MySQL. Most of our procedures in SQL Server are working with XML data, but we are issues where trying to convert these procedures to MySQL.[step=""]
Below is the sample XML file. We are sending XML data (not the XML file) directly as a input to the procedure.
<Attendance_Info Date="07/28/2010 00:00:00" Flag="1">
<Attendance Attendance="2" PersonalId="33304" SerialNumber="0"/>
<Attendance Attendance="2" PersonalId="33305" SerialNumber="1"/>
<Attendance Attendance="0" PersonalId="33306" SerialNumber="2"/>
<Attendance Attendance="2" PersonalId="33307" SerialNumber="3"/>
<Attendance Attendance="2" PersonalId="33308" SerialNumber="4"/>
<Attendance Attendance="2" PersonalId="33309" SerialNumber="5"/>
<Attendance Attendance="2" PersonalId="33310" SerialNumber="6"/>
<Attendance Attendance="2" PersonalId="33311" SerialNumber="7"/>
<Attendance Attendance="2" PersonalId="33312" SerialNumber="8"/>
<Attendance Attendance="2" PersonalId="33313" SerialNumber="9"/>
<Attendance Attendance="2" PersonalId="33314" SerialNumber="10"/>
<Attendance Attendance="2" PersonalId="33377" SerialNumber="11"/>
<Attendance Attendance="2" PersonalId="33651" SerialNumber="12"/>
<Attendance Attendance="2" PersonalId="33652" SerialNumber="13"/>
<Attendance Attendance="2" PersonalId="33734" SerialNumber="14"/>
<Attendance Attendance="2" PersonalId="34086" SerialNumber="15"/>
<Attendance Attendance="2" PersonalId="34170" SerialNumber="16"/>
</Attendance_Info>
[/step]
With the XML we want the data in the following tabular format in MySQL[step=""]
Date | Attendance | Personal_Id
[/step]
SET nrows = ExtractValue(xml_parm, 'count(//Attendance_Info/Attendance)');
SET rownum = 1;
WHILE rownum <= nrows DO
SET At_Attendance = ExtractValue(xml_parm, '//Attendance_Info/Attendance[$rownum]/@Attendance');
SET At_PersonalId = ExtractValue(xml_parm, '//Attendance_Info/Attendance[$rownum]/@PersonalId');
SET rownum = rownum + 1;
END WHILE;
extractvalue(@xml, '//child::*[1]/@PersonalId')
extractvalue(@xml, '/*/@Date')
[/step]
drop table if exists attendance;
create table `attendance` (
`att_date` datetime,
`att_attend_id` int(11),
`att_person_id` int(11),
`att_serial_no` int(11),
`att_id` bigint(20) not null auto_increment,
primary key (`att_id`)
) engine=InnoDB default charset=latin1;
...the iterative procedure may look something like this:
drop procedure if exists InsertAttendanceXmlProc;
delimiter //
create procedure InsertAttendanceXmlProc(in xml varchar(8000))
begin
declare dt datetime;
declare nrows int;
declare rownum int default 1;
-- to be safe, explicitly convert date from xml to datetime - expects mm/dd/yyyy hh:mi:ss
-- http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
set dt = str_to_date(coalesce(nullif(extractvalue(xml, '//@Date'), ''), now()), '%m/%d/%Y %H:%i:%s');
set nrows = extractvalue(xml, 'count(//Attendance)');
-- decrement nrows to save on creating another variable
while rownum <= nrows do
insert into attendance(att_date, att_attend_id, att_person_id, att_serial_no)
select dt
, coalesce(nullif(extractvalue(xml, '//child::*[$rownum]/@Attendance'), ''),0)
, coalesce(nullif(extractvalue(xml, '//child::*[$rownum]/@PersonalId'), ''),0)
, coalesce(nullif(extractvalue(xml, '//child::*[$rownum]/@SerialNumber'), ''),0);
set rownum = rownum + 1;
end while;
end//
delimiter ;
As shown in the question thread, it is also a possibility, if the sequential order of XML nodes doesn't matter, to use the same variable used to store count of rows as index for our while loop but using a decrement approach instead.
set @xml = '<Attendance_Info Date="07/28/2010 00:00:00" Flag="1">
<Attendance Attendance="2" PersonalId="33304" SerialNumber="0"/>
<Attendance Attendance="2" PersonalId="33305" SerialNumber="1"/>
<Attendance Attendance="0" PersonalId="33306" SerialNumber="2"/>
<Attendance Attendance="2" PersonalId="33307" SerialNumber="3"/>
<Attendance Attendance="2" PersonalId="33308" SerialNumber="4"/>
<Attendance Attendance="2" PersonalId="33309" SerialNumber="5"/>
<Attendance Attendance="2" PersonalId="33310" SerialNumber="6"/>
<Attendance Attendance="2" PersonalId="33311" SerialNumber="7"/>
<Attendance Attendance="2" PersonalId="33312" SerialNumber="8"/>
<Attendance Attendance="2" PersonalId="33313" SerialNumber="9"/>
<Attendance Attendance="2" PersonalId="33314" SerialNumber="10"/>
<Attendance Attendance="2" PersonalId="33377" SerialNumber="11"/>
<Attendance Attendance="2" PersonalId="33651" SerialNumber="12"/>
<Attendance Attendance="2" PersonalId="33652" SerialNumber="13"/>
<Attendance Attendance="2" PersonalId="33734" SerialNumber="14"/>
<Attendance Attendance="2" PersonalId="34086" SerialNumber="15"/>
<Attendance Attendance="2" PersonalId="34170" SerialNumber="16"/>
</Attendance_Info>';
call InsertAttendanceXmlProc( @xml );
[/step]
set @xml = '<Attendance_Info Date="07/28/2010 00:00:00" Flag="1">
<Attendance Attendance="2" PersonalId="33304" SerialNumber="0"/>
<Attendance Attendance="2" PersonalId="33305" SerialNumber="1"/>
<Attendance Attendance="0" PersonalId="33306" SerialNumber="2"/>
<Attendance Attendance="2" PersonalId="33307" SerialNumber="3"/>
<Attendance Attendance="2" PersonalId="33308" SerialNumber="4"/>
<Attendance Attendance="2" PersonalId="33309" SerialNumber="5"/>
<Attendance Attendance="2" PersonalId="33310" SerialNumber="6"/>
<Attendance Attendance="2" PersonalId="33311" SerialNumber="7"/>
<Attendance Attendance="2" PersonalId="33312" SerialNumber="8"/>
<Attendance Attendance="2" PersonalId="33313" SerialNumber="9"/>
<Attendance Attendance="2" PersonalId="33314" SerialNumber="10"/>
<Attendance Attendance="2" PersonalId="33377" SerialNumber="11"/>
<Attendance Attendance="2" PersonalId="33651" SerialNumber="12"/>
<Attendance Attendance="2" PersonalId="33652" SerialNumber="13"/>
<Attendance Attendance="2" PersonalId="33734" SerialNumber="14"/>
<Attendance Attendance="2" PersonalId="34086" SerialNumber="15"/>
<Attendance Attendance="2" PersonalId="34170" SerialNumber="16"/>
</Attendance_Info>';
select extractvalue(@xml, '//@Date') as att_date
, extractvalue(@xml, '//child::*[$@rownum]/@Attendance') as att_attend_id
, extractvalue(@xml, '//child::*[$@rownum]/@PersonalId') as att_person_id
, extractvalue(@xml, '//child::*[$@rownum]/@SerialNumber') as att_serial_no
from util.numbers
where n between 1 and extractvalue(@xml, 'count(//Attendance)')
and @rownum:=n
;
With luck, you will see that results of this query are similar to what our procedure added to the attendance table during our stored procedure.
drop procedure if exists InsertAttendanceXmlProc2;
delimiter //
create procedure InsertAttendanceXmlProc2(in xml varchar(8000))
begin
insert into attendance(att_date, att_attend_id, att_person_id, att_serial_no)
select str_to_date(coalesce(nullif(extractvalue(@xml, '//@Date'), ''), now()), '%m/%d/%Y %H:%i:%s')
, coalesce(nullif(extractvalue(@xml, '//child::*[$@rownum]/@Attendance'), ''),0)
, coalesce(nullif(extractvalue(@xml, '//child::*[$@rownum]/@PersonalId'), ''),0)
, coalesce(nullif(extractvalue(@xml, '//child::*[$@rownum]/@SerialNumber'), ''),0)
from util.numbers
where n between 1 and extractvalue(@xml, 'count(//Attendance)')
and @rownum:=n
;
end//
delimiter ;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (10)
Commented:
While you're at it, add a "parameter" tag and some tag that would indicate this applies to stored procs and user-defined functions.
Commented:
Author
Commented:Author
Commented:A MySQL Tidbit: In-line CSV Parsing
New Related MySQL Tidbit:
A MySQL Tidbit: Dynamic Export To_XML Stored Procedure
Commented:
My question is I have XMLData I am trying to insert into 2 tables.
Open in new window
I can insert data into the first table which has no problem but the second table has a problem. Each Level has child elements " Questions " I unable to insert child data into the second table can you please help me.
Thanks in Advance.
View More