Mysql parse xml stored procedure

Xml Store procedure used to insert the values.

Simple XML-formatted string in a MySQL stored procedure.

<Data>
                <parent>
                    <child1>Statement 1</child1>
                    <child2>Statement 2</child2>
                </parent>
                <parent>
                    <child1>Statement 3</child1>
                    <child2>Statement 4</child2>
                </parent>
</Data>

Create table Script.

DROP TABLE IF EXISTS `parent`;

CREATE TABLE parent (
    child1 varchar(400),
    child2 varchar(400)
);

Create Procedure.

DROP PROCEDURE XMLProc;
CREATE PROCEDURE XMLProc()
BEGIN
  DECLARE xmlDoc text;
  DECLARE i int;
  DECLARE count int;
  DECLARE child1 varchar(1000);
  DECLARE child2 varchar(1000);

  SET i = 1;
  SET xmlDoc = '<Data>
                <parent>
                    <child1>Statement 1</child1>
                    <child2>Statement 2</child2>
                </parent>
                <parent>
                    <child1>Statement 3</child1>
                    <child2>Statement 4</child2>
                </parent>
                </Data>';
  SET count = EXTRACTVALUE(xmlDoc, 'count(/Data/parent/child1)');

  WHILE i <= count DO
    INSERT INTO parent(child1,child2)
      SELECT
        EXTRACTVALUE(xmlDoc, '//parent[$i]/child1'),
        EXTRACTVALUE(xmlDoc, '//parent[$i]/child2');
    SET i = i + 1;
  END WHILE;
END

Execute Store Procedure

CALL XMLProc

select table.

SELECT * FROM parent

Result.