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.