Introduction
I have been thinking about publishing an article on this for quite a while now. Of late have been too busy (or may be lazy is a better word!) to write on this. Finally thought of breaking the
.
Previously I had blogged about how FOR XML PATH can be used to generate nested XML structures in the below two articles
There is an alternate method that can be used to generate these nested XML structures using FLOWR expressions. This will be much more efficient than using nested subqueries for large data volumes.
Illustration
Using the same example as in the previous article, we can see how FLOWR expression can be applied to get same result
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [varchar](100) NULL,
[OrderDate] [date] NULL,
[ReferredBy] [varchar](100) NULL,
[AgentGrp] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[OrderID] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (1, N’ABC Corp’, CAST(0xB8350B00 AS Date), N’Agent 1′, N’110′)
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (12353, N’R & K Associates’, CAST(0x7A370B00 AS Date), N’Agent 5′, N’105′)
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (13345, N’Zyng Enterprises’, CAST(0x5D370B00 AS Date), N’Agent 3′, N’110′)
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (15789, N’Maxim Bailey’, CAST(0x7A370B00 AS Date), N’Agent 1′, N’120′)
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (22345, N’Kyzer’, CAST(0xA5370B00 AS Date), N’Agent 2′, N’120′)
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (29398, N’ABC Corp’, CAST(0x54370B00 AS Date), N’Agent 4′, N’105′)
SET IDENTITY_INSERT [dbo].[Orders] OFF
GO
The result will be as below
As seen from result above the FLOWR expression helps us in building the XML in the structure we want.
The first part of the query using FOR XML will built a simple XML structure with all attributes involved. Then we apply query function on top of the simple XML we built and use FLOWR expressions inside to get XML to the format we desire
Another illustration is given below
declare @catalog table
(
catalogid int identity(1001,1),
catalogdescription nvarchar(100),
catalogcreationdate datetime
)
insert @catalog (catalogdescription,catalogcreationdate)
values
(N’mens wear’,getdate()-120),
(N’womens wear’,getdate()-35),
(N’sports wear’,getdate()-90),
(N’accessories’,getdate()-25),
(N’Beauté’,getdate()-20)
declare @products table
(
ProductID int identity(10001,1),
ProductDesc nvarchar(100),
CatalogID int
)
insert @products (ProductDesc,CatalogID)
values (‘Crop Tops’,1002),
(‘Sweat Shirts’,1002),
(‘Bodyfit Jeans’,1001),
(‘Golden Perfurme’,1005),
(‘Jerseys’,1003),
(‘Pendant with Earstuds’,1004),
(‘Anklet’,1004),
(‘Shorts’,1001)
declare @productattributeTypes table
(
AttributeTypeID int identity(10001,1),
AttributeTypeDesc nvarchar(100)
)
insert @productattributeTypes (AttributeTypeDesc)
values (‘Shoe Size’),(‘Belt Size’),(‘Base Material’),(‘Color’),(‘Pattern’),(‘Size’)
declare @productattributevalues table
(
AttributeID int identity(10001,1),
ProductID int ,
AttributeTypeID int,
AttributeValue nvarchar(100)
)
insert @productattributevalues (AttributeTypeID,ProductID,AttributeValue)
values (10003,10001,’Cotton’),
(10003,10002,’Polyester’),
(10006,10002,’XL’),
(10006,10003,’32’),
(10005,10003,’Slim fit’),
(10003,10004,’Cologne’),
(10006,10004,’100 ml’),
(10003,10005,’Polyester’),
(10003,10006,’Black Metal’),
(10003,10007,’White metal with stones’),
(10006,10008,’Cotton’)
Here’s the query
with the result as below
Conclusion
From the two illustrations above, its evident that FLOWR expression based method can be used effectively to generate nested XML structures
Artifacts
The full code for generating the XML structures can be found below