Solved

SQL 2005 import export wizard, where is the XML option, if not there how do i do it....?

Posted on 2009-07-01
5
203 Views
Last Modified: 2013-11-10
I have a number of tables I need to output, no format changes, no row changes just a simple output to xml, i have the SQL server Server Import & Export wizard open but cannot find an XML option.... Am I missing something...? If not how on earth does one do it. I am an SQL novice and an even bigger XML novice...
0
Comment
Question by:HolmeRose
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24752852
this command will give you result in XML, you can use that in BCP to get file save in your disk.

SELECT *
FROM AdventureWorks.Person.Contact
WHERE LastName LIKE 'A%' ORDER BY LastName, FirstName
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL
0
 

Author Comment

by:HolmeRose
ID: 24753965
Hi there, many thanks for this. I have added my own query text and run it in Studio but it keeps crashing as it runs out of memory. The table has over 170,000,000 records. Can I dump it direct to an XML file..?

Where do I find the BCP - I am a bit of a novice and have never stepped out of SQL Studio....

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24753985
well BCP is not an issue, you have to run it in DOS mode but the main thing right now is that, you won't be able to dump all those records together in XML, you have to make small chunk and export it.
0
 

Author Comment

by:HolmeRose
ID: 24756557
Why can they not be in one record...? There is plenty of drive space (over 2TB on my data drive and plenty of RAM 8GB)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24760208
only hard drive and RAM are not the factor, so many other things also affects it, your log size, your processor's capacity etc.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question