How to store and retrieve XML data into MySQL

MySQL doesn’t support XML data types like Microsoft SQL Server or PostgreSQL.

Many times it is required to store multiple nodes of data into a single column, in the form of XML.

In the MySQL, You have to store XML in CLOB (Character Large Object) data type.

MySQL provides different types of XML function so using those functions, we can parse the XML data very easily.

Below is a full demonstration on storing and retrieval of XML data:

First store XML data like any other string:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE DATABASE `Employee`;   CREATE TABLE Employee.XMLTest ( XMLDATA TEXT );   INSERT INTO Employee.XMLTest VALUES (    '<Students>       <RollNumber>1</RollNumber>       <Name>Anvesh</Name>       <RollNumber>2</RollNumber>       <Name>Neevan</Name>    </Students>' );

Second, Parsing this data using below stored procedure:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

DELIMITER $$ CREATE PROCEDURE Employee.usp_ParseXMLData() BEGIN     DECLARE i INT DEFAULT 1;     DECLARE XMLText TEXT;     DECLARE RNumber VARCHAR(50) ;       DECLARE RName VARCHAR(50);                SET XMLText = (SELECT XMLDATA FROM Employee.XMLTest);                CREATE TEMPORARY TABLE Employee.TempXMLData     (             RollNumber VARCHAR(50)         ,Name VARCHAR(50)     );              SET RNumber = ExtractValue(XMLText, '//RollNumber[$i]');                    WHILE RNumber != "" DO                               SET RNumber = ExtractValue(XMLText, '//RollNumber[$i]');           SET RName = ExtractValue(XMLText, '//Name[$i]');             INSERT INTO Employee.TempXMLData SELECT RNumber, RName;         SET i = i+1;       END WHILE;             SELECT RollNumber         ,Name     FROM Employee.TempXMLData     WHERE RollNumber <>'';       DROP TABLE Employee.TempXMLData; END$$ DELIMITER ;

Call this stored procedure and check the result:

1	CALL Employee.usp_ParseXMLData();