Based on the above, I may have been incorrect in my original guess, so bear with me for a minute while I try to understand your requirements.

1. You have a table in a database with the information you need in it.

2. You need to select and format that data.

3. You need to "export" that formatted data.

4. Someone else needs to "import" that data into something else.

I have some questions concerning those requirements so that we can get'er done right the first time... 🙂

1. Let's start at the end goal, which is Item #4 above.

1.a. What are they importing the data to? For example, is it a spreadsheet that they're going to import the data to?

1.b. If it's not being imported into a spreadsheet, then they just need a text file?

1.c. In either case, do they [font="Arial Black"]really [/font]need the XML formatted data (as in your original example) or will something simpler like a TSV file (Tab Separated Values) work?

2. Going back to Item #3 and regardless of the format of the data we end up with, you want SQL Server to "export" the formatted data. There are a couple of "keys" to this part of the problem that will dictate how it needs to be done.

2.a. The output needs to be exported to a physical file, correct? Or will the output be consumed directly by whatever is calling the proc?

2.b. What will be the "thing" that triggers the stored procedure to executed?

2.c. If the output must be directed to a physical file, can xp_CmdShell be used (we CAN do that safely) and, if not, can SQLCmd be used?

2.d. If the output must be a physical file, will the location of that file be on the SQL Server itself (not recommended) or on some other machine? If on some other machine, will it always be the same machine and folder (recommended) or will the target machine and/or folder vary (not recommended)?