ExtractValue very slow XML processing & Parsing in mysql

Question

We are processing a large amount of XML using ExtractValue(xml,xpath) and loop on our XML. e.g. ExtractValue(p_xml, concat(xpath,'/ROOT/TABLE[0]/')),

While (20k)
 INSERT INTO select_keys(key)
 Values (ExtractValue(p_xml, concat(xpath,'key',[counter])));
End While

The XML processing to data table becomes terribly slow when rows are more than thousands.

CALL `MT_PROC`
(181, 0, '<ROOT></ROOT>', '<ROOT>
<TABLE><keys>f5d29464-e941-4cdf-a21b-984add68dce9</keys></TABLE>
<TABLE><keys>731d23a1-0438-4f72-91a5-83288f6e93b4</keys></TABLE>
<TABLE><keys>c68a4fb8-6306-4728-8b55-f26a151ffad4</keys></TABLE>
<TABLE><keys>3f5ec4bb-2109-4876-a419-b07b553081cc</keys></TABLE>
<TABLE><keys>4689bc20-9eec-4f6b-a454-9a30b7080cc2</keys></TABLE>
<TABLE><keys>466a7da5-7a9e-419f-b1fc-dcd57931a095</keys></TABLE>
....20K rows
</ROOT>') 

What is the solution for this problem.

Answer 1

the answer you provided is vulnerable to MySQL injection attack. I am also looking at how i can improve performance when extracting values from xml. But concatenating strings to create commands seems like asking for trouble..

Just to give you an example of such attack (tested)

If the values inside the xml can somehow be manipulated by the user, a malicious user might provide a value similar to this:

normalvaluehere\');select * from atablehere-- 

(with a space at the end) that will result in

select * from atablehere 

being executed.

Did you guys find another way to do that?

Thanks!

Answer 2

Faster xml processing with direct inserts instead of ExtractValue. Use query to directly insert data in table as below.

if p_xml is not null and p_xml <> '<ROOT></ROOT>' 
        then
            /*set CountMAX  = ExtractValue(p_xml, 'count(/ROOT/TABLE)');
            set Counter=1;

                while(Counter<=CountMAX) DO
                set xpath=concat('/ROOT/TABLE[',Counter,']/');
                    -- inserting values into the temp table
                    INSERT INTO select_keys(report_key)   Values (
                    ExtractValue(p_xml, concat(xpath,'report_key'))
                     );
                set Counter = Counter + 1;
               END WHILE;
               */               
    set p_xml = replace(replace(p_xml,'<TABLE><report_key>','('''),'</report_key></TABLE>','''),');            
    set p_xml = replace(replace(p_xml,'<ROOT>',''),',</ROOT>','');

    set @stmt_str = concat('INSERT INTO select_keys(report_key) values ' ,p_xml);
    prepare stmt from @stmt_str;
    execute stmt;
    deallocate prepare stmt;
END If;