Exporting MYSQL data into xml file

Exporting MYSQL data into xml file Using command line scripts

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>

MSSQL - creating xml output

Creating xml output using MSSQL server

Select * from Person.AddressType for xml path('Version');

Talend Basics

Talend Integration services - Executing methods

1. Creating new project
2. Create new job under the Job designer
3. Every project can have separate folder under the workspace
4. The exported job items can have the .sh, .batch file
5. The .sh file alone will run at Linux environment
6. Batch file run at Windows environment

        Project
             Workspace
                      Job folder
                                  

Talend - date format conversion using tMap component

Date format conversion using tMap component

Relational.ISNULL(row1.PlannedStartDate)? null:((TalendDate.formatDate("yyyy-MM-dd",row1.PlannedStartDate)).matches("(^[a-zA-Z0-9 -/]+$)")?row1.PlannedStartDate:null)

row1.PlannedStartDate - Component vaiable

Talend - String to float conversion using tMap component

String to float conversion in talend using tMap component

Relational.ISNULL(ExceltoMap.PlannedEffort)? null:((ExceltoMap.PlannedEffort).matches("[0-9]*\\.?[0-9]+")?Float.parseFloat(ExceltoMap.PlannedEffort):null)

ExceltoMap.PlannedEffort - Component variable

Not: this expression should allow only integers and floating point numbers