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