Using Ssis to Extract a Xml Representation of Table Data to a File

Using SSIS to extract a XML representation of table data to a file

Sorry to spoil, but there's an SSIS option for you: Export Column Transformation.

I defined an OLE DB query with

SELECT
    *
FROM
(
    SELECT * FROM dbo.spt_values FOR XML AUTO, ROOT('RootVal')
) D (xml_node)
CROSS APPLY
(
    SELECT 'C:\ssisdata\so_xmlExtract.xml'
) F (fileName)

This results in 1 row and 2 columns in the dataflow. I then attached the Export Column Transformation and wired it up with xml_node as Extract Column and fileName as the File Path Column

Mostly truncated results follow

<RootVal>
    <dbo.spt_values name="rpc" number="1" type="A  " status="0"/>
    <dbo.spt_values name="dist" number="8" type="A  " status="0"/>
    <dbo.spt_values name="deferred" number="8192" type="V  " low="0" high="1" status="0"/>
</RootVal>

A more detailed answer, with pictures, is available on this Q&A Export Varbinary(max) column with ssis

What is the largest XML file SSIS can extract data from?

Xml Source does not load the whole document in memory, but streams data out as it reads it from XML file. So if you are reading the XML and writing it to e.g. text files without complex transformations, you need relatively little memory. Also the amount of memory you need (after some threshold) stops growing when XML file grows - so you may handle potentially unlimited XML files.

E.g. this guy exported the whole Wikipedia content (20Gb XML file):
http://www.ideaexcursion.com/2009/01/26/import-wikipedia-articles-into-sql-server-with-ssis/

Of course, you will probably do something with that data, e.g. join multiple streams coming out of the XML Source. Depending on what you need, you might need a lot of memory, because some transforms do keep the whole dataset in memory, or perform much better if you have enough memory for the whole dataset.

How to load an XML file into a database using an SSIS package?

Here is a possible option which demonstrates how to load multiple XML files having same definition into an SQL Server table. The example uses SQL Server 2008 R2 and SSIS 2008 R2. The example shown here loads three XML files into an SQL table using SSIS Data Flow Task with the help of XML Source component.

Step-by-step process:

Create a table named dbo.Items using the script given under SQL Scripts section.
Create an XSD file named Items.xsd in the folder path C:\temp\xsd using the content provided under XSD File section.
Create three XML files namely Items_1.xml, Items_2.xml and Items_3.xml in the folder path C:\temp\xml using the content provided under XML Files section.
On the package, create 3 variables namely FileExtension, FilePath and FolderPath as shown in screenshot #1.
On the package's Connection Managers, create an OLE DB Connection named SQLServer to connect to the SQL Server Instance as shown in screenshot #2.
On the Control Flow tab, place a Foreach loop container and a Data Flow Task within the Foreach loop container as shown in screenshot #3.
Configure the Foreach Loop container as shown in screenshots #4 and #5.
Double-click on the Data Flow Task to navigate to the Data Flow tab. Place an XML Source component and an OLE DB Destination as shown in screenshot #6.
Configure the XML Source as shown in screenshot #7 and #8. The XML file path will be retrieved from the variable FilePath. This variable will be populated by the Foreach Loop container.
Configure the OLE DB Destination as shown in screenshots #9 and #10.
Screenshots #11 and #12 show the package execution.
Screenshot #13 shows the table data before the package execution. Screenshot #14 shows the table data after the package execution. The data in the table dbo.Items now contains the data present in three XML files.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](50) NOT NULL,
    [ItemName] [nvarchar](60) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

XSD File

<xsd:schema xmlns:schema="ItemsXSDSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="ItemsXSDSchema" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Items">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Item">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="Id" type="sqltypes:int" />
                            <xsd:element name="ItemNumber">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="20" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="ItemName">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="60" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="Price">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:numeric">
                                        <xsd:totalDigits value="18" />
                                        <xsd:fractionDigits value="2" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

XML Files

Items_1.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>I2345343</ItemNumber>
        <ItemName>Monitor</ItemName>
        <Price>299.99</Price>
    </Item>
</Items>

Items_2.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>J1231231</ItemNumber>
        <ItemName>Mouse</ItemName>
        <Price>29.99</Price>
    </Item>
</Items>

Items_3.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>K0456212</ItemNumber>
        <ItemName>Keyboard</ItemName>
        <Price>49.99</Price>
    </Item>
</Items>

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

How to export from SQL Server to XML

If you want to use the task components, set up an Execute SQL Task to read the result of a SQL statement or stored procedure into a user-defined SSIS variable. Your statement above is a good example of what it should look like:

Sample Image

Then use the XML Task to write the contents of the variable to a file:

Sample Image

Using Ssis to Extract a Xml Representation of Table Data to a File