Hi,

Sorry if this is a simplistic question but I'm having trouble using FOR XML
AUTO in a DTS package
created using the DTS Export wizard in SQL Server 2000.

My SQL is :

Select rtrim(col1) as c1, rtrim(col2) as c2, rtrim(col3) as c3 from table1
for XML AUTO

and I'm outputting to a file, called say test.xml

The output always comes out in some kind of binary format but I want it in
XML format.
What am I doing wrong ?

If I execute the SQL in Enterprise manager I get the output as <long text>,
but not if I put :

Select rtrim(col1) as c1, rtrim(col2) as c2, rtrim(col3) as c3 from table1
for XML AUTO

this gives XML (but not when I use it in the DTS wizard)


If I can't output XML to file, can someone tell me what type of binary
format SQL server stores output in
so that I can attempt to decode it ?

Thanks
Dave

In article <ac73af15b2d7864c964e44e87aadd34c (AT) news (DOT) teranews.com>, Dave S
<dave_xxx70 (AT) hotmail (DOT) com> writes
Simple answer, this doesn't work. Very annoying and frustrating, and I
have tried many times, but Dts just doesn't like the xml stream.

Try this-
http://www.sqlxml.org/faqs.aspx?faq=10

You can change it slightly and stream directly into a file stream rather
than all into memory and the Dom. You can also loose the template query
as well and just use a normal text command or stored procedure instead.
This is all just normal ADO stuff really, so if you use FOR XML and the
stream output from the ADO Command Execute elsewhere in your apps it
should make sense.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren,

Thanks for this, it's useful for me to keep for future use.
The only reason I was trying to use XML from a file was that I thought it
was a neater solution than pipe separated fields in a file.
I've got no choice but to use a file because it's for an import which is
required because direct access to the SQL Server DB is not
possible from the server where the import is to be run (security
restrictions).
I used a pipe separated file in the end, but at least I know that there are
difficulties with XML extracts from DTS now.

Cheers
Dave



"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote in
message news:siC$B+Grz51$Ewb1 (AT) sqldts (DOT) com...