October 11, 2013 at 10:42 am
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?
~mj
October 11, 2013 at 10:46 am
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/
October 11, 2013 at 11:01 am
I suspect this is your problem. There is also a size setting in Management Studio.
-y display_width
Sets the sqlcmd scripting variable SQLCMDMAXFIXEDTYPEWIDTH. It limits the number of characters that are returned for the large variable length data types:
varchar(max)
nvarchar(max)
varbinary(max)
xml
UDT (user-defined data types)
text
ntext
image
October 11, 2013 at 11:08 am
I do not think the DISTINCT is the issue --- I tried that to see what I would get but with or without it in SSMS the Select without the FOR XML clause returns 248 records. Once I add the FOR XML clause, it only give me 46 records and the xml file is not compeleted -- it just ends in the middle of one of the XML tags...
I also checked the field types/sizes. the larges ones are only char(50).... :unsure:
~mj
October 11, 2013 at 12:12 pm
Ok, Not sure exactly why, but I did get it to work finally.
I created an SQL view of the data using RTRIM to eliminate trailing spaces in fields. I then ran my SQLCMD again making sure it used the FOR XML RAW (vs AUTO) and verified that my resulting xml output file was complete this time. (The row count here throws me off -- it now says 30 rows affected but there are actually 248 data rows included). My original output file was being cut off. I do not know/understand why exactly, however after using the view described and the RAW rather than AUTO flag on my FOR XML clause I now have a completed XML file containing all the required data.
~mj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply