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 Wednesday, September 09, 2015 2:18 AM Not SSIS related

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.

Wow, it did work at first try!!!! I will analyze the query to see how it works. thanks you very much, I really appreciate your effort and help

Sorry, I have a couple of questions. I have been working with your nice query, and I try to add the "header" section at the beginning of the XML, which would be a GETDATE() value, how can I add it?

Also, if a field is NULL, it won't shows in the XML. I searched and find the keyword EXPLICIT, which includes the CDDATA structure. But this keyword means a need to rewrite the query. Is there an easier way?

thanks for your help

Hi Dominic33,

If the tag CDATA has to be remained, please refer to below query, not an elegant one but I'm afraid it might be the only approach.

;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 
REPLACE(REPLACE(REPLACE(REPLACE((
SELECT
GETDATE() header,
( 
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 * FROM(
SELECT
1 Tag,
NULL Parent,
NULL [Items!1!],
NULL [Items!1!item],
NULL [item!2!item_no!ELEMENT],
NULL [item!2!serial_no!ELEMENT],
NULL [item!2!sku!ELEMENT],
NULL [item!2!description!CDATA],
NULL [item!2!barcode!ELEMENT],
NULL [item!2!task_type!ELEMENT],
NULL [item!2!item_location!ELEMENT]
UNION ALL
SELECT
2 Tag,
1 Parent,
NULL [Items!1!],
itmIndex [Items!1!item],
itmNo [item!2!item_no!ELEMENT],
itmSerial [item!2!serial_no!ELEMENT],
itmSku [item!2!sku!ELEMENT],
itmDescription [item!2!description!CDATA],
itmBarcode [item!2!barcode!ELEMENT],
itmTaskType [item!2!task_type!ELEMENT],
itmLocation [item!2!item_location!ELEMENT]
FROM Cte WHERE ordInvoiceNo=c2.ordInvoiceNo AND ordWrhNo=C2.ordWrhNo AND ordZone=c2.ordZone AND ordDistZone=c2.ordDistZone AND c2.ordStoreNo=ordStoreNo
) T
FOR XML EXPLICIT
)
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')
) 
FOR XML PATH('extract')
),'&lt;','<'),'&gt;','>'),'&amp;gt;','>'),'&amp;lt;','<')