Export SQL Server data into an XML file with SSIS

Export data from a SQL Server table into an XML document with an SSIS package.

To export a SQL Server table to an XML file type with an SSIS package and without any additional components, you need to transform the SQL data to XML using a query. SSIS does not provide a native XML component, only an XML source exists. Other software editors offer components for exporting to XML, such as the SSIS productivity pack from KingswaySoft, for example.

1. Create the SQL Server table with data to export in XML

The first step of course is to create a sample source table in SSMS to have some data. Use the following code to create the customer table and insert 8 rows to export in XML format.

-- Créer la table des clients
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

-- Insérer 8 lignes à exporter au format XML
INSERT INTO [dbo].[Customers] VALUES (1, N'Ali', N'Ahmed', N'Cairo', N'Egypt');
INSERT INTO [dbo].[Customers] VALUES (2, N'Johnny', N'John', N'Toronto', N'Canada');
INSERT INTO [dbo].[Customers] VALUES (3, N'John', N'Doe', N'Mexico City', N'Mexico');
INSERT INTO [dbo].[Customers] VALUES (4, N'Shu', N'Abbas', N'Paris', N'France');
INSERT INTO [dbo].[Customers] VALUES (5, N'Jeane', N'Raffin', N'Liushutun', N'China');
INSERT INTO [dbo].[Customers] VALUES (6, N'Legra', N'Leate', N'Błaszki', N'Poland');
INSERT INTO [dbo].[Customers] VALUES (7, N'Sullivan', N'Goadby', N'Xiaoguwei', N'China');
INSERT INTO [dbo].[Customers] VALUES (8, N'Tom', N'Ellams', N'Lop Buri', N'Thailand');

2. Build the query to transform SQL data into XML

After creating the table and inserting customer data, build the SQL Server query to transform SQL data into XML.

-- SQL Select query to check the Customers data
SELECT
   [CustomerID],
   [FirstName],
   [LastName],
   [City],
   [Country]
FROM [dbo].[Customers];

Now, here is the query with the XML functions to create the XML elements Customers and Customer.

-- XML Query to transform the data into XML format and allow the export
SELECT 
   [CustomerID],
   [FirstName],
   [LastName],
   [City],
   [Country]   
FROM [dbo].[Customers]
FOR XML PATH('Customer'),
      ROOT('Customers'),
         ELEMENTS XSINIL;

Click on the link in the result to display the XML file in a new SSMS window.

The data returned by the XML query is of IMAGE type, meaning it is a file. To transform the SQL Server IMAGE type into text, include the query as a subquery. The column is also renamed to XML_Column to facilitate mapping from the SSIS data flow. Use a subquery to encapsulate the previous one and display the result as text in a SQL column.

SELECT (
   SELECT 
       [CustomerID],
       [FirstName],
       [LastName],
       [City],
       [Country]   
   FROM [dbo].[Customers]
   FOR XML PATH('Customer'),
         ROOT('Customers'),
            ELEMENTS XSINIL
) AS XML_Column;

3. Configure the SSIS flow from the SQL source table to the XML file

Add the SSIS OLE DB Source and the Flat File Destination components in the data flow.

Open the OLE DB Source component and use a connection to the source database. Enter the query from the previous step using a simple copy / paste in the SSIS OLE DB Source Editor.

If you change the query, check that the data type is DT_NTEXT, i.e. in Unicode Text format, to avoid errors when mapping to the file. To do so, right-click and choose Show Advanced Editor.

4. Configure the XML file to be exported from SSIS

From the file component, open the Flat File Destination Editor Window and create a new flat file connection. Choose the Delimited type.

To set up the file, follow these steps:

1. Rename the XML file: XML File Export
2. Choose the path on the disk, in our case it is: C:\data\Customers_Data_Export.xml
3. Select Unicode to ensure the mapping with the source column.

By default, Integration Services uses the DT_WSTR type with a length of 255 characters. Now set the XML column to adapt it to the column we export.

1. From the Advanced tab
2. Rename the column with the same name as the source: XML_Column
3. Change the data type to Unicode text: Unicode text stream [DT_NTEXT]

Also check the mapping of the column from the Mappings tab.

5. Run the package to export the table to XML and check the content

Finally, run the package to actually export the data from the SQL Server source table into the XML document, i.e. a file with the XML format. Only one row is transferred because the column contains the entire file in XML format and not the classic rows in SQL format.

Then check the exported file, i.e. from C:\data\Customers_Data_Export.xml.

Open the file with Google Chrome or another open source web browser like Mozilla Firefox for example. And finally check the data and the structure of the file.

Conclusion on exporting data into an XML document with SSIS

To conclude, the process to export SQL Server data into an XML file with SSIS is not completely guided, as SSIS does not provide a native component for XML export. However, with T-SQL code and available components, it is possible to adapt the query to the data structure and configure the components with the appropriate data type.

Useful resources on data export with SSIS or Python

• Export SQL Server data into a text file with SSIS
• SSIS package to export a SQL table to an Excel file
• Use Python to export SQL Server data into a CSV file