none
Problem with line breaks to create XML files using BCP RRS feed

  • Question

  • Dears Sirs,

    I am intending to create a XML file from a query, but I have a problem with the output XML file because of the row terminator (line break)


    I am using the following command :

    EXEC XP_CMDSHELL 'BCP "select COL1, COL2, COL3 from DATABASE..TABLE AS TABLE_NAME FOR XML AUTO, ELEMENTS, ROOT(''OUTPUT_TABLE'')" queryout  D:\TABLE.XML -S. -T -c'


    I expected the output of this command would be:

    <OUTPUT_TABLE>
          <TABLE_NAME>
                <COL1>col1 value</COL1>
                <COL2>col2 value</COL2>
                <COL3>col3 value</COL3>
          </TABLE_NAME>
          <TABLE_NAME>
                  .
                  .
                  .
          </TABLE_NAME>
    </OUTPUT_TABLE>


    But the command created a file like this:

    <OUTPUT_TABLE><TABLE_NAME><COL1>col1 value</COL1><COL2>col2 value</COL2><COL3>col3 value</COL3></TABLE_NAME><TABLE_NAME>...</TABLE_NAME></OUTPUT_TABLE>


    What should I do in order that the XML file has the line break, tag by tag ?


    Thanks in advance,

    José Luiz



    Thursday, November 3, 2011 2:55 PM

Answers

  • The command executes successfully as shows by the results. Also if you check it file should be created with .XML extension and since you open the file it will read that XML file and are showing corresponding rows and columns.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    • Marked as answer by KJian_ Thursday, November 10, 2011 9:06 AM
    Monday, November 7, 2011 3:26 PM
    Answerer

All replies

  • Hi Jose !

    You don't need to specify FOR XML AUTO in your query. You also need to specify formatFile while bcp. Please use the below bcp command to achieve your desired output;

     

    EXEC master..xp_cmdshell 'bcp "YourDatabaseQuery" queryout "YouXMLFilePath" -f formateFile-c..xml -T, -S "ServeName\InstanceName" -U bcpuser -P bcppassword -w' 
    


    EXEC master..xp_cmdshell 'bcp "SELECT EmployeeID,NationalIDNumber FROM AdventureWorks.HumanResources.Employee" queryout "E:\Test\Employee.xml" -f Employee-c..xml -T -S SSSLT105\SQLEXPRESS08 -U sa -P 123456 -w'
    

     

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    Sunday, November 6, 2011 7:14 PM
    Answerer
  • Hi, Hasham,

    Thanks for the reply, but unfortunately the commands did not work. Runtime error has occurred.

    I ran the following command :

    EXEC XP_CMDSHELL 'BCP "select COL1, COL2, COL3 from DATABASE..TABLE AS TABLE_NAME" queryout  "D:\TABLE.XML" -f D:\FORMAT_FILE.XML  -S MY_SERVER -U sa -P MY_PASS -w'

    And the return message was:

    "Warning: -w overrides -f.
    NULL
    Starting copy...
    NULL
    2 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (2000.00 rows per sec.)
    NULL"

    In addition, the output file were the rows of the table and not an XML format.

     


    Monday, November 7, 2011 12:33 PM
  • The command executes successfully as shows by the results. Also if you check it file should be created with .XML extension and since you open the file it will read that XML file and are showing corresponding rows and columns.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    • Marked as answer by KJian_ Thursday, November 10, 2011 9:06 AM
    Monday, November 7, 2011 3:26 PM
    Answerer