MS SQL XML - XML output in SSIS

Author

XML output in SSIS

Ralf

2005-12-05, 1:24 pm

Is it possible to have a Data Destination in SSIS that outputs an XML file? I
can only find information about XML as a Data Source. But I need to output
data into XML.

Kent Tegels

2005-12-06, 1:23 pm

Hello Ralf,

> Is it possible to have a Data Destination in SSIS that outputs an XML
> file? I can only find information about XML as a Data Source. But I
> need to output data into XML.

The simple answer seems to be no since there is XML data destination. Sometimes
it helps to remember that it really should have been called SQL Server INTOgration
service :)

The long answer is that its pretty simple to have a bit of code that takes
a flat file output to XML as long as you're not dealing with much in terms
of hierarchy.

As an alternative, you could write a query that gives you the XML you want,
pass that as parameter to a SQLCLR stored proc and a have it write it to
a disk file.

Or use BCP with the -X option.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Sponsored Links