SSIS - Generate XML Formatted File

Refer Source Video :Generate XML Formatted File From SQL Server - SSIS 

Option1:->

Execute SQL  Task:->

sql statement:

select... table joins

FOR XML AUTO, ROOT('Organization')

Resultset:xml

ResultSet:

Result Name        Variable Name

0                            User::var_xml_out

Script Task:->

Script:

ReadOnlyVariable: User::var_xml_out

note:

1.FOR XML  is the T-Sql which generates in xml format.

2.var_xml_out  - string type variable.

Option2:-> Data Flow Task

Data Access Mode:Sql Command

OLE DB Source:

Select(

select... table joins

FOR XML AUTO, ROOT('Organization'))) AS data

note:the above statement will return data as string unlike above the xml in option 1.

Data Conversion Transformation:-> Converting from unicode to nonunicode.

Create a flat file Connection:->

Advanced:

Column:XMLData

Datatype:string(DT_STR)

OutputColumnWidth:500

Option3:->

Select(

select... table joins

FOR XML AUTO, ROOT('Organization'))) AS data

 , C:\data\out\text.xml AS Path

Export Column: transformation  takes the data copies to the mentioned Path.