SQL to XML | How to Export Data from SQL Server to XML

XML, Extensible Markup Language, is a markup language which defines a set of rules for encoding documents in a format that can be read by both human and machine. XML stores data in plain text format, which indicates the simplicity, usability, and generality of this format. Given that, many users want to save the SQL query result to XML or export the SQL database to the XML file.

Export SQL Query Result to XML

With the FOR XML clause, you can save the result of a SQL query as XML. It can be used both in top-level queries (SELECT statement) and in subqueries (INSERT, UPDATE, and DELETE statements).

When you use a FOR XML clause, you need to specify one of the four modes: RAW, AUTO, EXPLICIT, and PATH. 

• RAW: The RAW mode is the simplest one among the four. It only creates a single row element per row in the rowset that is output by the SELECT statement.
• AUTO: According to the way the SELECT statement is specified, the AUTO mode creates nesting in the resulting XML.
• Explicit: The EXPLICIT mode enables you to mix the attributes and elements, create wrappers, and more as you like. With this mode, you have more control over the shape of the XML.
• PATH: You can enjoy the flexibility of the EXPLICIT mode in an easier way with the PATH mode and the nested FOR XML query capability.

The example of exporting a SQL Server query as XML:

USE [Database Name]
GO
SELECT [the information you want to export]
INNER JOIN [Table Name] [information]
On [limits]
For XML AUTO;

Export SQL Database to XML

If you want to export the whole SQL Server database to the XML file, you can first use the SQL Server Management Studio to export your SQL database to CSV, and then convert CSV to XML using an online converter. You ca also choose to output SQL Server database to Excel and then convert Excel (XLS) to XML.

To Export SQL database to XML:

Procedure 1. Export the SQL Database to XLS (Excel), CSV or HTML

Step 1. Open SQL Server Management Studio and connect to the database. 

Step 2. Go to "Object Explorer", find the server database you want to export in CSV. Right-click on it and choose "Tasks" > "Export Data" to export table data in CSV. Then, the SQL Server Import and Export Wizard welcome window pop up.

Step 3. Now, you need to click on the Data source drop-down button to choose the data source you want to copy. Here, select "SQL Server Native Client 11.0". In the Server name drop-down box, select a SQL Server instance. In the Authentication section, choose authentication for the data source connection and from the Database drop-down box, select a database from which data will be copied. After everything is set, press the "Next" button. 

Step 4. On the "Choose a Destination" window, in the Destination box, choose "Flat File Destination" for copying data from SQL Server to CSV. In the File name box, specify a CSV file where the data from a SQL Server database will be exported and click the "Next" button.

Step 5. On the Specify Table Copy or Query screen, you can choose to export the SQL database into CSV format either by "Copy data from one or more tables or views" or "Write a query to specify the data to transfer". Then, click Next to go on.

Step 6. Now, in the "Configure Flat File Destination" screen, you can select the table for exporting from "Source table or view" option. And you can also change the default settings of Row & Column delimiter. You can click the "Preview" button to view which data will be exported to the CSV file. Click Next to go on.

Step 7. Here, on the 'Save and Run Package" window, you don't need to change any settings. Click the "Next" button to move on.

Step 8. On the "Complete" Wizard window, you can check all the settings set during of exporting process. If everything is right, click "Finish" to start exporting the SQL database to CSV.

Step 9. You can view the exporting process in this step. When it completes, click on the "Report" drop-down button.

Step 10. You can choose "Save Report to File..." (including Excel, Notepad) to save the report as you need. This report consists of details of the complete SQL database to CSV conversion.

Procedure 2. Convert Excel/CSV/HTML to XML

When it comes to converting CSV, XLS or HTML to XML, there are many online converters available. Find the one you prefer and apply it to convert the database you exported to an XML file.

The Bottom Line

Depending on your needs, you can choose to use the FOR XML clause to return a query result to XML or export a whole SQL database to XML file. When you use the FOR XML clause in a query, you need to make sure which mode you need to specify. While if you export the SQL database to XML, you need to save the database as a supportable format and then convert the current format to XML. Hope this article is helpful to you.