January 20, 2012
Recently I get a lot of questions how to built a simple XML file data export . Well, we can do this one standing, this is as simple as can be:
We write some pretty simple SQL Query, for example:
SELECT [Org_Id] ,[Org_Name] FROM [Mydb].[dbo].[Organisations]
add a XML line to the query:
SELECT [Org_Id] ,[Org_Name] FROM [Mydb].[dbo].[Organisations] FOR XML PATH('Organisations'), ROOT('Root Organisations')
now we run the query and see the results in xml layout:
To do the export on a regular basis, we schedule it. In SQL you can run simple jobs under the server Agent.
we go to the next step, schedules and schedule the task on a daily basis and save it. Now if we run the job we will receive 4 xml exports from sql ready to be used. Yes, the basics are that simple.