sql-serverxmlssmsbcp

XML format in SQL Server BCP

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?

Solution

  • 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 '