Pin Me

How to Get SQL Results into an XML File

written by: S. R. Obbayi•edited by: Amy Carson•updated: 6/15/2011

You have probably tried to create a XML file from SQL and found it is not as standardized as you thought it should be. This does not mean it is a difficult task. Have a look at this guide which brings to light the procedures on how to get it done.

  • slide 1 of 8

    A Brief Description of XML

    Sample XML File Before we start to create a XML file from SQL, it is good to know that XML or Extensible Markup Language in full, is a language related to the popular web page language known as HTML. The major differences here are that HTML is mainly used to display data and the tags that mark-up the HTML content are built into the language. As for XML, user defined tags are supported form the basis for the mark-up. XML is not designed as a display language but instead it is designed as a means to store and transport data in a way that makes it more cross portable between a vast array of platforms and software frameworks.

  • slide 2 of 8

    SQL in a Nutshell

    SQL on the other hand is a language designed to manage data stored in a relational database. SQL is short for Structured Query Language. A powerful language that that can extract, insert, manipulate and delete data in your database. SQL can also be used to create other storage objects such as tables, view and files as well. For the sake of this article, we will be interested in how you can use SQL specifically in SQL Server database system to extract, manipulate and store your data as XML.

  • slide 3 of 8

    SQL Syntax Explained

    To extract data from any table using SQL, you would implement the SQL SELECT query in its most basic form as follows:

    SELECT * FROM table

    Where the star simply means all columns. An alternative and recommended one for that matter is to use the actual column name separated by commas in this format:

    SELECT col1, col2, col3 FROM table

    This is quite okay and will suit the needs of many applications. This will dump the results of this query onto the console to display on the computer's primary output device. In other cases the output will normally be piped through the calling application's Widget or Controls.

  • slide 4 of 8

    Saving Results to File

    You may also want to dump the output into a file rather than display the results on screen. Different database management systems us different syntax. To mention a few:

    While using the command line (SQL*plus) In Oracle, all you need to do is type:

    SPOOL folder/filename.ext

    SELECT col1, col2, col3 FROM table;


    This will save the results in the fame named filename.

    You can achieve the same task using MySQL with the OUTFILE extension. The syntax is as it is in the following query:

    SELECT col1, col2, col3 INTO OUTFILE 'folder/filename.ext'


    You can see there is a lot going on here which is used for formatting the output file.

    In SQL Server this can be done with the following query:

    SELECT col1, col2, col3 FROM table QUERYOUT "c:\filename.txt" -c

    The -c flag here is used to ensure the formating is kept.

    All these queries listed here above otput a CSV file format so the filename does not matter as long as you keep that in mind. The reason why each of these use a different syntax is because standard SQL does not have a native way of sending the output to file and therefore has to be implemented by the vendors.

  • slide 5 of 8

    Displaying SQL Output as XML

    Now that we have done the groundwork, we can look further into into how to create an XML file from SQL. This is not a difficult task. SQL Server has got further extensions as T-SQL which makes this possible. A typical query for creating the XML output would look similar to this:


    The XML extension here tells the parser that the output is to be wrapped in XML tags. The AUTO mode can be options of either AUTO, RAW or EXPLICIT.

    The AUTO mode gives a nested output based on the selected columns and specified tables. The RAW mode extracts and prepares results with a row prefix in the tag name leaving the column name as the attribute. The EXPLICIT mode this is more complicated but the most powerful. It requires the user to specify the names and values of the returned XML.

  • slide 6 of 8

    Saving the XML Output to File

    The easiest way to do this is through a stored procedure by using the BCP utility to run the procedure.



    DECLARE @myXml xml

    SET @myXml = (SELECT * FROM table FOR XML AUTO)

    SET @SQL= 'bcp "exec @myXml" QUERYOUT c:\filename.xml -w -r -t -SServerName -T' EXEC Master..xp_CmdShell @SQL

    Here we make a stored procedure and set out XML output query to the SELECT statement we have been using all along. We then execute that query with the BCP utility and pass the output to the named xml file of choice. We then have optional flags and a final call to execute through the xp_CmdShell which is a command line utility.

  • slide 7 of 8

    The key for how to create a XML file with SQL lies in three parts. Create a stored procedure to bring the required queries into a common name space. Secondly use the QUERYOUT operator to dump the results into a file and finally us BCP utility to run the query.

  • slide 8 of 8



    Images provided by author