Learn to Create: XML File from SQL, Step by Step

A Brief Description of XML

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.

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.

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.

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;

SPOOL OFF

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’

FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ FROM table;

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.

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:

SELECT * FROM table FOR XML AUTO

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.

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.

CREATE PROC makeXML

AS

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.

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.

References

Sources

• Microsoft Developer Network
• Author’s own eperience

Images provided by author