Want to parse the XML File in MySQL (using predefined function/procedures) and insert it into Table

Question

I have a XML File pasted below. I want to parse it in MySQL.

1) I referred some link[1] in which first we have to load the XML file and insert it in the table.

[1] - https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

2) And also I read that fetch the value using ExtractValue function, but i am getting output as NULL

ExtractValue(@xml, -here-node-path);

Here's XML File:

<?xml version="1.0" encoding="utf-8"?>
<ItemData>
    <Rows>
        <VRow ID="ba3c4fd9-6691-49ee-996a-9841810d8264" ItemType="Pulse" />
        <VRow ID="401682df-9839-456e-b08f-563361392530" ItemType="Height" />
        <VRow ID="c39ee7ab-7217-4750-bc0d-9cec495fdd41" ItemType="Weight" />
        <VRow ID="effabbcb-718f-4b0c-8f81-6d0bf4ba5028" ItemType="BloodPressure" />
        <VRow ID="eb6451d3-646a-4447-919a-f778daf6fdc5" ItemType="BodyMassIndex" />
    </Rows>
    <Groups>
        <VGroup ID="4535bf31-da00-47e8-8975-f21a1b3fdb62" ReadingDate="2009-07-24T14:26:28.50Z">
            <Notes />
            <Readings>
                <VitalReading ID="af0af8e1-41d4-4cc9-a042-7a33876b643e" ItemType="Pulse">
                    <Values>
                        <ValueItem Type="{302DABB8-BF22-4da1-BE2F-8213F8A191D8}" ID="f46322d9-2e15-4542-ad33-d37395dfe31b" Initialized="True">
                            <Pulse>80</Pulse>
                        </ValueItem>
                    </Values>
                </VitalReading>                 
            </Readings>
        </VGroup>
    </Groups>
</ItemData>

Can anyone suggest me some solution over this?

score 3 · Answer 1 · edited Feb 22 '16 at 13:03

Chances are that you won't know how many records will be imported on each run. Therefore the row count is essential to be able to iterate over every XML row. In the while loop we have to increment the row index by one in order to retrieve the current XML row. One specific row can be fetched using the square array brackets [n], where n is the v_row_index. The @* after the last forward slash tells ExtractValue() to fetch the entire contents of the node. We can then access each attribute in the same way as the rows, again using the square array brackets [n]. The Insert statement syntax that I used here does not include the column names. As such, each parameter is inserted in order.

    declare v_row_index int unsigned default 0;   
    while v_row_index < v_row_count do                
        set v_row_index = v_row_index + 1;        
        set v_xpath_row = concat(node, '[', v_row_index, ']/@*');

        insert into applicants values (
            extractValue(xml_content, concat(v_xpath_row, '[1]')),
            extractValue(xml_content, concat(v_xpath_row, '[2]')),
            extractValue(xml_content, concat(v_xpath_row, '[3]'))
        );
    end while;
Here is the full proc code:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `import_applicant_xml`(path varchar(255), node varchar(255))
BEGIN
    declare xml_content text;
    declare v_row_index int unsigned default 0;   
    declare v_row_count int unsigned;  
    declare v_xpath_row varchar(255); 

    set xml_content = load_file(path);

    -- calculate the number of row elements.   
    set v_row_count  = extractValue(xml_content, concat('count(', node, ')')); 

    -- loop through all the row elements    
    while v_row_index < v_row_count do                
        set v_row_index = v_row_index + 1;        
        set v_xpath_row = concat(node, '[', v_row_index, ']/@*');
        insert into applicants values (
            extractValue(xml_content, concat(v_xpath_row, '[1]')),
            extractValue(xml_content, concat(v_xpath_row, '[2]')),
            extractValue(xml_content, concat(v_xpath_row, '[3]'))
        );
    end while;
END
Performing a Test Run

Calling the proc is just a matter of using the call command with the proc name and two input parameters.   (Remember to escape backslashes in the file path on Windows platforms.)

MySQL> call import_applicants_xml('C:\\applicants1.xml', '/applicant_list/applicant');
MySQL> select * from applicants;
+---+----------+-------------+
 |1  |Rob       |Gravelle     |
 +---+----------+-------------+
 |2  |Al        |Bundy        |
 +---+----------+-------------+
 |3  |Little    |Richard      |
 +---+----------+-------------+
 3 row(s) returned

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

Here is a tutorial which clearly explain you how to use MySQL predefined xml functions

Ref Link

Other stackoverflow sports this out stackoverflow link

edited May 23 '17 at 10:29

Community

1
1

answered Jan 04 '13 at 09:24

Shiva Komuravelly

3,252
1
15
16

I also referred that link, but for the path of nodes I am giving, getting NULL Value. I tried like this "SELECT ExtractValue(xmlDoc,'//VitalReading[1]');". Please correct me if I am wrong in the syntax after referring My above XML FILE. – Bhavesh Shah Jan 04 '13 at 09:46
One more thing is how to get the value from Node Attribute? Like for VitalReading I want ItemType? – Bhavesh Shah Jan 04 '13 at 09:51
that mean you want the complete inner xml or what ... like complete values tag – Shiva Komuravelly Jan 04 '13 at 09:56
not complete... but somewhat like ItemType and Pulse in this case. – Bhavesh Shah Jan 04 '13 at 10:00
Just check this pdf which has the solution, But really wonderful question buddy. [PDF File](http://ict.udlap.mx/people/carlos/is346/files/XMLXpathSupportInMySQL.pdf) – Shiva Komuravelly Jan 04 '13 at 10:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22172/discussion-between-shiva-komuravelly-and-bhavesh-shah) – Shiva Komuravelly Jan 04 '13 at 10:37

Want to parse the XML File in MySQL (using predefined function/procedures) and insert it into Table

Here's XML File:

2 Answers2