The old good BCP easily goes to the history. XML became worldwide adopted standard for data

interchange. So if you are using BCP for import text data files, and you want to use XML import files instead of text files,

now is good time for change.

At first, we can see BCP utility called from xp_cmdshell procedure partial example:

DECLARE @cmd VARCHAR(256)
SET @cmd='bcp ' + @DbName + '..' + @TableName + ' in ' + @FullImportFileName + ' /S' + @ServerName + ' /f' + @FullFmtFileName + ' -T'
EXEC @Result_cmd = master..xp_cmdshell  @cmd BCP

Advantages: 

• BCP enable  fast import for big data files
• Database can import data without developing other application using BCP. 

Although this method for importing data is very fast, it has several limitations when we want to use themin complex data processing systems in integration with other user’s applications:

• BCP is not appropriate for importing XML data
• Inattention using master..xp_cmdshell can seriously endanger SQL Server

  security

BCP disable full control over transactions from application to the final insert in database table.

BCP was a very good utility that can help to build application independent database. What does it

mean? I want see clear border between application and database. Databases have to be independent in sense that every action from application against database

can be “Execute Procedure”. I don’t want any SQL statement from application code acting directly in database risking to damage database logic.

In that case applications bugs will have less damage effects in database.

This is especially important if you want clear developers responsibility in your development team.

In case of unexpected crash of application some procedure can be executed by hand through SQL Query Analyzer.

This is reason because the task for importing data in Database I want to grant to the Database itself instead of

some application or user interface.

Then we need appropriate tool for import XML files in SQL Server database, called from Stored Procedure. Although OPENXML statement can be used for direct import in

database, I prefer this option:

Using SQLXMLBulkLoad.SQLXMLBulkLoad.3.0

On your SQL Server you have to install SQLXML3.0 SP1 (http://msdn.microsoft.com),

then create the file ImportData.xml in 'C:\Folder\ImportData.xml' ImportData.xml

using the following data:

<ImportData>
<Row>
<Field1>Row1_ Filed1_Data</Field1>
<Field2>Row1_ Filed2_Data</Field2>
<Field3>Row1_ Filed3_Data</Field3>
</Row>
   <Row>
   <Field1>Row2_ Filed1_Data</Field1>
   <Field2>Row2_ Filed2_Data</Field2>
   <Field3>Row2_ Filed3_Data</Field3>
   </Row>
</ImportData>

You also need to create a file called schema.xml in the same folder, as

follows:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <ElementType name="Field1" dt:type="string"/>
   <ElementType name="Field2" dt:type="string"/>
   <ElementType name="Field3" dt:type="string"/>
   <ElementType name="ElementRow" sql:is-constant="1">
   <element type="Row"/>
   </ElementType>
   <ElementType name="Row"  sql:relation="TableImport">
   <element type="Field1"  sql:field="TabField1"/>
        <element type="Field2"  sql:field="TabField2"/>
        <element type="Field3"  sql:field="TabField3"/>
   </ElementType>
</Schema>

Create TableImport1 in “YourDatabase” executing the following script SQL Query Analyzer:

CREATE TABLE [dbo].[TableImport1] (
[TabField1] [varchar] (40) NULL,
[TabField2] [varchar] (40) NULL ,
[TabField3] [varchar] (40) NULL) ON [PRIMARY]

GO

Then you can create new procedure in you database:

CREATE PROCEDURE BulkLoad AS 
DECLARE@objectINT
DECLARE @hr INT
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR (255)
DECLARE @Schema VARCHAR(128)DECLARE @ImportDataVARCHAR(128)
DECLARE @ErrorFile VARCHAR(128)
SET @Schema = 'C:\Folder\schema.xml'SET @ImportData =  'C:\Folder\ImportData.xml'SET @ErrorFile =
'C:\Folder\Error.log'
EXEC @hr = sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkLoad.3.0', @object OUT
IF @hr <> 0BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc   RETURNEND
ELSE
EXEC @hr = sp_OASetProperty @object, 'ConnectionString', 'provider=SQLOLEDB.1;data source=SERVERNAME; database= YourDatabase;Trusted_Connection=Yes'
IF @hr <> 0
BEGIN
PRINT 'ERROR sp_OAMethod - ConnectionString'                                      EXEC sp_OAGetErrorInfo @object
RETURN
END
EXEC @hr = sp_OASetProperty @object, 'ErrorLogFile', @ErrorFile
IF @hr <> 0
    BEGIN
    PRINT 'ERROR sp_OAMethod - ErrorLogFile'
    EXEC sp_OAGetErrorInfo @object
    RETURN
    END
   
   EXEC @hr = sp_OAMethod @object, 'Execute', NULL, @Schema, @ImportData
IF @hr <> 0
BEGIN
PRINT 'ERROR sp_OAMethod - Execute'
EXEC sp_OAGetErrorInfo @object
RETURN
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'ERROR sp_OADestroy '
EXEC sp_OAGetErrorInfo @object
RETURN
END
   SELECT  'OK'
GO

At last you are prepared to execute your procedure to import data into TableImport1

by executing the 'BulkLoad' procedure from SQL Query Analyzer. Don’t forget to change connection string appropriate to your

server (the one shown uses a trusted connection, but you could change to use a

sql login). After executing you can test if it was successful by doing a select

* from TableImport1, which should yield three rows.