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 sceario 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>'
SELECT * FROM MyTable (whatever my temp table is)
results "should be" be:
firstname lastname
Magic Johnson
John Smith
Thanks in advance,
J