How to convert an SQL statement into a XML file

Today I found some functionality which I thought was amazing and will save me loads of time creating some sample XML files to test my webservice with.

I had some sample data in database which I wanted to convert into an XML file.

My original idea was to use excel to concatenate bits of it but this turned out to be to slow and difficult.  I then found out that SQL Server has in built functionality to convert sql statements into XML files.

This article goes through some of the functionality and I would also read this article if you want more detail than my example below

below is my select statement, the important part is at the end
FOR XML AUTO,ELEMENTS
this converts the sql statement into XML elements

SELECT
      [Code]"Code"
      ,[Name]"Name"
      ,[Commission %]"Commission"
      ,[E-Mail]"Email"
      ,[Phone No_]"Phone"
      ,[Job Title]"JobTitle"
  FROM [database].[dbo].[Salesperson_Purchaser]salesperson
  FOR XML AUTO,ELEMENTS

this is the result, amazing.  I can't believe I have never heard of this fantastic 
feature before

<salesperson>
  <Code>MT</Code>
  <Name>Metaphorix</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email>metaphorix@metaphorix.co.uk</Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>
<salesperson>
  <Code>TS</Code>
  <Name>tom scott</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email>tom.email.co.uk</Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>
<salesperson>
  <Code>DR</Code>
  <Name>Jerry time</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email></Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>