I have to export data from SQL Server 2014 SP3 CU3 to an XML file, for a vendor to test something, which I've done successfully.
However, I don't know how their application/system works, as they asked that they need the .xml file to SPECIFICALLY start with
<?xml version ='1.0' encoding='UTF-8'?>
Now, the file is UTF-8 encoded, data displays correctly but they want that line to exist.
This is my query:
EXEC xp_cmdshell 'bcp "set quoted_identifier on; SELECT *, CHAR(13)+CHAR(10) from <table> FOR XML PATH (''Userrecord''), ROOT(''Userinfo''), type" queryout "<path>" -T -c -C65001 -t,'
I needed to put CHAR(13)+CHAR(10) because otherwise it was splitting the tags, making the file not open properly. I also added -C65001 because the table contains special characters, and it worked in SQL Server 2014 SP3.
So the file is formatted in UTF-8, opens fine by a browser and displays all data, but the vendor wants that leading line in the file, and also they want to get rid of the CLRF characters which appear at the end, without deleting the CLRF itself...
Any solution? Thanks!