XML format in SQL Server BCP

Question

I run below statement to generate a XML output in SQL Server:

EXEC xp_cmdshell 'bcp "select * FROM DataCenter.Dbo.TblCity  FOR XML AUTO" 
     queryout "C:\Sample\Test.xml" -T  -c -r -C 65001  -t, '

The output is:

<DataCenter.Dbo.TblCity Id="1" PortalUserId="11"/><DataCenter.Dbo.TblCity Id="2" PortalUserId="22"/>

But I want the result be like this:

<DataCenter.Dbo.TblCity Id="1" PortalUserId="11"/>
<DataCenter.Dbo.TblCity Id="2" PortalUserId="22"/>

Would you please answer how can I get the correct result?

Like HTML, XML derives from SGML and doesn't care about whitespace between elements. What problem are you trying to solve here? — AlwaysLearning, Jul 26 '23 at 07:41
Thank You. first format, when the table has many rows, is not easy readable by eye. I want the element "DataCenter.Dbo.TblCity" be located under each other. — Reza Davoudian, Jul 26 '23 at 07:47
You can use an editor which can pretty print your xml for you. Or build string manually by casting xml to nvarchar and then adding some newlines. — siggemannen, Jul 26 '23 at 07:56
Does this answer give you a direction: https://stackoverflow.com/a/13019414/3710053 — Siebe Jongebloed, Jul 26 '23 at 07:59
select replace((select * from yourtable FOR XML AUTO), '><','> <') — siggemannen, Jul 26 '23 at 08:13
there should be a new line between the > < but comments don't accept that :) — siggemannen, Jul 26 '23 at 08:13
select replace((select * from yourtable FOR XML AUTO), '><','>' + CHAR(10) + '<') — siggemannen, Jul 26 '23 at 10:31
or if you sit on windows: select replace((select * from yourtable FOR XML AUTO), '><','>' + CHAR(13) + CHAR(10) + '<') — siggemannen, Jul 26 '23 at 10:33
Thank You Bro! but a bit change should be applied. I post the answer and mention you. @siggemannen — Reza Davoudian, Jul 26 '23 at 10:51

score 0 · Accepted Answer · answered Jul 26 '23 at 10:57

Thanks To https://stackoverflow.com/users/13061224/siggemannen

Split element by replace in query level and use "-r \n":

EXEC xp_cmdshell 'bcp "select replace((select * FROM DataCenter.Dbo.TblCity  FOR XML AUTO), '/><','/>' + CHAR(13) + CHAR(10) + '<')" queryout "C:\Sample\Test.xml" -T  -c -r \n -C 65001 '

XML format in SQL Server BCP

1 Answers1