• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 994
  • Last Modified:

Export MSSQL table into XML data file.

We are running MSSQL Server 2008.    How can I export a table of data into an XML file?
0
RichNH
Asked:
RichNH
1 Solution
 
Michael DyerCommented:
you can use SQL manager to run a query on the table with "For XML Auto" and then save the results.  This will get you an XML formatted file.

EXAMPLE

use database
select * from dbo.table
for XML AUTO
0
 
Saurabh BhadauriaCommented:
Here is a simple example...

Declare @emp table (Emp_id int identity(1,1) , Emp_name varchar(150) ,Salary int  )

insert into @emp 
select 'a',100
union all 
select 'b',200
union all
select 'c',300 
union all 
select 'd',500

select * from @emp Employee for xml auto  , elements

Open in new window


But you can define output XML architecture through qurey....for example with elements above query   will give you different xml then withour elements cluase
0
 
RichNHAuthor Commented:
Thank you both.   The first solution worked fine.   Took me a little bit to realize that I needed to click on the single link that was returned.  Now I think I just need to get past some sort of memory issue.  The file generated is several GB in size and the XML editor won't accept the entire thing.   I have exported the link out as a flat text file but there seems to be some sort of issue with record length.  Both when it comes out as filename.XML.CSV or when I simply say All Files in the file type when exporting and then name the file filename.XML there seems to be a natural record break at some byte limit where the <CR><LF> is inserted into the file.   I'm seeing this in the preview pane of Windows Explorer.  Perhaps this is just a natural condition of XML files which I have no experience in.  

In any case, the data did come out in XML format.   thank you.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now