XML or Array Parameter

[jshurst]

I've been searching around for this but haven't found a solution that is satisfactory. I'm coming from a SQL Server background and new to MySQL, but this is a scenario that is quite common for me.

I have an application which I need to pass an array to a stored proc in the database. I want to pass an array so I don't have to make multiple calls. Usually in SQL Server I'll construct some XML then use the OpenXML() function and basically cast it to a temp table (or table variable). This is extremely convenient.

I've seen the ExtractValue() function in MySQL, but so far can't get it to accomplish the same thing because it will put multiple values into the same row/column ( will say this is a very cool function though). Can someone tell me how to get multiple values into a table with 1 DB call?

Ex. Say this is my character parameter.

'<books> 
<author> 
<firstname>Magic</firstname> 
<lastname>Johnson</lastname> 
</author> 
<author> 
<firstname>John</firstname> 
<lastname>Smith</lastname> 
</author> 
</books>' 

[code]

results "should be" be: 

[code]

SELECT * FROM MyTable (whatever my temp table is) 

firstname lastname 
Magic Johnson 
John Smith

Thanks in advance,

J

 

[tsuji]

Something like this can be done.
[tt]
set @xml:='<books>
<author>
<firstname>Magic</firstname>
<lastname>Johnson</lastname>
</author>
<author>
<firstname>John</firstname>
<lastname>Smith</lastname>
</author>
</books>';

delimiter //
create procedure my_extractvalue(
IN `xml_data` varchar(65535)
)
begin
declare i int default 1;
declare firstname varchar(50);
declare lastname varchar(50);
declare nmax int default 0;
set nmax:=ExtractValue(@xml, 'count(//author)');

create temporary table t (
pos int unsigned not null default 1,
first_name varchar(50) null,
last_name VARCHAR(50) null
);

while i<=nmax do
set firstname:=ExtractValue(xml_data, '//author[$i]/firstname');
set lastname:=ExtractValue(xml_data, '//author[$i]/lastname');
insert into t (select i, firstname, lastname);
set i=i+1;
end while;
select * from t;
drop table t;
end //
delimiter ;

call my_extractvalue(@xml);
drop procedure if exists my_extractvalue;
[/tt]