BCP export to XML, opening XML gives error: Extra content at the end of the document

Question

I´m trying to export to XML format using BCP and the XML file is generated correctly but the actual content seems wrong. Can anyone help please?

When I try to open the XML in a browser I get the following error message:

This page contains the following errors: error on line 1 at column 62: Extra content at the end of the document

The SQL select that I'm using is:

DECLARE @fileName VARCHAR(50)
DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

SET @fileName = 'c:\fund_lib\test.xml'

USE PORT_APP_SQL

DROP TABLE ##temp;

WITH cte1
     AS (SELECT LTRIM(RTRIM(codigo))             AS code,
                CONVERT(VARCHAR(10), fecha, 120) AS date,
                precio                           AS NAV
         FROM   mpr_price_history
         WHERE  codigo IN( 'LU0038743380', 'LU0086913042', 'LU0265291665', 'LU0098860363',
                           'LU0128525689', 'LU0121204944', 'CZ0008474780', 'LU0363630376',
                           'LU0372180066', 'LU0271663857', 'LU0271663774', 'LU0363630707', 'LU0313643024' ))
SELECT *
INTO   ##temp
FROM   cte1

SET @sqlStr = 'select * from ##temp  order by code, date desc FOR XML RAW (''code'');'
-- Save XML records to a file:
SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName
              + ' -S "MPR01\SQLEXPRESS"  -T -w'

EXEC xp_cmdshell @sqlCmd  

And this is the error message if I open it in Firefox ( sorry is in Spanish).

error message seems to be at the end of each line

Answer 1

I think that your whole query can be simplified... No need for a CTE or a temp table...

attention The solution for your problem is - probably! - the missing root node, as mentioned by @TT.. If adding a root node solves your problem, please do not accept my answer (although you might vote it up, if you like it :-) ).

Your problem might be bound to a mix of encodings. If your output includes special characters, there could be some problems when you mix 8-byte encoding (VARCHAR) and the output with option -w. Therefore I put this all in NVARCHAR(MAX)

My suggestion to get things slim:

USE PORT_APP_SQL;

DECLARE @fileName NVARCHAR(50) = 'c:\fund_lib\test.xml';
DECLARE @cmd      NVARCHAR(MAX);

SET @cmd = 
N'SELECT LTRIM(RTRIM(codigo))             AS code
      ,CONVERT(VARCHAR(10), fecha, 120)   AS [date]
      ,precio                             AS NAV
FROM   mpr_price_history
WHERE  codigo IN( ''LU0038743380'', ''LU0086913042'', ''LU0265291665'', ''LU0098860363'',
                  ''LU0128525689'', ''LU0121204944'', ''CZ0008474780'', ''LU0363630376'',
                  ''LU0372180066'', ''LU0271663857'', ''LU0271663774'', ''LU0363630707'', ''LU0313643024'' )
ORDER BY code,[date] DESC
FOR XML RAW(''code''),ROOT(''root'');'

-- Save XML records to a file:
SET @cmd = N'bcp "' + @cmd + N'" queryout ' + @fileName
              + N' -S "MPR01\SQLEXPRESS"  -T -w'

EXEC xp_cmdshell @cmd;