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

xml - Loading XML Documents

SQL Server 2012 - Loading XML Documents

~

<Subcategories>

  <Subcategory ProductSubCategoryID="18"  Name="Bib-Shorts">

  <Products>

    <Product>

         <ProductID>855 </ProductID>

         <Name> T-Shirt </Name>

         <ProductNumber>1234</ProductNumber>

         <ListPrice>89.99</ListPrice>

         <ModifiedDate>    </ModifiedDate>

     </Product>

      <Product>

     </Product>

 </Products>

~

example1.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  * FROM OPENXML(@hdoc, ' /Subcategories/Subcategory',1)
WITH ( ProductSubCategoryID,Name)


EXEC  sp_xml_removedocument @hdoc

example2.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  *  INTO  MyProducts  FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH
(
'../../@ProductSubcategoryID',
'../../@Name'
ProductID int,
ProductName varchar(100),
ListPrice float,
ModifiedDate datetime
)


EXEC  sp_xml_removedocument @hdoc

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

step wise:

1.

SELECT
FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

note:
i. it creates a table named "Products"  and a column named "P"  and the  'c:\Examples\Products.xml' is stored as binary.

2.

DECLARE @x xml

3.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

Now xml in sql server. It can be read using below sql.

SELECT  * FROM OPENXML(@hdoc, ' ',1) WITH ( )

Treats the xml as the table.

@hdoc - handle to the xml doc.

'  '  - xpath ( xml hierarchy u want to select data from ).

1,2  - flag

1 - attribute
2 - fields are elements

4.

<Subcategories>

  <Subcategory ProductSubCategoryID="18"  Name="Bib-Shorts">

5.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  * FROM OPENXML(@hdoc, ' /Subcategories/Subcategory',1)
WITH ( ProductSubCategoryID,Name)

EXEC  sp_xml_removedocument @hdoc

6.

a.No rows will be returned.

SELECT * FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product',1)

note: 1 , look for attribute not element.

WITH (

ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float,
Modifieddate datetime

)

b.Rows will be returned.

SELECT  *  FROM  OPENXML ( @hdoc, 'Subcategories/Subcategory/Products/Product',2)
WITH (
ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float,
ModifiedDate datetime
)

7.

a.

SELECT  *  INTO  MySubcategories FROM OPENXML(@hdoc, '/Subcategories/Subcategory',1)
WITH(
ProductCategoryID int,
Name varchar(100)
)

b.

SELECT  *  INTO  MyProduct FROM OPENXML(@hdoc, '/Subcategories/Subcategory/Products/Product',2)
WITH(
ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float
)

8.

SELECT  *  INTO  MyProducts  FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH
(
'../../@ProductSubcategoryID',
'../../@Name'
ProductID int,
ProductName varchar(100),
ListPrice float,
ModifiedDate datetime
)

note:

flag : 2 - elements , so to include attribute  each level is defined as ../

Name,ProductName is different otherwise duplicate error would have come.

xml - Load XML Files into SQL Server Tables - SSIS

Load XML Files into SQL Server Tables - SSIS