Using DTS to export a XML format file.

Using DTS to export a XML format file.

Post by Geir Holm » Sat, 22 Feb 2003 00:37:25



Hi.
Today I use DTS to export to .txt files i different ways. That works great,
but now they want it out on XML format. Is there anyway I can use DTS for
this or do I need to go trough my VB code to do this.

Any ideas?

Thanx all

-gh

 
 
 

Using DTS to export a XML format file.

Post by Anith Se » Sat, 22 Feb 2003 03:20:06


You can use FOR XML option in your T-SQL SELECT statement and pipe
the output to an external file (use osql/xp_cmdshell) See SQL Server
Books Online for more details on FOR XML option.

--
- Anith
(Please respond only to newsgroups)

 
 
 

Using DTS to export a XML format file.

Post by David Wang [MSF » Sat, 22 Feb 2003 09:48:12


Depend on what bersion you are using. In SQL 2000 you can do it using For XML statement indicated by Anith
and of course you can write scripts to do it as well

David Wang  MCSD MCAD MCSE MCDBA
Microsoft

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nos...

 
 
 

Using DTS to export a XML format file.

Post by Geir Holm » Sat, 22 Feb 2003 17:42:00


Thanx all.
Using the osql gives me a few other surprices.

1. The Windows Ansi format problems with our spesial characters. I am
putting the result in a file an looking at it in NotePad gives me strange
characters. In edit mode in dos or on the screen directly it looks fine as
it should.
2. How to get rid of headers an lots of blanks after the file.

When I use QA and puts the result out to a file it look very nice indeed.

It is strange that you cannot use a data transformation tast i DTS to put a
FOR XML result into a file directly.

I will try some other ideas I have.

Thank you for the help.

-gh


Quote:> Hi.
> Today I use DTS to export to .txt files i different ways. That works
great,
> but now they want it out on XML format. Is there anyway I can use DTS for
> this or do I need to go trough my VB code to do this.

> Any ideas?

> Thanx all

> -gh

 
 
 

1. Import/Export XML formatted files

I need to import an XML file(s) into SQL Server, and
subsequently export data in an xml format from SQL
Server.  I tried using the DTS import/export text file
facility, but was very surprised that there weren't any
options for an xml formatted text file.  Am I missing
something?  I find it nearly impossible to believe that
SQL Server 2000 doesn't have a native means of importing
and exporting from/to an xml formatted file.  Can anyone
suggest a means of accomplishing my objective?

I'm running SQL Server 2000 on Windows XP.

2. Using MSACCESS autoincrement fields via BDE

3. Exporting file properties in XML format for integration in databases

4. Question on database design

5. DTS to export an XML file

6. Fulltext: Ignored Words

7. Export date using DTS in mm/dd/yyyy format

8. btrieve data file error #5

9. DTS export of XML to a text file

10. DTS Export data to text file in native format

11. Exporting and Importing XML file using ADO

12. EXPORT File Format / Export API

13. How to append to a text file when exporting using DTS