Avatar of REA_ANDREW
REA_ANDREW
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Combing ROW_NUMBER() and FOR XML

Hi,

I am wondering the best way to go about this.  I need to shape data which is returned from SQL Server.  For example:

Return Customer
     Return Customer Orders
          Return Customer Order Items
Return Customer
     Return Customer Orders
          Return Customer Order Items
Return Customer
     Return Customer Orders
          Return Customer Order Items


So eachrecord of a customer also contains there related Orders and subsequently the related order items.

I have opted for the FOR XML AUTO,TYPE  and it is working great for me.  My question is as follows:

How can i combine the ROW_NUMBER() function with this FOR XML AUTO, TYPE,  so in one select statement I will end up with Customer, orders and order items of that customer, but i need to return this data for the customers who are on page 1 with page size of 10.  So 10 customer nodes with the related order and order item collections

TIA

ANDREW
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
REA_ANDREW

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
momi_sabag

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mark Wills

yep, momi_sabag's solution should work. I have one question - what defines "page 1" ?
REA_ANDREW

ASKER
StartingIndex and PageSize are supplied to the procedure.

Cheers

Andrew
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes