How to create XML from SSIS (Network Steve Forum)

How to create XML from SSIS

Hi,

I need to create a XML file and I have been scratching my head for hours now trying to write the query that will let me create the file with the proper layout. An example of the resulting XML would be:

<?xml version="1.0" encoding="ISO-8859-1"?>
<extract xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="ClientFile.xsd">
	<header>2015-06-03 14:28:15</header>
	<Retailer>
		<RetailerName>BRICK</RetailerName>
		<Order>
			<order_info>
				<invoice_number>04045JE0012001</invoice_number>
				<warehouse_no>NAN2N</warehouse_no>
				<zone>MTL</zone>
				<distribution_zone>Quebec</distribution_zone>
				<store_no>027</store_no>
			</order_info>
			<items>
				<item index ="1" >
					<item_no>HAZELQ-S</item_no>
					<serial_no>ABCD1234</serial_no>
					<sku>HAZELQ-S</sku>
					<description><![CDATA[HAZEL QUARTZ SOFA]]></description>
					<barcode>BAR123456</barcode>
					<task_type>D</task_type>
					<item_location>4</item_location>
				</item>
			</items>
		</Order>
	</Retailer>
</extract>

Database structures would be like the following:

CREATE TABLE [dbo].[ST_Orders](
	[ordID] [int] IDENTITY(1,1) NOT NULL,
	[RetailerName] [nvarchar](50) NULL,
	[recConfirmNo] [nvarchar](20) NULL,
	[recMaxReceiveDate] [smalldatetime] NULL,
	[recMaxReceiveTime] [nvarchar](10) NULL,
	[recLeadDay] [int] NULL,
	[ordInvoiceNo] [nvarchar](50) NULL,
	[ordWrhNo] [nvarchar](30) NULL,
	[ordZone] [nvarchar](20) NULL,
	[ordDistZone] [nvarchar](20) NULL,
	[ordStoreNo] [nvarchar](10) NULL,
	[ordCliFirstname] [nvarchar](50) NULL,
	[ordCliLastname] [nvarchar](50) NULL,
	[rteStopDate] [smalldatetime] NULL,
	[rteName] [nvarchar](20) NULL,
	[rteRoadOrder] [int] NULL,
	[rteTruckNo] [nvarchar](30) NULL,
	[ordDateTime] [smalldatetime] NULL CONSTRAINT [DF_ST_Orders_ordDateTime]  DEFAULT (getdate()),
 CONSTRAINT [PK_ST_Orders] PRIMARY KEY CLUSTERED 
(
	[ordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ST_Produits](
	[itmSerial] [nvarchar](50) NOT NULL,
	[ordInvoiceNo] [nvarchar](50) NOT NULL,
	[itmIndex] [int] NULL,
	[itmNo] [nvarchar](30) NULL,
	[itmSku] [nvarchar](50) NULL,
	[itmDescription] [nvarchar](80) NULL,
	[itmBoxQty] [int] NULL,
	[itmWeight] [decimal](10, 2) NULL,
	[itmVolume] [decimal](10, 2) NULL,
	[itmValue] [decimal](10, 2) NULL,
	[itmBarcode] [nvarchar](30) NULL,
	[itmTaskType] [nvarchar](2) NULL,
	[itmLocation] [tinyint] NULL
CONSTRAINT [PK_ST_Produits] PRIMARY KEY CLUSTERED 
(
	[itmSerial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT [dbo].[ST_Orders] ([ordID], [RetailerName], [recConfirmNo], [recMaxReceiveDate], [recMaxReceiveTime], [recLeadDay], [ordInvoiceNo], [ordWrhNo], [ordZone], [ordDistZone], [ordStoreNo], [ordCliFirstname], [ordCliLastname], [rteStopDate], [rteName], [rteRoadOrder], [rteTruckNo], [ordDateTime]) VALUES (1, N'BRICK', N'1248765545', CAST(N'2015-06-21 00:00:00' AS SmallDateTime), N'14:00', 2, N'04045JE0012001', N'NAN2N', N'MTL', N'Quebec', N'027', N'Bruce', N'Wayne', CAST(N'2015-06-25 00:00:00' AS SmallDateTime), N'TP1_001', 8, N'4568', CAST(N'2015-09-03 09:13:00' AS SmallDateTime))
GO
INSERT [dbo].[ST_Orders] ([ordID], [RetailerName], [recConfirmNo], [recMaxReceiveDate], [recMaxReceiveTime], [recLeadDay], [ordInvoiceNo], [ordWrhNo], [ordZone], [ordDistZone], [ordStoreNo], [ordCliFirstname], [ordCliLastname], [rteStopDate], [rteName], [rteRoadOrder], [rteTruckNo], [ordDateTime]) VALUES (2, N'BRICK', N'124567345', CAST(N'2015-06-21 00:00:00' AS SmallDateTime), N'14:00', 2, N'04045JH0987021', N'NAN3N', N'MTL', N'Quebec', N'458', N'Bruce', N'Banner', CAST(N'2015-06-26 00:00:00' AS SmallDateTime), N'TP1_004', 3, N'4567', CAST(N'2015-09-03 09:13:00' AS SmallDateTime))
GO
INSERT [dbo].[ST_Orders] ([ordID], [RetailerName], [recConfirmNo], [recMaxReceiveDate], [recMaxReceiveTime], [recLeadDay], [ordInvoiceNo], [ordWrhNo], [ordZone], [ordDistZone], [ordStoreNo], [ordCliFirstname], [ordCliLastname], [rteStopDate], [rteName], [rteRoadOrder], [rteTruckNo], [ordDateTime]) VALUES (3, N'Mabe inc.', N'7689944777', CAST(N'2015-06-24 00:00:00' AS SmallDateTime), N'13:00', 2, N'1012374655', N'NAN2N', N'MTL', N'Quebec', N'54A', N'Mike', N'Wiggle', CAST(N'2015-06-25 00:00:00' AS SmallDateTime), N'TP1_001', 2, N'4568', CAST(N'2015-09-03 09:13:00' AS SmallDateTime))
GO
INSERT [dbo].[ST_Orders] ([ordID], [RetailerName], [recConfirmNo], [recMaxReceiveDate], [recMaxReceiveTime], [recLeadDay], [ordInvoiceNo], [ordWrhNo], [ordZone], [ordDistZone], [ordStoreNo], [ordCliFirstname], [ordCliLastname], [rteStopDate], [rteName], [rteRoadOrder], [rteTruckNo], [ordDateTime]) VALUES (4, N'Mabe inc.', N'7689944777', CAST(N'2015-06-24 00:00:00' AS SmallDateTime), N'13:00', 2, N'1012374655', N'NAN2N', N'MTL', N'Quebec', N'22B', N'Dave', N'Tanguy', CAST(N'2015-06-26 00:00:00' AS SmallDateTime), N'TP1_007', 12, N'4576', CAST(N'2015-09-03 09:13:00' AS SmallDateTime))
GO
SET IDENTITY_INSERT [dbo].[ST_Orders] OFF
GO
INSERT [dbo].[ST_Produits] ([itmSerial], [ordInvoiceNo], [itmIndex], [itmNo], [itmSku], [itmDescription], [itmBoxQty], [itmWeight], [itmVolume], [itmValue], [itmBarcode], [itmTaskType], [itmLocation]) VALUES (N'ABCD1234', N'04045JE0012001', 1, N'HAZELQ-S', N'HAZELQ-S', N'HAZEL QUARTZ SOFA', 1, CAST(131.00 AS Decimal(10, 2)), CAST(80.29 AS Decimal(10, 2)), CAST(599.00 AS Decimal(10, 2)), N'BAR123456', N'D', 4)
GO
INSERT [dbo].[ST_Produits] ([itmSerial], [ordInvoiceNo], [itmIndex], [itmNo], [itmSku], [itmDescription], [itmBoxQty], [itmWeight], [itmVolume], [itmValue], [itmBarcode], [itmTaskType], [itmLocation]) VALUES (N'ABCD1235', N'04045JE0012001', 2, N'HAZELQ-S', N'HAZELQ-S', N'HAZEL QUARTZ SOFA', 1, CAST(131.00 AS Decimal(10, 2)), CAST(80.29 AS Decimal(10, 2)), CAST(599.00 AS Decimal(10, 2)), N'BAR123457', N'D', 5)
GO
INSERT [dbo].[ST_Produits] ([itmSerial], [ordInvoiceNo], [itmIndex], [itmNo], [itmSku], [itmDescription], [itmBoxQty], [itmWeight], [itmVolume], [itmValue], [itmBarcode], [itmTaskType], [itmLocation]) VALUES (N'GL123451232', N'04045JH0987021', 1, N'10294116', N'10294116', N'SAUMSUNG WHITE WASHER', 1, CAST(210.00 AS Decimal(10, 2)), CAST(80.29 AS Decimal(10, 2)), CAST(599.00 AS Decimal(10, 2)), N'BAR123458', N'D', 5)
GO
INSERT [dbo].[ST_Produits] ([itmSerial], [ordInvoiceNo], [itmIndex], [itmNo], [itmSku], [itmDescription], [itmBoxQty], [itmWeight], [itmVolume], [itmValue], [itmBarcode], [itmTaskType], [itmLocation]) VALUES (N'GL462726636', N'04045JH0987021', 2, N'10294117', N'10294117', N'SAMSUNG WHITE DRYER', 1, CAST(131.00 AS Decimal(10, 2)), CAST(80.29 AS Decimal(10, 2)), CAST(599.00 AS Decimal(10, 2)), N'BAR123459', N'D', 5)
GO
INSERT [dbo].[ST_Produits] ([itmSerial], [ordInvoiceNo], [itmIndex], [itmNo], [itmSku], [itmDescription], [itmBoxQty], [itmWeight], [itmVolume], [itmValue], [itmBarcode], [itmTaskType], [itmLocation]) VALUES (N'GTY575757', N'1012374655', 1, N'AB LTD 1312511', N'AB LTD 1312511', N'CXW200D4BISS RANGE HOOD', 1, CAST(175.00 AS Decimal(10, 2)), CAST(15.29 AS Decimal(10, 2)), CAST(299.00 AS Decimal(10, 2)), N'GFT67838484', N'D', 5)
GO
INSERT [dbo].[ST_Produits] ([itmSerial], [ordInvoiceNo], [itmIndex], [itmNo], [itmSku], [itmDescription], [itmBoxQty], [itmWeight], [itmVolume], [itmValue], [itmBarcode], [itmTaskType], [itmLocation]) VALUES (N'XDS35467475', N'04045JH0987021', 3, N'964199', N'964199', N'SHARP X LC-50LB261U 50'' LED 1080', 1, CAST(131.00 AS Decimal(10, 2)), CAST(80.29 AS Decimal(10, 2)), CAST(599.00 AS Decimal(10, 2)), N'BAR123460', N'D', 5)
GO

How can I generate the XML with this structure?

thanks a lot for your time and help

Moved by ArthurZMVP 5 hours 24 minutes ago Not SSIS related

September 8th, 2015 3:48pm

Hi Dominic,

Try these links

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/create-xml-files-out-of-sql-server-with-

http://businessincredulous.blogspot.in/2012/05/ssis-xml-output.html

http://www.rad.pasfu.com/index.php?/archives/25-SSIS-Sql-Server-to-XML-Save-to-file.html

http://blogs.msdn.com/b/jenss/archive/2009/11/06/an-secret-ssis-xml-destination-provider-you-might-not-found-yet.aspx

http://microsoft-ssis.blogspot.in/2010/12/flexible-xml-destination.html

To append date to filename

http://www.vbforums.com/showthread.php?592543-RESOLVED-Adding-timestamp-to-a-file-name

http://www.visualbasicscript.com/Add-Date-To-End-of-Text-File-Name-m29679.aspx

Free Windows Admin Tool Kit Click here and download it now

September 8th, 2015 10:26pm

Hi Dominic33,

To get the expected output, you can refer to below query.

;WITH Cte AS
(
SELECT 
so.RetailerName,
so.ordInvoiceNo,
so.ordWrhNo,
so.ordZone,
so.ordDistZone,
so.ordStoreNo,
sp.itmIndex,
sp.itmNo,
sp.itmSerial,
sp.itmSku,
sp.itmDescription,
sp.itmBarcode,
sp.itmTaskType,
sp.itmLocation 
FROM [ST_Orders] so JOIN [ST_Produits] sp ON so.ordInvoiceNo=sp.ordInvoiceNo 
) --select * from cte
SELECT 
RetailerName,
(
SELECT
(SELECT 
ordInvoiceNo invoice_number,
ordWrhNo warehouse_no,
ordZone zone,
ordDistZone distribution_zone,
ordStoreNo store_no 
FOR XML PATH('order_info'),TYPE
),
(
SELECT
itmIndex "@Index",
itmNo item_no,
itmSerial serial_no,
itmSku sku,
itmDescription description,
itmBarcode barcode,
itmTaskType task_type,
itmLocation item_location
FROM Cte WHERE ordInvoiceNo=c2.ordInvoiceNo AND ordWrhNo=C2.ordWrhNo AND ordZone=c2.ordZone AND ordDistZone=c2.ordDistZone AND c2.ordStoreNo=ordStoreNo
FOR XML PATH('item'),ROOT('Items'),TYPE
)
FROM Cte c2 WHERE c1.RetailerName=c2.RetailerName
GROUP BY c2.ordInvoiceNo,c2.ordWrhNo,c2.ordZone,c2.ordDistZone,c2.ordStoreNo
FOR XML PATH('Order'),TYPE)
FROM CTE C1
GROUP BY RetailerName
FOR XML PATH('Retailer'),ROOT('extract')

If you have any question, feel free to let me know.

September 9th, 2015 3:17am

This topic is archived. No further replies will be accepted.