Main menu

ADO.Net & OPENXML to Perform Bulk Database Operations

Last week at my work I had to write a tool to process few hundreds of thousand email files and insert their details to the MS SQL Server 2005 database. Doing it in traditional way might require much connectivity to the database and will consume many resources. I was wondering what if I could insert those whole hundreds thousands of data at once to the db which will be much more efficient. I started googgling and I found OPENXML. This is not a whole new technology but I didn’t know about this before (strange!!).

And few more minutes of research I found out how to perform bulk operations against the databases using OPENXML and stored procedures.

What is OPENXML?

OPENXML is a function added to SQL Server 2000 that provides a row set view over an XML document. Since a row set is simply a set of rows that contain columns of data, OPENXML is the function that allows an XML document to be treated in the familiar relational database format. It allows for the passing of an XML document to a T-SQL stored procedure for updating the data.

OPENXML- Summery

  • It extends the SQL Language
  • It is used within T-SQL Stored Procedures
    • XML Document passed as parameter
  • It uses row and column selectors utilizing XPath
  • It supports the following:
    • Attribute and element-centric mappings.
    • Edge table row set.
    • XML annotation/overflow column.
    • Hierarchy support.

OPENXML and ADO.Net

This code block explains the usage of OPENXML to insert bulk data to database. It simulates the actual process I wanted ( processing .eml files) by reading files in a folder and saving their names, created date and size in bytes.

  1. First create the table below:
    CREATE TABLE FileDetails(
    FileName varchar(50) PRIMARY KEY,
    CreatedDate varchar(50) ,
    Size decimal(18, 0) )
  2. And then create the stored procedure below:
    CREATE PROC sp_bulkinsert @xmldata varchar(max)
    AS
    DECLARE @hDoc int
    exec sp_xml_preparedocument @hDoc OUTPUT,@xmldata
    –inserting data
    Insert Into FileDetails
    SELECT FileName, CreatedDate, Size
    FROM OPENXML (@hdoc, ‘/ds/fileDetails’,2)
    WITH (FileName varchar(50), CreatedDate Datetime, Size decimal) XMLFileDetails
    Where XMLFileDetails.FileName Not IN (Select FileName from FileDetails)
    EXEC sp_xml_removedocument @hDoc
    GO
  3. The below sample code snippet shows how to call this stored procedure to insert bulk data to database at once.
    We can retrieve the file details using FileInfo() objects and create a Dataset of the file details and then we can convert this Dataset to xml using WriteXml() Method of the Dataset.

private static string preparexml(DataSet ds)

{

StringBuilder sb = new StringBuilder();

StringWriter sw = new StringWriter(sb);

ds.WriteXml(sw, XmlWriteMode.WriteSchema);

return sb.ToString();

}

This xml representation of the data can be passed as the parameter to the stored procedure as given below :

private static void bulkInsert(string xml)

{

try{

SqlConnection con = creatCon();

con.Open();

SqlCommand cmd = new SqlCommand(“sp_bulkinsert”, con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter(“@xmldata”,SqlDbType.VarChar));

cmd.Parameters["@xmldata"].Value=xml;

cmd.ExecuteNonQuery();

Console.WriteLine(“Completed inserting file informations to database”);

con.Close();

}

catch(Exception ex)

{

Console.WriteLine(ex.Message);

}

}

That’s all simple isn’t it? You can see the performance of this method by writing another code to insert the same fields using the traditional 1 at a time way. Which surprisingly will show a huge difference in performance and time.

You can use the same way to update, delete records in bulk.

The complete code can be downloaded and tested in the below link :

Complete Source Code

Aneef Fashir (38 Posts)

Software Architect @ Assette, Sri Lanka


  • Pingback:   SQL Server 2008 Table Valued Parameters by Aneef.Net

  • Randy Mardayat

    I tried using your code with my project. I get an “XML parsing error: A declaration was not closed”. Have you ever gotten it?

  • Randy Mardayat

    I tried using your code with my project. I get an “XML parsing error: A declaration was not closed”. Have you ever gotten it?

  • http://www.aneef.net/ Aneef Fashir

    Hi,

    this is not a problem with the code. this happens because of encoding errors. There are several basic approaches to solving this: escaping problematic characters (< becomes <, & becomes &, etc.), escaping entire blocks of text with CDATA sections, or putting an encoding declaration at the start of the feed.

    Another common error is the inclusion of whitespace characters (spaces, tabs, newlines) before the XML Declaration. If an XML Declaration is included, it must be the first thing in the document.

    you can identify whether the XML Generated by the system is valid or invalid by writing it to an xml file in disk and try to open it with any browser. you can write XML to file like this:

    ds.WriteXml(string filename);

  • http://www.aneef.net Aneef Fashir

    Hi,

    this is not a problem with the code. this happens because of encoding errors. There are several basic approaches to solving this: escaping problematic characters (< becomes <, & becomes &, etc.), escaping entire blocks of text with CDATA sections, or putting an encoding declaration at the start of the feed.

    Another common error is the inclusion of whitespace characters (spaces, tabs, newlines) before the XML Declaration. If an XML Declaration is included, it must be the first thing in the document.

    you can identify whether the XML Generated by the system is valid or invalid by writing it to an xml file in disk and try to open it with any browser. you can write XML to file like this:

    ds.WriteXml(string filename);

  • Virgilio

    Also is you store an XML file into SQL with a Varchar Max data type.
    Your shredding process might puke as Varchar will take invalid characters to Varchar this is <, & becomes &, is ok but is not well formed XML Also
    Why do you use XMLFileDetails after the With Statement?????
    I’ve shredded xml in the past and I’ve never used this verbiage

  • Virgilio

    Also is you store an XML file into SQL with a Varchar Max data type.
    Your shredding process might puke as Varchar will take invalid characters to Varchar this is <, & becomes &, is ok but is not well formed XML Also
    Why do you use XMLFileDetails after the With Statement?????
    I’ve shredded xml in the past and I’ve never used this verbiage

  • http://www.aneef.net/ Aneef Fashir

    Hi Virgillio,

    I Know that Varchar(Max) has this problems with invalid characters and i have mentioned about that on the second comment on this article about invalid characters.

    and im not storing the xml in the database. its just that i pass open xml and it retrieve the data from that and insert in to the table, and using WITH Statement im sure its very easy to find out reason.

    Thanks.
    Aneef

  • http://www.aneef.net Aneef Fashir

    Hi Virgillio,

    I Know that Varchar(Max) has this problems with invalid characters and i have mentioned about that on the second comment on this article about invalid characters.

    and im not storing the xml in the database. its just that i pass open xml and it retrieve the data from that and insert in to the table, and using WITH Statement im sure its very easy to find out reason.

    Thanks.
    Aneef

  • Jay

    I got a problem with using openxml to bulk insert XML data into the database. In your sample, assume column ‘Size’ accept NULLs and the XML supplied looks like this: test10/10/2008

    When the sp runs, I got the following error:
    Error converting data type nvarchar to numeric.

    Any clue what’s wrong?

    Thanks.

  • Jay

    I got a problem with using openxml to bulk insert XML data into the database. In your sample, assume column ‘Size’ accept NULLs and the XML supplied looks like this: test10/10/2008

    When the sp runs, I got the following error:
    Error converting data type nvarchar to numeric.

    Any clue what’s wrong?

    Thanks.

  • Jay

    Sorry, the XML messed up!

    <FileName>test</FileName><CreatedDate>10/10/2008</CreatedDate><Size></Size>

  • Jay

    Sorry, the XML messed up!

    <FileName>test</FileName><CreatedDate>10/10/2008</CreatedDate><Size></Size>

Tags

Archives