Load xml into mysql using values from another table -

i have mysql database want populate xml data want information change depending on value in table.

the xml file looks follows:

d<datarow num="1"> <film_date>20/04/2013</film_date>  <film_name>days of thunder</film_name>  <film_revenue>100</ film_revenue > d<datarow num="2"> <film_date>21/04/2013</film_date>  <film_name>top gun</film_name>  <film_revenue>120</ film_revenue >  d<datarow num="3"> <film_date>20/04/2013</film_date>  <film_name> mission impossible </film_name>  <film_revenue>100</ film_revenue > 

for instance want load xml file film titles , dates film details table

load xml local infile ‘c:\film_details.xml table film details 

i don’t want load film name table film details rather use id table called film titles

+----+--------------------+ | id |     film name      | +----+--------------------+ |  1 | vanilla sky        | |  2 | days of thunder    | |  3 | top gun            | |  4 | mission impossible | +----+--------------------+ 

i want film details table follows

+------------+-----------+---------+ |    date    | film name | revenue | +------------+-----------+---------+ | 20/04/2013 |         2 |     100 | | 21/04/2013 |         3 |     120 | | 20/04/2013 |         4 |     100 | +------------+-----------+---------+ 

please note film name column in table film details different in xml (missing “_”)

is there way manipulate xml data before inserted mysql database?

it idea if load xml file temporary table first. after that, simple query should enough grab code reference table.

this way can solve both issues have. foreign key , different field names.