Link to home
Create AccountLog in
Avatar of REA_ANDREW
REA_ANDREWFlag 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
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mark Wills
yep, momi_sabag's solution should work. I have one question - what defines "page 1" ?
Avatar of REA_ANDREW

ASKER

StartingIndex and PageSize are supplied to the procedure.

Cheers

Andrew