I’ve been working a great deal with SQL XML Bulk Import into SQL Server 2005 lately and wanted to document it for myself for later use. And perhaps others might find the findings useful, as the documentation isn't always very helpful.
So this means I’ll dedicate some blog posts to the XSD for the import, the syntax for the bulk import, the COM stream conversion, and how I've used the import, and various problems I’ve run into.
Even if this then will be very XML inspired, I’ve decided to post it here, instead of another blog, because it fits the SQL Server theme as well.

To start off soft and not make a too long blog post, I’ll provide the syntax for providing the import itself.
The object reference is created by adding a reference to "Microsoft XML BulkLoad for SQL Server 4.0 Type Library" in the version I’m using.
When using a reference, make sure that your thread is running in STA mode and not MTA. Then you’ll get an InvalidCastException (QueryInterface for interface SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed). More about this can be read at MSDN.

The code for the import is in C# as following:

SQLXMLBULKLOADLib.SQLXMLBulkLoad4 _bulkload = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4();
try {
            _bulkload.ConnectionString = _connection;
            _bulkload.KeepIdentity = false;
            _bulkload.XMLFragment = true;
            _bulkload.ErrorLogFile = "";
            _bulkload.Execute(_XSDFile, _DataFileOrCOMStream);
}
catch (Exception ex) { 
}
finally {
            if ((_bulkload != null)) {
            _bulkload = null; 
}  

The connection string needs to be an OLE DB connection string and can for example look like the following:
provider=SQLOLEDB;data source=[datasource];database=[database];integrated security=SSPI

KeepIdentity is important if you wish SQL Server to handle identities itself, otherwise set to true.
XML Fragment is used if you do not get a full XML document starting with the XML declaration tag.
Error log file is as the name suggest for the error file if something goes wrong. It is a good debugging tool, because otherwise the exception messages you get can be quite tricky.

Next blog instalment I’ll start with showing some XSD syntax for various XML files and go more hands on.