BGSvetionik CLUB

[bgsvetionik]

Importing very large XML files into SQL server can be taxing for standard methods. Thats where SQL Bulk load comes in very handy, Bulk load can load a 100 meg XML File into a SQL Server table in a matter of seconds. Using the microsoft standard xml parser in your application could leave you hanging could leave you hanging for over 10 minutes with a file this size. And to make things even better bulk load is actually quite simple to use.

To get started using bulk load you will need to install the Web Services Toolkit and the SQL Server Client tools on the machine that you will be running bulk load on. You do not have to install any additional components on your sql server itself. So you can simply install the toolkit on your desktop and run your bulk inserts from there.

There are two methods you can use to code your bulk load, either in a DTS or in an application. Both are relatively the same, I will outline bulk loading using VB6(visual basic 6) for this example.

Once you have the xml toolkit installed you will find a reference to Microsoft SQLXML BulkLoad 3.0 Type Library (xblkld3.dll) available. You will need to check this option to run bulk load. Next insert the code below into you application.

Kod: Obeleži sve

Attribute VB_Name = "bulkload"
Option Explicit
Sub Main()


Dim objXBL As New SQLXMLBulkLoad3
objXBL.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=[SERVER];UID=[USER];PWD=[PASS];
DATABASE=[DBNAME];"


objXBL.ErrorLogFile = "errlog.txt"
objXBL.KeepIdentity = False
objXBL.Execute "xsdfile.xsd", "xmlfile.xml"


Set objXBL = Nothing


End Sub

You will see that you need to provide a connection string to your database, I am using an OLE connections tring here but a ODBC will work as well. You will also notice that you must provide two files, first the XML file to be imported and an XSD file for translation. An XSD transformation is very similar to the XSL transformations you may use very often but instead of outputting the XML to another XML file or HTML output an XSD outputs directly to SQL Server. The XSD model allows for addition keys that will allow you to specify tables, column names and other database objects. Below is an example of the contents of your XSD file.

Kod: Obeleži sve

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="product" sql:relation="table_1" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="localkey" type="xsd:string" sql:field="localkey" />

<xsd:element name="name" type="xsd:string" sql:field="name" />

<xsd:element name="description" type="xsd:string" sql:field="description" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="category" sql:relation="table_2" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="path" type="xsd:string" sql:field="path" />

<xsd:element name="id" type="xsd:string" sql:field="_ID" />

<xsd:element name="description" type="xsd:string" sql:field="localeDescriptions_itemDescription_description" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

As you can see you can specify the tables and columns that data should insert into by defining the XML element and mapping it to a database field.

Though the XSD model does allow of multi-tiered XML files, please note that SQL Server Bulk load will crash if you try to load this type of XML file. If the XML file that you are dealing with is multi-teared you will first need to run an XSL tranformation to flatten XML file down to one level. I have included an example XSL file in my application that performs this task.

Application Source: bulkload.rar

[agvozden]

I’d rather not store the XML files Im using in my current project onto the server as they are very verbose so I decided to use the SQLBulkCopy class in .net to allow me to write the data (and only the data - no schema related stuff) within the XML files to a SQL Server database table which gives me the power of SQL (for data filtering) which I’m more familiar with than XPath. The process for doing this is extremely easy, check out the code snippet below:

Kod: Obeleži sve

    DataSet reportData = new DataSet();
    reportData.ReadXml(Server.MapPath(”report.xml”));

    SqlConnection connection = new SqlConnection(”CONNECTION STRING”);
    SqlBulkCopy sbc = new SqlBulkCopy(connection);
    sbc.DestinationTableName = “report_table”;
           
    //if your DB col names don’t match your XML element names 100%
    //then relate the source XML elements (1st param) with the destination DB cols
    sbc.ColumnMappings.Add(”campaign”, “campaign_id”);
    sbc.ColumnMappings.Add(”cost”, “cost_USD”);

    connection.Open();
    //table 4 is the main table in this dataset
    sbc.WriteToServer(reportData.Tables[4]);

    connection.Close();

    //remove the xml file

…

Basically your creating a DataSet, populating it with XML data from a *.xml file somewhere on your system and passing the DataTable of interest from your DataSet into the WriteToServer method of the SQLBulkCopy class. The table specified in DestinationTableName must exist, additionally if your DB column names do not match your XML element names 100% you will need to use the ColumnMappings property to let SQLBulkCopy know which XML elements match to which DB cols. In the code above I’m telling SQLBulkCopy to pair the ‘campaign_id’ and ‘cost_USD’ columns in the DB with the ‘campaign’ and ‘cost’ elements from the XML file. XML elements which are not explicitly specified will not be written to the DB table.

The SQLBulkCopy class is available in the System.Data.SqlClient namespace, so you will need to add a using statement at the top of your code if you want to instantiate a SQLBulkCopy instance. Although my project will only really involve XML data with a max of about 2,000 elements I’ve done a bit of reading about some of the performance benefits some .net developers are getting by using SQLBulkCopy, a couple of people said they were writing 40-50K XML elements (how big were the elements though?) to their DB in only a few seconds, now that’s quick… it sure beats looping over ‘insert into’ statements.

Resources:
http://dotnetslackers.com/Articles/ADO_ ... T_2_0.aspx
http://www.sqlteam.com/article/use-sqlb ... sql-server

http://www.akamarketing.com/blog/135-importing-xml-into-sql-server-table-aspnet.html

[agvozden]

I've written about bulk insert methods for text files in sql server 2005.
.Net 2.0 brings the SqlBulkCopy Class in System.Data.SqlClient namespace.
Speed wise it can't even compare to native sql server methods but it's usefull
if it's the only viable solution.

SqlBulkCopy.WriteToServer has 4 overloads:
SqlBulkCopy.WriteToServer (DataRow[])
Copies all rows from the supplied DataRow array to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable)
Copies all rows in the supplied DataTable to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (IDataReader)
Copies all rows in the supplied IDataReader to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable, DataRowState)
Copies only rows that match the supplied row state in the supplied DataTable to a
destination table specified by the DestinationTableName property of the SqlBulkCopy object.

When importing text files with this method you have to create a DataTable first, import the text file
to the created DataTable and then write this DataTable to server.

With this we're acctually performing 2 tasks in .net:
1. Fill data from text file to DataTable in memory
2. Fill data from DataTable in memory to SQL server

Compared to SQL servers native bulk import methods where we just import the text file directly.

I used the same file and the same table structure as in previous bulk import methods described here.
The time it took to complete the whole process was around 30 seconds.

This is the code i used for import:

Kod: Obeleži sve

private void StartImport()
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;",
        SqlBulkCopyOptions.TableLock);
    bulkCopy.DestinationTableName = "dbo.testSQLBulkCopy";
    bulkCopy.WriteToServer(CreateDataTableFromFile());
    sw.Stop();
    txtResult.Text = (sw.ElapsedMilliseconds/1000.00).ToString();
}
private DataTable CreateDataTableFromFile()
{
    DataTable dt = new DataTable();
    DataColumn dc;
    DataRow dr;

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.Int32");
    dc.ColumnName = "c1";
    dc.Unique = false;
    dt.Columns.Add(dc);
    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.Int32");
    dc.ColumnName = "c2";
    dc.Unique = false;
    dt.Columns.Add(dc);
    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.Int32");
    dc.ColumnName = "c3";
    dc.Unique = false;
    dt.Columns.Add(dc);
    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.Int32");
    dc.ColumnName = "c4";
    dc.Unique = false;
    dt.Columns.Add(dc);
    StreamReader sr = new StreamReader(@"d:\work\test.txt");
    string input;
    while ((input = sr.ReadLine()) != null)
    {
        string[] s = input.Split(new char[] { '|' });
        dr = dt.NewRow();
        dr["c1"] = s[0];
        dr["c2"] = s[1];
        dr["c3"] = s[2];
        dr["c4"] = s[3];
        dt.Rows.Add(dr);
    }
    sr.Close();
    return dt;
}

http://weblogs.sqlteam.com/mladenp/arch ... 11368.aspx

[agvozden]

This ways is OK if we have only one table...

But if we stored related data, then need some advanced method...

I am trying to resolve, this days...