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 4 hours 46 minutes ago Not SSIS related

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