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

I'm trying to use SSIS to extract XML representation of a query result set to a text file. My query is currently successfully extracting the exact XML output I need when I run it in SSMS. I've tried every trick I can find to use this result set in a SSIS package to create a file.

Using a dataflow to port a OLE Source to a Flat file doesn't work because the output of a XML query is treated as TEXT and SSIS can't push TEXT, NTEXT or IMAGE to a file destination.

I've tried to then Execute SQL Task to fill a user variable and then use a Script Task (written using C#) to write the contents of this user variable to a file output, but the user variable is always empty. I don't know, but I suspect this is, again, because the XML is treated as TEXT or IMAGE and the user variable doesn't handle this.

The query is in this form: SELECT * FROM dataTable WHERE dataTable.FIELD = 'Value' FOR XML AUTO, ROOT('RootVal')

The resulting dataset is well formed XML, but I can't figure out how to get it from result set to file.

It's a relatively easy task for me to write a console app to do this in C# 4.0, but restrictions require me to at least prove it CAN'T be done with SSIS before I write the console app and a scheduler.

Solution 1:

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

Solution 2:

BillInKC's answer is the best I've ever seen, but SQL can be simplified (no need for cross apply):

SELECT X.*, 'output.xml' AS filename
FROM (SELECT * FROM #t FOR XML PATH('item'), ROOT('itemList')) AS X (xml_node)

It will output the same structure:

xml_node                                           filename
-------------------------------------------------- ----------
<itemList><item><num>1000</num></item></itemlist>  output.xml

(1 row(s) affected)