export to xml file with separate rows

Question

export to xml file with separate rows

sam-433147

Ten Centuries

Points: 1221
More actions
January 27, 2017 at 12:48 am

#326179

for exporting to xml file i use:
bcp "select * from table'" queryout C:\filename.dat -S @@servername -t -c -C 65001 -T
the result in the created file appears in one long row like:
"<Payment_group><Payment>100</Payment><PayMonth>201301</PayMonth></Payment_group>"
how can i force it to appear in separate rows like:
"<Payment_group>
<Payment>100</Payment>
<PayMonth>201301</PayMonth>
</Payment_group>"
thanks ahead

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

Sam,

What BCP outputs is XML. It's the viewer that you're having issue with.
I use NotePad++ and have added the XML Plugin.
Once I open the xml I use the Plugins/XML Tools/Pretty Print option and then save the file again.

Regards,
Matt

sam-433147 Ten Centuries Points: 1221 More actions · Answer 2

Matt Simmons - Friday, January 27, 2017 1:33 PM
Sam,
What BCP outputs is XML. It's the viewer that you're having issue with.
I use NotePad++ and have added the XML Plugin.
Once I open the xml I use the Plugins/XML Tools/Pretty Print option and then save the file again.

thanks for the reply.
NotePad++ can be good solution for own use or for light use.
for the end user it can not be a clean usage.
the solution must must be in the exporting code

sam-433147 Ten Centuries Points: 1221 More actions · Answer 3

thanks for the reply.
NotePad++ can be good solution for own use or for light use.
for the end user it can not be a clean usage.
the solution must must be in the exporting code

Lowell SSC Guru Points: 323518 More actions · Answer 4

back in SQL2000 days, there was an undocumented flag DBCC TRACEON(257) that would format xml when sent to text view; you could try that, but I'd bet you need to convert the data to varchar/nvarchar max, and post process it using a REPLACE function.
there is nothing, native to SQL, that will format xml for you....you'll have to build it or live with it.
CLR(Common Language Runtime is where i would probably handle this, if it was on my plate.
As others have noted, any decent text editor or visual studio will format xml for you to be human readable.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!