use bcp queryout export sql data to XML file

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

SSIS and Import/Export (2005)

use bcp queryout export sql data to XML file

Author

Topic

amyc
Starting Member

2 Posts

Posted - 2010-01-15 : 14:04:08

I am using bcp exporting sql data to a XML file. It works but the xml file has line breaks, it put a tag in two lines. I got errors when open it in IE.
I think the size of "Network packet size (bytes): 4096" caused the issue. I tried to increase the size to 35525, but still has the same problem.

------output xml file --------------------
.../demograhics></pers
on><person id="123">...
----------IE error---------------------
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

End tag 'pers' does not match the start tag 'person'

-------sql codes it works----------------------------------
DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd VARCHAR(5000)
SELECT @FileName = 'C:\XMLTemp\SampleXMLOutput.xml'

SELECT @SQLCmd = 'bcp ' +
'"SELECT trim(id) as ""@id"", '+
' rtrim(npi) as ""demograhics/lastname"",'+
' rtrim(firstname) as ""demograhics/firstname"", ' +
' rtrim(middlename) as ""demograhics/middlename"" ' +
' from dbo.Demographics ' +
' FOR XML PATH (''person''),ROOT(''users'') "' +
' queryout ' +
@FileName +
' -w -T -a 65535 -S ' + @@SERVERNAME

SELECT @SQLCmd AS 'Command to execute'

EXECUTE master..xp_cmdshell @SQLCmd

Any ideas?

Thanks

Amy

amyc
Starting Member

2 Posts

Posted - 2010-01-15 : 14:35:27

I fixed it when I added -r after queryout. it removed the default delimiters .
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources