I am trying to generate an XML file based on the result of a SQL Query.

In the absence of an XML Destination shape, I've done the following...

1. Exec SQL Statement returns XML & populates a string variable.

2. Use a script component to create a file and write to the file with the
variable.

Is anyone aware of patterns that involve less code?

Cheers,
Dave.

Dave,

You could place your query in the SQL command of the "OLEDB Source" in a
data flow and use the "Flat File Destination" to accomplish the same.

Hope this helps,
Patrik

"Dave" wrote:

Hi Patrik,

thanks for that. I'm having problems using a SQL command/query that returns
XML with the OLEDB Source. It complains that data source does not have column
names.

Any ideas?

Cheers,
Dave.


"Patrik Schneider" wrote:

Dave,

Below is an example of a command where I've named the output "myColumn":

select (select name, object_id
from sys.objects
for xml auto, type) as myColumn

~Patrik

--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <Dave (AT) discussions (DOT) microsoft.com> wrote

Thanks patrik,

I see what your trying to do, but I get a syntax error with the format
you've suggested.

I did this simple "Northwind" example below that copies the format you
suggested. Have I got this right?

select(
select EmployeeID, FirstName
from dbo.Employees
for xml auto, type) as myColumn

"Patrik Schneider [MSFT]" wrote:

Dave,

SQL Server 2000 will return a syntax error on "xml" in the query. The "For
XML" clause is new to SQL Server 2005.

Could you post an example of the query that you were using to retrieve the
XML data in your Execute SQL Task?

Patrik
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <Dave (AT) discussions (DOT) microsoft.com> wrote

Ah, I see.

The "For xml" clause is actually valid in SQL 2000 - but the syntax of
returning the XML as a column (below) seems only to be valid in 2005.

Thanks.

select (select name, object_id
from sys.objects
for xml auto, type) as myColumn

"Patrik Schneider [MSFT]" wrote: