Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.