exporting xml from sql server

[hatemgamil]

Greetings

lets say i have a table Employee like this

EmpID,EmpName
1 ,hatem

and i write a query that select * from Employee for xml auto

so the out put will be in xml format

i want some help to know how can i export the result to a xml file to be saved on my computer as i need to read the .xmls files from this folder and deserialize them in my .net application

thnx

 

[TheBugSlayer]

You may want to look at the bcp utility. It may be used to bulk import data into a table or export data from a table to a file. Now, I have never used it with FOR XML...Try. And I believe the file gets created on the server' s HD...not sure, but check it out.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[hatemgamil]

thnx bugslayer

can u please give me more details about bcp

i mean an example

thnx

 

[TheBugSlayer]

Hi. Unfortunately not much time right now, but you may look at the links below:

http://msdn.microsoft.com/en-us/library/ms162802.aspx

http://msdn.microsoft.com/en-us/library/ms191184.aspx

In it' s simplest form, to export data from a table to a data file for instance:

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T

You would type that at a command prompt, or in SSMS, or save it in a command file and execute the file. In the example the -c switch means the data will be exported in the CHAR datatype and -T means BCP (bulk copy) utility connects to SQL Server using a trusted connection. So what I am saying is that maybe for XML you would have something like:

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname FOR XML AUTO" queryout Contacts.txt -c -T

probably with some formatting, but I have not had the chance to test it. I know FOR XML will produce one long line with the XML in it...Maybe your .NET code can parse it.

Let me know what you find out.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Here's a blog written by a friend of mine that explain how to do this with SSIS.

http://blogs.lessthandot.com/index....ming/create-xml-files-out-of-sql-server-with-

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

George,

Looks like the url was truncated. Can you post again? I am inerested to read the article. Thanks

 

[gmmastros]

It looks like TT is losing the last character, which happens to be a dash. Weird. If you click the link and add a dash at the end of the url, it works. This should also work.

http://digs.by/9UYsA5

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Thanks, works perfectly.