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>
Advertisements

4 thoughts on “How to convert an SQL statement into a XML file

  1. Nikhila November 11, 2013 / 4:44 pm

    I tried doing this in Teradata SQL Assistant on one of my database tables and I get the following error SELECT Failed. 3706: Syntax error: expected something between the ‘select’ keyword and ‘[‘.

    Like

  2. Tony Dunsworth January 28, 2015 / 9:12 pm

    Thanks for the tip. I am borrowing this for a sql query which needs to have it’s information converted to XML to send to another vendor. However, how do you get the FROM clause to work if you’re or calling multiple tables?

    Like

      • Tony Dunsworth January 29, 2015 / 1:15 pm

        I noticed that you have everything in quotations after each of the pieces of the SELECT statement, but nothing around the salesperson designation trailing the FROM clause. I’m assuming that it will work the same way with a JOIN as it does without it above.

        Thanks again,

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s