In memory. To keep the XML small, you need to pass the minimum of informations between the layers. For example, this would be bad if what you need is only 3 fields from the table and only some of the records:

SELECT * FROM MyTable

This would be better:

SELECT Field1, Field2, Field3 FROM MyTable WHERE Field1 > DATETIME ()

>I haven't used XML yet but I've heard that the files are huge. How does this affect an app when let's say we are getting 500 records into the app? Do you create a file or just create the XML in memory?