Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dumping SQL into XML file... Expand / Collapse
Author
Message
Posted Friday, October 11, 2013 10:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 31, 2014 12:52 PM
Points: 73, 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
Post #1504089
Posted Friday, October 11, 2013 10:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's 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)
Post #1504090
Posted Friday, October 11, 2013 11:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 138, Visits: 874
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
Post #1504096
Posted Friday, October 11, 2013 11:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 31, 2014 12:52 PM
Points: 73, 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)....


~mj
Post #1504099
Posted Friday, October 11, 2013 12:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 31, 2014 12:52 PM
Points: 73, 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
Post #1504116
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse