xml - Generate XML Formatted File From SQL Server - SSIS

Generate XML Formatted File From SQL Server - SSIS

~

SELECT (

SELECT Employee.*,Dept.*  FROM  XML_EMP  Employee INNER JOIN XML_DEPT  Dept

ON Employee.DeptID=Dept.DeptID

FOR XML AUTO, ROOT('Organization') AS Data,'B:\SQLFolder\XML\Output\Test3.xml' AS Path

Execute SQL Task:

Resultset:XML

Variable:

Result:

~

TRUNCATE  TABLE  LOAD_XML_EMP  

GO

DELETE FROM LOAD_XML_DEPT

GO

note:

i. It can not use truncate as it's PK is referneced as a FK in a table.

ii. So separate XSD task is used for data flow. As  FK reference is used.

iii. If there would not have any FK reference - one DFD task would have been fine.

~

.xsd - open in VS 2008

drag the .xsd to VSS.

~

Download   XSD.EXE  from IE

C:\> xsd.exe test2.xml

1.

This will generate .xsd open in VSS: the diagram  edit the diagram.

Employee                                       Dept

Eid                                                  DeptId

Ename                                             DeptDesc

Salary

DeptId

Employee_Id             -edit                      Employee_Id (delete)

(delete -click edit)

Link table EmployeeId  - EmployeeId

(correct it.)

Relation

Employee                      Dept

Key column                  FK 

DeptId                          DeptId

2.

Add PK Constraint

Employee    Dept

Dept_Id       Dept_Id

2.Add  PK  constraint

Employee                Dept

Eid<-create pk        DeptId

Ename                     Deptname

Ename

Salary

DeptId

~

ScriptMain.cs

public void Main( )

{

    //TODO: Add your code here

string  xml_content = dts.variables("User::Var_xml_out").value.ToString( );

StreamWriter file= new  StreamWriter("B:\\SQLFolder\\XML\output\\test.xml");

file.WriteLine(xml_content);

file.close();

dts.TaskResult=(int)ScriptResults.Success;

}

ReadonlyVariable:User::var_xml_out

variable  xml 

~

SQL1:Output:xml

SELECT Employee.*,Dept.*  FROM XML_EMP_Employee INNER JOIN XML_DEPT Dept ON 

EMPLOYEE.DEPTID=DEPT.DEPTID FOR XML  AUTO,ROOT('Organization')

GO

~

SQL2:Output:string

SELECT (

SELECT Employee.*, Dept.*  FROM  XML_EMP_Employee INNER JOIN XML_dept dept on
employee.DeptId=DEPT.DEPTID
FOR XML AUTO,ROOT('Organization')) AS Data

output:
Data  Path

~
SQL1:Output:xml

SELECT (
SELECT Employee.*,Dept.*  FROM XML_EMP Employee INNER JOIN  XML_Dept dept
ON  Employee.DeptId=Dept.DeptId  FOR  XML AUTO,ROOT('Organization'))
AS Data,'B;\SQLFolder\XML\Output\Test3.xml' AS Path

~
---------------------------------------------------------------------------------------------------------------------

Method1:

---------------------------------------------------------------------------------------------------------------------

Method2: ODBC Source :SQL 2

SQL Command

OLE DB Source

Data Conversion

FlatFile Destination

~

Flat file Connection

Filename: B:\SQLFolder\XML\Output
( create a new file format test2.xml)

Columns:
New
Columnno:XMLData
outputcolumnwidth:500
~

---------------------------------------------------------------------------------------------------------------------

Method3:

OLEDB  Source

Data Conversion

Export Column

~

Export column  File pathcolumn

CopyofData       Path

All  App  ForceTruncate

                     --Truncate every type