M Jones (10/11/2013)
I need to simply dump an SQL table into an XML file. Simple enough. I used the SQLCMD utility to do this. My first table isn't very big only 248 records and only 10 columns (only really need 9 of those). I can't seem to get a complete file -- I only get 45 records no matter what I do. How do I get the data to the file completely?Here's what I have thus far:
SQLCMD -S <serverName> -d <database> -o "C:\SQLXML\<filename>.xml" -Q "SELECT DISTINCT DWName, DWCity, DWState, DWPhone, ISNull(DWFax,'') AS DWFax, DWType, DWStateList, ISNULL(DWEmail,'') AS DWEmail FROM <tableName> FOR XML RAW, ELEMENTS;"Running the query in SSMS only processes 45 rows too... I have tried using the FOR XML AUTO as well but still only get part of my file. How do I get ALL my data into the XML file?
Your code looks fine for writing it to file. The problem is in your query. You said it returns 45 rows in SSMS, that means you aren't going to get any more with the same query. Do you actually want the distinct? Is that what is limiting to 45 rows?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/