SQL query to file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query to file

Author

Topic

NightPassanger
Starting Member

2 Posts

Posted - 2012-07-19 : 05:37:58

Hi , I have to execute query as a xml , or cvs result. But when i executed on database with too many record , the xml is not compete.

I represented my query as view and I supposed that this code must do the work, but it doen't:

SELECT(SELECT
*
FROM
documents.v_ProposalGeneralInfo
for xml path('ITEM'),type)

for xml PATH(''),type,root('root')

The link for xml is generated but when i click it the following message is shown :
TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to show XML. The following error happened:
Unexpected end of file has occurred. The following elements are not closed: ?????_??_????????, ITEM, root. Line 71, position 270361.

What i should do , any help will be appreciated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 07:28:06

I don't quite know what it is, but I have a few suggestions:

1. If you want to export the data as CSV, it is easier to do it using BCP. There is documentation and a few examples here: http://msdn.microsoft.com/en-us/library/ms162802.aspx

2. Since the query is generating the XML and you are running into problems only when you try to display it in SSMS, you can try the following.

WITH T(c) AS 
(
    SELECT
    	*
    FROM
    	documents.v_ProposalGeneralInfo
    	FOR XML PATH('ITEM'),
    	TYPE
)SELECT * INTO dbo.TestXML FROM T;

This will create a table called TestXML. Now use BCP to get the data to a file by running this from a command window.

bcp "SELECT * FROM YourDatabaseName.dbo.TestXML" queryout TestXML.dat -T -c

This assumes you are running it from the server. If you are connecting remotely, specify server. If you are connecting using SQL authentication provide password etc. See the BCP link above.

As I said earlier, I don't know what the problem is. Even if you do this and get the data into the file, before you declare success, you should check with the consumer of the data to make sure they are able to parse and process the data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 11:13:42

quote:
Originally posted by NightPassanger

Hi , I have to execute query as a xml , or cvs result. But when i executed on database with too many record , the xml is not compete.

I represented my query as view and I supposed that this code must do the work, but it doen't:

SELECT(SELECT
*
FROM
documents.v_ProposalGeneralInfo
for xml path('ITEM'),type)

for xml PATH(''),type,root('root')

The link for xml is generated but when i click it the following message is shown :
TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to show XML. The following error happened:
Unexpected end of file has occurred. The following elements are not closed: ?????_??_????????, ITEM, root. Line 71, position 270361.

What i should do , any help will be appreciated

did you try dumping it onto a file using ssis or bcp and then opening it in browser?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources