none
bcp out to xml file

    Question

  • I am trying to create a stored procedure and then use bcp to export the data to an xml file..I have the following query..

     

    Code Snippet

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000)

    SET @FileName = 'C:\Users\me\test.xml'

     

    SET @bcpCommand = 'bcp "SELECT

    ''CustomerID'' AS ''Parameter/name'',

    CustomerID AS ''Parameter/value'',

    ''ContactName'' AS ''Parameter/name'',

    ContactName AS ''Parameter/value'',

    ''CompanyName'' AS ''Parameter/name'',

    CompanyName AS ''Parameter/value''

    FROM Customers

     

    FOR XML PATH(''T2Method''), ROOT(''Parking''), TYPE, ELEMENTS" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -x'

     

    EXEC master..xp_cmdshell @bcpCommand

     

     

    This executes with no errors but I get that bcp usage results table that looks like below when I try to actually run the proc:

     

    usage: bcp {dbtable | query} {in | out | queryout | format} datafile
      [-m maxerrors]            [-f formatfile]          [-e errfile]
      [-F firstrow]             [-L lastrow]             [-b batchsize]
      [-n native type]          [-c character type]      [-w wide character type]
      [-N keep non-text native] [-V file format version] [-q quoted identifier]
      [-C code page specifier]  [-t field terminator]    [-r row terminator]
      [-i inputfile]            [-o outfile]             [-a packetsize]
      [-S server name]          [-U username]            [-P password]
      [-T trusted connection]   [-v version]             [-R regional enable]
      [-k keep null values]     [-E keep identity values]
      [-h "load hints"]         [-x generate xml format file]
    NULL

     

     

    Am I missing something here? Any help is appreciated.

    Tuesday, May 06, 2008 5:03 AM

Answers

  • Could it be that the line breaks between your query causing the problem? Try removing the line breaks and see if that helps.

     

    Tuesday, May 06, 2008 9:50 AM
    Moderator

All replies

  • Check out your bcp syntax before deploying:

    Code Snippet

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000)

    SET @FileName = 'C:\Users\me\test.xml'

    SET @bcpCommand = 'bcp "SELECT

     

    ''CustomerID'' AS ''Parameter/name'',

    CustomerID AS ''Parameter/value'',

    ''ContactName'' AS ''Parameter/name'',

    ContactName AS ''Parameter/value'',

    ''CompanyName'' AS ''Parameter/name'',

    CompanyName AS ''Parameter/value''

     

    FROM Customers

    FOR XML PATH(''T2Method''), ROOT(''Parking''), TYPE, ELEMENTS" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -x'

    PRINT @bcpCommand

    --EXEC master..xp_cmdshell @bcpCommand

     

     

    Tuesday, May 06, 2008 5:50 AM
  • This means that you had missed some parameter / Wrong syntax of bcp

     

    Execute the bcp command only

     

    After successful execution, merge it with T-SQL Script.

     

    Tuesday, May 06, 2008 5:57 AM
  • Could it be that the line breaks between your query causing the problem? Try removing the line breaks and see if that helps.

     

    Tuesday, May 06, 2008 9:50 AM
    Moderator
  • Yes....that looked odd to me also...

     

    Tuesday, May 06, 2008 11:07 AM
  • Ok it was the line breaks messing things up. However the xml output is all in one big string, not formatted like an xml file.

    Tuesday, May 06, 2008 4:57 PM
  • What do you mean by 'not formatted'? XML is always a single string and it is the XML viewer/editor that formats the content for readability. If you open your XML output in an XML editor you might see correct formatting.

    Tuesday, May 06, 2008 5:09 PM
    Moderator
  • Well its just one long string, its not lined up by nodes..I even opened it up in VS..oh well it is working for what I need, I was just being a little anal. thanks for the help.

     

    • Proposed as answer by M0L Thursday, October 20, 2011 3:37 PM
    Tuesday, May 06, 2008 5:31 PM
  • I am having this same problem. Have you found a solution yet?

    Tuesday, May 13, 2008 2:58 PM
  • What problem muellertj? To creating the xml file?? My syntax I had there worked correctly I just had to remove all the link breaks. Is that what you mean?
    Tuesday, May 13, 2008 3:43 PM
  • did u have any problem wih opening the xml file with xml editor it says invalid charcter in the line i have no clue there are commas , space and $ sigh in the element values is that may be the error.

     

    Friday, May 23, 2008 1:39 PM
  • No I had no problems opening it...open it with notepad and make sure you have no spaces at the top of the xml file
    Friday, May 23, 2008 3:11 PM
  • This will not help you with using the bcp command, but I create a clr stored procedure that accepts a FOR XML query and exports the results to a file path specified as a parameter.  It is posted http://www.sqlclr.net/Examples/tabid/55/articleType/ArticleView/articleId/15/Export-xml-query-results-to-file.aspx.

     

    Here is the code:

     

    Code Snippet
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    Imports System.Xml
     
     
    Partial Public Class outputxml
        <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString)
     
            'Create the variables to hold the values that are supplied by the parameters
            'input by the stored procedure
            Dim xmlDoc As New XmlDocument()
            Dim output As SqlPipe = SqlContext.Pipe()
     
            Try
                'Load the result set into the XmlDoc Variable and then save the results in the
                'path provided by the stored procedure. The values are provided by the
                'input parameters of the stored procedure
                xmlDoc.LoadXml(XmlData.Value)
                xmlDoc.Save(Filename.Value)
     
            Catch ex As Exception
                'If an error occurs catch the message and pipe it back to SQL
                output.Send(ex.Message.ToString)
            End Try
     
        End Sub
    End Class
     
     
    T_SQL CODE
    --Import the assembly into SQL
    ALTER DATABASE AdventureWorks SET trustworthy ON
    CREATE ASSEMBLY outputxml
    from 'C:\outputxml.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS
     
    --If necessary alter the authorization of the database
    ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login
     
     
    -- Create the proc from the imported dll
    CREATE PROCEDURE output
    @xmldata XML,
    @filename nvarchar(1024)
    AS
    EXTERNAL NAME outputxml.[outputxml.outputxml]
    .outputxml
     
     
    -- Test managed stored procedure
    DECLARE @output xml
    SET @output = (SELECT ProductID, Name, ListPrice
                        FROM Production.Product Product
                        FOR XML AUTO, ROOT('Catalog'), TYPE)
    EXEC dbo.outputxml @output, 'c:\Output.xml'
    GO

     

     

    Friday, May 23, 2008 4:27 PM
    Moderator
  • A way to avoid "ALTER DATABASE AdventureWorks SET trustworthy ON"

    Download Microsoft Windows SDK here.

    In command prompt window

    makecert -r -pe -n CN=CodeSigningCertificate -sky signature -a sha256 -len 2048 -m 12 -cy end -sv CodeSigningCertificate.pvk CodeSigningCertificate.cer

    answer prompts with same password.

    Merge private and public keys into PFX file using pvk2pfx.exe utility ( provided with same Microsoft Windows SDK downloaded earlier ) and build manually from command line, instructions to create an key container can be found here using option /keycontainer.

    Follow instructions here to deploy assembly.

    -- cheers


    Miron

    Sunday, October 06, 2013 7:19 PM