sql server dts: SSIS - Creating an XML File -- sql server dts

RE: SSIS - Creating an XML File

Patrik Schneider
1/9/2006 2:51:03 PM

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

[quoted text, click to view]

RE: SSIS - Creating an XML File

Dave
1/9/2006 9:22:01 PM

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.

[quoted text, click to view]

Re: SSIS - Creating an XML File

Patrik Schneider [MSFT]
1/10/2006 11:21:09 AM

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.
[quoted text, click to view]

Re: SSIS - Creating an XML File

Dave
1/10/2006 1:30:02 PM

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

[quoted text, click to view]

Re: SSIS - Creating an XML File

Patrik Schneider [MSFT]
1/11/2006 2:17:45 PM

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.
[quoted text, click to view]

Re: SSIS - Creating an XML File

Dave
1/11/2006 7:18:01 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.