I have to take 2 million rows from a Sql server table (extracting 20 fields from table ) and create XML in a predefined format .
Sql Server table has sample fields like ( ALL ARE STRING)
ID , Name, Discipline, Asset, category ,CreationDate
XML has to be created in some format which later need to be "loaded" into another SQL Database ( A software will read the XML and create Sql instruction to insert rows into a normalised database)
Category entries need to be validated against a picklist
The XML will have attributes like EquipName ( Which maps to Name in the input databse ) , DocCreationDate ( The date conversion rule has to be applied here)
...... And more fields and rules
Any thoughts on how to handle this ? I am thinking of writing a .net utility for this . I have no working knowledge on ETL. But an open source free ETL is good for this ?
Any thoughts on a high level design of the utility for this ? Especially considering the 2 million rows