SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dumping SQL into XML file...


Dumping SQL into XML file...

Author
Message
M Jones
M Jones
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 170
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26012 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Bill Talada
Bill Talada
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 1996
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

M Jones
M Jones
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 170
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
M Jones
M Jones
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 170
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search