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