Exporting Mysql data to XML file

Using Mysql Procedure and function to exporting the mysql data into xml file

# --------------------------------------------------------
# Host:                         localhost
# Server version:               5.5.16
# Server OS:                    Win32
# HeidiSQL version:             6.0.0.3603
# Date/time:                    2013-03-12 12:52:19
# --------------------------------------------------------
=================================================================
 Step -1 (creating the table structure)
# Dumping database structure for sample
CREATE DATABASE IF NOT EXISTS `sample`;
USE `sample`;


# Dumping structure for table sample.employee
CREATE TABLE IF NOT EXISTS `employee` (
  `EmpId` int(11) DEFAULT NULL,
  `EmpName` varchar(10) DEFAULT NULL,
  `EmpSSN` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

====================================================
 Step -2 (Inserting the table records)

# Dumping data for table sample.employee: ~11 rows (approximately)
INSERT INTO `employee` (`EmpId`, `EmpName`, `EmpSSN`) VALUES
    (1, 'Jack', '555-55-5555'),
    (2, 'Joe', '555-56-5555'),
    (3, 'Fred', '555-57-5555'),
    (4, 'Mike', '555-58-5555'),
    (5, 'Cathy', '555-59-5555'),
    (6, 'Lisa', '555-70-5555'),
    (7, 'Jack', '555-55-5555'),
    (8, 'Mike', '555-58-5555'),
    (9, 'Cathy', '555-59-5555'),
    (10, 'Lisa', '555-70-5555'),
    (11, 'Lisa', '555-70-5555');

=========================================================================
 Step -3 (Procedure for exporting the data as .xml file)

# Dumping structure for procedure sample.myproc
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`(IN `employee_id` VARCHAR(50), IN `xml_path` VARCHAR(50))
BEGIN
    -- Define xml pretags for export
     SET @xmlPreStart = "<resultset xmlns:xsi=http://www.w3.org/2001/XMLSchema- instance > \n";
     SET @xmlPreEnd = "</resultset>";
    
     -- Define the columns to be export
    SET @col1 = " GROUP_CONCAT( '<row>\n', xml_tag('EmpId',EmpId,null,null), ";
   SET @col2 = " xml_tag('EmpName',EmpName,null,null),'</row>\n' SEPARATOR '' )";
  
     -- Check the EmpId parameter for filter the result   
    IF employee_id <> '' THEN
        SET @WhereCon = CONCAT(" WHERE EmpId IN(", REPLACE(employee_id, ";", "','"),")");
    ELSE
        SET @WhereCon = " ";
    END IF;
   
    -- Concatenating the XML pretag and colums to form the query
   SET @sqlstring =  CONCAT("SELECT  @xmlPreStart UNION ALL SELECT " ,@col1, @col2,                              
                            " FROM employee  ", @WhereCon
                           );
     -- Concatenating the query to export as .XML file
    SET @sqlstringFinal = CONCAT("SELECT  * INTO OUTFILE '",
                           REPLACE(xml_path, '\\', '\\\\'),
                           "' LINES TERMINATED BY '\r\n' FROM (",
                           @sqlstring,
                           " UNION ALL SELECT  @xmlPreEnd ) derived");
                          
   -- Execute dynamic SQL string.
   PREPARE dySQL FROM @sqlstringFinal;
   EXECUTE dySQL;
   DEALLOCATE PREPARE dySQL;
  
END//
DELIMITER ;
======================================================================
Step -4 (Function to handling the attributes)

# Dumping structure for function sample.xml_attr
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `xml_attr`(tagname VARCHAR(2000),
tagvalue VARCHAR(2000)) RETURNS varchar(2000) CHARSET latin1
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN CONCAT(' ', tagname ,'="',xml_escape(tagvalue),'" ');
END//
DELIMITER ;

====================================================================

Step -5 (Handling escape string)
# Dumping structure for function sample.xml_escape
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `xml_escape`( tagvalue VARCHAR(2000)) RETURNS varchar(2000) CHARSET latin1
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
tagvalue,'&','&amp;'),
'<','&lt;'),
'>','&gt;'),
'"','&quot;'),
'\'','&apos;');
END//
DELIMITER ;
====================================================================
Step -6 (Function to building the row columns)
# Dumping structure for function sample.xml_tag
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `xml_tag`(`tagname` VARCHAR(2000), `tagvalue` VARCHAR(2000), `attrs` VARCHAR(2000), `subtags` VARCHAR(2000)) RETURNS varchar(2000) CHARSET latin1
BEGIN
DECLARE result VARCHAR(2000);
SET result = CONCAT(' <field name = "' , tagname,'"');
IF attrs IS NOT NULL THEN
SET result = CONCAT(result,' ', attrs);
END IF;
IF (tagvalue IS NULL AND subtags IS NULL) THEN
RETURN CONCAT(result,' />');
END IF;
RETURN CONCAT(result ,'>',ifnull(xml_escape(tagvalue),''),
ifnull(subtags,''),'</field>\n');
END//
DELIMITER ;
===================================================================
Step -7 (Executing the procedure)

Check the output using folowing commands

call myproc ("1,2,3,4","D:\\Rathinasamy\\test.xml"); 

O/P

----- 

<resultset xmlns:xsi=http://www.w3.org/2001/XMLSchema- instance >

<row>

<field name = "EmpId">1</field>

<field name = "EmpName">Jack</field>

</row>

<row>

<field name = "EmpId">2</field>

<field name = "EmpName">Joe</field>

</row>

<row>

<field name = "EmpId">3</field>

<field name = "EmpName">Fred</field>

</row>

<row>

<field name = "EmpId">4</field>

<field name = "EmpName">Mike</field>

</row>

</resultset>