Home All advices
ENPL
Modify date: 2013-01-15 11:38:23

Import data from xml into table

Below are shown how import data from xml into table

example of a solution

delimiter //
drop procedure if exists get_teams //
create procedure get_teams (in xml_data varchar(2000))
begin   
    declare i int default 1;
    declare j int default 1;
    declare k int default 1;
    declare i_no_l int default 0;
    declare i_no_t int default 0;
    declare league_name varchar(100);
    declare team_name varchar(100);    
    create temporary table leagues_teams (
        ls_id int unsigned not null default 0,    
        league_name varchar(100) not null,
        team_name varchar(100) not null);
    set @xml = xml_data;
    set i_no_l = extractvalue(@xml, 'count(leagues/league)');    
    while i <= i_no_l do
        set i_no_t = extractvalue(@xml, 'count(//league[$i]/teams/team)');
        set league_name = ExtractValue(@xml, '//league[$i]/@name');
        set j = 1;
        while j <= i_no_t do
            set team_name = extractvalue(@xml, '//league[$i]/teams/team[$j]/@name');            
            insert into leagues_teams (ls_id, league_name, team_name) values (k, league_name, team_name);       
            set j = j + 1;
            set k = k + 1;
        end while;        
        set i = i + 1;
    end while;      
    select * from leagues_teams order by league_name asc, team_name asc;
    drop table leagues_teams;
end //
delimiter ;

Below are shown how call procedure

example of a call

call get_teams('
<leagues>
<league name="bundesliga">
  <teams>
    <team name="bayer leverkusen"/>
    <team name="bayern monachium"/>
    <team name="borussia dortmund"/>
    <team name="schalke 04 gelsenkirchen"/>
  </teams>
</league>
<league name="ligue 1">
  <teams>
    <team name="girondins bordeaux"/>
    <team name="olympique lyon"/>
    <team name="olympique marsylia"/>
    <team name="paris saint germain"/>
  </teams>
</league>
<league name="premier league">
  <teams>
    <team name="arsenal londyn"/>
    <team name="chelsea londyn"/>
    <team name="liverpool fc"/>
    <team name="manchester city"/>
    <team name="manchester united"/>
  </teams>
</league>
<league name="primera division">
  <teams>
    <team name="atletico madryt"/>
    <team name="fc barcelona"/>
    <team name="real madryt"/>
    <team name="valencia cf"/>
  </teams>
</league>
<league name="serie a">
  <teams>
    <team name="ac milan"/>
    <team name="as roma"/>
    <team name="inter mediolan"/>
    <team name="juventus fc"/>
    <team name="lazio rzym"/>
  </teams>
</league>
</leagues>');

result table

numberleaguesteam
1 bundesliga bayer leverkusen
2 bundesliga bayern monachium
3 bundesliga borussia dortmund
4 bundesliga schalke 04 gelsenkirchen
5 ligue 1 girondins bordeaux
6 ligue 1 olympique lyon
7 ligue 1 olympique marsylia
8 ligue 1 paris saint germain
9 premier league arsenal londyn
10 premier league chelsea londyn
11 premier league liverpool fc
12 premier league manchester city
13 premier league manchester united
14 primera division atletico madryt
15 primera division fc barcelona
16 primera division real madryt
17 primera division valencia cf
18 serie a ac milan
19 serie a as roma
20 serie a inter mediolan
21 serie a juventus fc
22 serie a lazio rzym
[ Strona główna ]