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

https://www.dataplatformcentral.com//2014/05/05/t-sql-tips-fun-with-for-xml-path/

https://www.dataplatformcentral.com//2013/12/17/generating-nested-xml-structures-with-for-xml-path/

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]

SET ANSI_PADDING OFF

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

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