Allow me to illustrate

USE MyTempDB

GO

IF OBJECT_ID('MyTempDB..SomeTable1','U') IS NOT NULL

DROP TABLE SomeTable1;

CREATE TABLE SomeTable1

(

Firstname varchar(20),

Lastname varchar(20),

Position varchar(20)

);

GO

INSERT INTO SomeTable1

(Firstname, Lastname, Position)

SELECT 'Adam', 'Athome', 'President' UNION ALL

SELECT 'Dagwood', 'Bumstead', 'VicePresident'

GO

When I run this:

SELECT * FROM SomeTable1

FOR XML RAW ('DataRecord'), ROOT ('DataRecords'), ELEMENTS;

..with "results to file" selected, I get this:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<DataRecords><DataRecord><Firstname>Adam</Firstname><Lastname>Athome</Lastname><Position>President</Position></DataRecord><DataRecord><Firstname>Dagwood</Firstname><Lastname>Bumstead</Lastname><Position>VicePresident</Position></DataRecord></DataRecords>

(2 row(s) affected)

I'm trying to produce a file with just the XML:

<DataRecords>

<DataRecord>

<Firstname>Adam</Firstname>

<Lastname>Athome</Lastname>

<Position>President</Position>

</DataRecord>

<DataRecord>

<Firstname>Dagwood</Firstname>

<Lastname>Bumstead</Lastname>

<Position>VicePresident</Position>

</DataRecord>

</DataRecords>

I do not want this part in the XML file:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

..or this part:

(2 row(s) affected)

I just want a file to email out. I'd like to schedule the email with SSRS (no user intervention).

Any ideas???