CODEWITHSUNDEEP
sqlexporthelperexport-to-xml
Sna
Sna

Reputation: 1

SQL query to XML output goes something wrong. the format is like text file

I have a very interesting problem with XML output from SQL query.

DECLARE @DateStr VARCHAR(30), @sql nvarchar (4000)
SET @DateStr = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), dateadd(HH,+2,getdate()),120),'.','-'),':','-'),' ','_');
SET @sql ='bcp "SELECT top 1  [Kod] ,[Ad] FROM [SMFT].[dbo].[AlanKonya]  FOR XML PATH(''Cubik'') ,ROOT(''CubicDetails'')" queryout "\\\cgtserver\Development\Cubik_FIFOREPORT_' + @DateStr + '.xml" -T -S CGTSAPP\CGTSSQL120 -w -r -t'
EXEC xp_cmdshell @sql

the Output file seems like this.[notepad++]1

When I look with WinSCP editor it seems like this [WinSCP]2

The file format looks like a text format

I just want to see like this [notepad++ ok ]3

Why does my XML output look like a Text file when other XML files I open look formatted?

I'm having trouble transferring the file with WinScp because it appears as a text file

I'm using SQL 2012 Standard edition. I tried it in SQL 2016 enterprise but I took the same result

Could you help me, please?

Best regards

here is my new quert that is worked

DECLARE @FilePath VARCHAR(4000)DECLARE @SQLStr NVARCHAR(4000),
    @Cmd NVARCHAR(4000),
    @Ret INT,
    @DateStr VARCHAR(30), @sql nvarchar (4000)
SET @DateStr = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), dateadd(HH,+2,getdate()),120),'.','-'),':','-'),' ','_');
SELECT @SQLStr = 'SELECT N''<?xml version=''''1.0'''' encoding=''''UTF-8''''?>'' + (SELECT CAST((SELECT * FROM [SMFT].[dbo].Cubik_FIFOREPORT FOR XML  PATH(''Cubik'') ,ROOT(''CubicFifoReport''), ELEMENTS XSINIL) AS NVARCHAR(MAX)))'
SELECT @SQLStr AS SQLStr
SELECT @FilePath ='\\cgtserver\DEVELOPMENT\Cubik_FIFOREPORT_' + @DateStr + '.xml'
SELECT @Cmd = ' bcp " ' + @SQLStr + '" queryout '+@FilePath+'  -c -r  "" -T -S ' +@@ServerName
EXEC @Ret = master.dbo.xp_cmdshell @Cmd

Upvotes: 0

Views: 493

Answers (1)

Martin Prikryl
Martin Prikryl

Reputation: 202574

Your output file is in UTF-16 encoding with BOM. WinSCP internal editor does not support UTF-16, while Notepad++ does.

If you want to use WinSCP internal editor do view/edit the file, export the file using a different encoding. Use bcp -C switch for that. The best would be to use UTF-8 (65001), but it's supported since SQL server 2016 only. With 2012, you would have to resort to "ANSI/Microsoft" encoding (ACP), but that's not binary safe. See bcp documentation.

queryout -C{65001} "path"

Are you aware that you can configure WinSCP to edit files in Notepad++ instead of the internal editor?

Upvotes: 0

Related Questions