T-SQL Tips: Generating Nested XML Structures Efficiently Using FLOWR Expressions

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