none
BCP XML to file RRS feed

  • Question

  • I am trying to export an XML file from an XML column in SQL Server 2012 and an getting a invalid XML file.  When I copy the XML from the column and paste it into notepad and save it I can open it fine in my XML editor (XMLShell) but the bcp version loses its formatting (comes out on one line) and has invalid characters.  I use the following switches in my bcp command:

    -c -T -S

    I can live with it loosing the formatting and being on one line but I really need the xml to be valid so my content editor can edit it and load it.

    Friday, October 11, 2019 5:41 PM

All replies

  • Hi Robert,

    The XML should be well-formed.

    bcp.exe utility is streaming data into XML file on the file system. That's why it doesn't have line breaks, and looks like a one long string for a human eye. It is absolutely ok.

    Check it out how I am using bcp to generate an XML file.

    Obviously, the @SQL variable shall be specific for your needs.

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
       , @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
       , @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
       , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
       , @tcpPort VARCHAR(10) = '1433'
       , @WindowsAuth BIT = 1   -- 1 is for Windows Authentication, 0 is for SQL Server Authentication
       , @loginID VARCHAR(100) = 'loginID'
       , @password VARCHAR(100) = 'password'
       , @serverName VARCHAR(100) = 'SPACESHIP';
    
    -- /B "WindowTitle" parameters to produce output in the SSSMS !!!
    SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
                + ' "' + @SQL + '"' 
                + ' queryout "' + @outputFileName + '"'
                --+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
                + CASE WHEN @WindowsAuth = 1 THEN ' -T'
                   ELSE ' -U '+ @loginID + ' -P ' + @password
                   END
                + ' -x -c -C 1252 -a 32768'
                + ' -S "' + @serverName + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;


    Friday, October 11, 2019 7:24 PM
  • What is the encoding for the XML document? -c will give you a file on your OEM character set, which may not be what you want. -w may work better.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 11, 2019 9:39 PM
  • Hi Robert,

    The XML should be well-formed.

    bcp.exe utility is streaming data into XML file on the file system. That's why it doesn't have line breaks, and looks like a one long string for a human eye. It is absolutely ok.

    Check it out how I am using bcp to generate an XML file.

    Obviously, the @SQL variable shall be specific for your needs.

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
       , @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
       , @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
       , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
       , @tcpPort VARCHAR(10) = '1433'
       , @WindowsAuth BIT = 1   -- 1 is for Windows Authentication, 0 is for SQL Server Authentication
       , @loginID VARCHAR(100) = 'loginID'
       , @password VARCHAR(100) = 'password'
       , @serverName VARCHAR(100) = 'SPACESHIP';
    
    -- /B "WindowTitle" parameters to produce output in the SSSMS !!!
    SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
                + ' "' + @SQL + '"' 
                + ' queryout "' + @outputFileName + '"'
                --+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
                + CASE WHEN @WindowsAuth = 1 THEN ' -T'
                   ELSE ' -U '+ @loginID + ' -P ' + @password
                   END
                + ' -x -c -C 1252 -a 32768'
                + ' -S "' + @serverName + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;


    I am just stumped on this.  If the XML in the XML Column didn't copy and paste perfectly I would understand (though since its an xml data type I would get errors when a build it.)  I use this same process for several different tables and databases on the same server and this db has two tables that are problem.  Just frustrating!
    Friday, October 11, 2019 9:56 PM
  • I am just stumped on this.  If the XML in the XML Column didn't copy and paste perfectly I would understand (though since its an xml data type I would get errors when a build it.)  I use this same process for several different tables and databases on the same server and this db has two tables that are problem.  Just frustrating!

    Without seeing the file and the original XML it is difficult to say what is going on. Or at least a more details on the errors you get.

    Did you try using -w as I suggested?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 12, 2019 10:01 AM
  • Hi Robert,

    In the sample code that I provided, there are 2 particular spots you need to pay attention:

    1. @SQL variable shall contain a legit SQL or a stored procedure to produce a well-formed XML. You need to test it in SSMS on its own.
    2. -C 1252 switch to generate XML in a particular encoding, because XML is always stored in UTF-16 in MS SQL Server in a XML data type column.



    Sunday, October 13, 2019 12:23 PM
  • 2. -c 1252 switch to generate XML in a particular encoding, because XML is always stored in UTF-16 in MS SQL Server in a XML data type column.

    Well, -C 1252. The -C option sets the code page. The -c option specifies that the output is varchar.

    Whether it actually should be 1252 or something else we don't know. Whence my suggestion to use -w instead of -c.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, October 13, 2019 1:16 PM