This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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 ( ' ' ); |
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(); |
