Data conversion from Sql server to XML

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
Sam OZAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
Any reason why you can't export the data directly from the SQL server to a (eg. comma delimited) file ?
What kind of the SQL server is that?
MSSQL allows to export data to XML
You need to craft an SQL expression something like the following:
select ID ,
 Name as [@EquipName ], 
Discipline as [Discipline], 
Asset as [Asset], 
category as [category],
CreationDate as [@DocCreationDate ]
from the table 
where category in ('pick list val1','pick list val2','pick list val3')
for XML path('RowElementName')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sam OZAuthor Commented:
Hi Experts,
   I have to mention that the task is not just exporting table to XML.  Based on the table data , with several rules and validations the XML need to be written in a given format . I believe a utility need to be written for that.
AndyAinscowFreelance programmer / ConsultantCommented:
I still think you could probably just do it in SQL.

Any thoughts on a high level design of the utility  for this ? Especially considering the 2 million rows
Just design it for handling one row then let it loop through all the rows.  The 2 million is a red herring (unless you intend to do this eg. hourly from now on).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.