locked
Generate XML File and use Bulk Insert to extract data from flat files with help of XML File RRS feed

  • General discussion

  • Hi All,

    From last few days I was working with how to generate XML file and use the XML file to extract data from FIXED lenghts flat files into SQL Server table.Please follow step by step to solve this situation.

    1. Generate XML file for a SQL Server table by running BCP in command prompt.

    bcp DatabaseName.dbo.Product format nul -c -x -f "\\Servername\Folder\Product.XML" -T -S <ServerName>

         2. XML file will generate at folder with default XML language.But if you are using FIXED length flat files,then modify the XML format file as:

    <?xml version="1.0"?>
    <BCPFORMAT
           xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <RECORD>
        <FIELD ID="1" xsi:type="CharFixed" LENGTH="10"/>
        <FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>
        <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\n"
      </RECORD>
      <ROW>
        <COLUMN SOURCE="1" NAME="C1" xsi:type="SQLINT" />
        <COLUMN SOURCE="2" NAME="C2" xsi:type="SQLINT" />
      </ROW>
    </BCPFORMAT>

         3. After modifying the format file, then use BULK INSERT statement inorder to extract fixed length flat files data into SQL Server table:

             BULK INSERT [Database].[Schema].[Product]
               FROM '\\Servername\Folder\Product.txt'
               WITH (FORMATFILE = '\\Servername\Folder\Product.xml')

    Finally, BULK Insert statement will help you to insert data from FIXED length flat files into SQL Server table.

    Hope, it will help someone who are looking for the same situation.

    Thank you,

    Maruthi..


    Maruthi...
    Wednesday, January 12, 2011 6:26 AM

All replies

  • Hi Maruthi,

    Thank you very much for your great share in the forum which will definately benefit others who encounter the same issue.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, January 19, 2011 12:05 PM