MySQL Forums
Forum List  »  Connector/Node.js

Re: Create a view from an XML field that one row per node in the XML field--is this possible?
Posted by: Albert Wal
Date: January 25, 2013 04:44AM

example from site:
http://prog.uvdb.eu/doc.php?id=3

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 ;

Options: ReplyQuote


Subject
Written By
Posted
Re: Create a view from an XML field that one row per node in the XML field--is this possible?
January 25, 2013 04:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.