Hi All,
During the last few days i was trying to figure it out how to generate XML file for a sql table and load data from a text flat file by using BULK Insert statement. By following the below example we can load data fixed length flat file into SQL table.
1.To generate XML by using BCP command
bcp DBName.Schema.Table format nul -c -x -f "D:\FileName.xml" -T -S
2. Modify the generated XML file inorder to handle for fixed length columns:
Note: Either format the XML file as in screen1 or in screen2.It works for both ways.
3. Use BulkInsert statement inorder to extract data based on fixed length text flat file:
Bulk Insert [DBName].[Schema].[Table]
FROM '\\ServerName\Pathfolder\FileName.txt'
WITH (FORMATFILE = '\\\ServerName\Pathfolder\FileName.xml')
During the last few days i was trying to figure it out how to generate XML file for a sql table and load data from a text flat file by using BULK Insert statement. By following the below example we can load data fixed length flat file into SQL table.
1.To generate XML by using BCP command
bcp DBName.Schema.Table format nul -c -x -f "D:\FileName.xml" -T -S
2. Modify the generated XML file inorder to handle for fixed length columns:
Note: Either format the XML file as in screen1 or in screen2.It works for both ways.
3. Use BulkInsert statement inorder to extract data based on fixed length text flat file:
Bulk Insert [DBName].[Schema].[Table]
FROM '\\ServerName\Pathfolder\FileName.txt'
WITH (FORMATFILE = '\\\ServerName\Pathfolder\FileName.xml'
No comments:
Post a Comment