Tooltip of the month: Simple XML export with SQLserver 2008

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:

<Root Organisations><Organisations>
<Org_Id>001</Org_Id>
<Org_Name>Thebuttonfactory</Org_Name>
</Organisations></Root Organisations>

To do the export on a regular basis, we schedule it. In SQL you can run simple jobs under the server Agent.

We can insert multiple steps, in the step list, all containing a simple sql query in XML style we just wrote and add it to the step list.

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.