locked
Importing Xml with SqlBulkCopy RRS feed

  • Question

  • 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?

    Andrew
    Saturday, November 25, 2006 1:42 PM

Answers

  • 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.

     

    Wednesday, December 13, 2006 8:26 AM

All replies

  • You 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.
    Thursday, November 30, 2006 5:45 PM
  • 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
    Thursday, November 30, 2006 7:58 PM
  • 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.

    Thursday, November 30, 2006 9:12 PM
  • 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.

     

    Wednesday, December 13, 2006 8:26 AM
  • Could you please send the code which you have used to achieve this.
    Thursday, January 18, 2007 3:30 PM
  •  

    Hi Sir

     

    I am facing this error while Importing Xml with SqlBulkCopy

     

    I have written this code on click event of Buttton

     

    protected void ReadXml_Click(object sender, EventArgs e)

    {

    string connectionString = "Data Source=amar;Initial Catalog=db_test_com;User ID=dbm_test;Password=v2345";

    DataSet ds = new DataSet();

    DataTable sourcedata = new DataTable();

    ds.ReadXml(@"C:\Documents and Settings\test\My Documents\Visual Studio 2005\WebSites\WebSite2\dynamicdata/test.xml");

    sourcedata = ds.Tables[0];

    using (SqlConnection sqlconn = new SqlConnection(connectionString))

    {

    sqlconn.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn.ConnectionString))

    {

    bulkCopy.ColumnMappings.Add("productID", "ProductID");

    bulkCopy.ColumnMappings.Add("productName", "Name");

    bulkCopy.DestinationTableName = "tbl_xmlrecord";

    bulkCopy.WriteToServer(sourcedata);

    }

    }

    }

     

    I am getting this error

    {Login failed for user 'dbm_test}

    Plz help as soon as possible.

     

    Best Regards

     

     

    Thursday, March 27, 2008 5:06 AM
  • I have test your code . the problem cause by following line :
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn.ConnectionString))
    replace it by :
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn))
    that's it.

    Sunday, June 21, 2009 8:47 AM