Import data from XML file to database table.

Posted on 2006-05-04
Last Modified: 2008-01-09
I am trying to learn how to use XML files (I have never worked with XML before!), and need some help. What I wish to do is to import customer-data from a XML-file (customer.xml, which is saved in my projects bin-folder in the Debug folder.) to my database table called customer.
When the user clicks on import customerdata the system is supposed to read the customer.xml file and save the customer information in the customer table. When the transfer is complete the system is to write a message to the user telling how many customers that was imported.
The customer table is like this:

Can someone pls help me with some code to accomplish this?!? I am using Visual Studio, visual basic and SQL Server.
Question by:FabRed
    1 Comment
    LVL 26

    Accepted Solution

    If the XML document is extremely large, you would be best off using the SQL XML bulk upload utility:

    If the XML is more reasonably sized, it's probably easiest to use OpenXML to insert the data.  OpenXML is well documented in SQL Books online at:

    It's also included as a compiled help file when you install SQL Server.  I would also download SQLXML 3.0 from Microsoft, because it includes some big improvements and bug fixes to SQL Servers XML support.  

    Here's an example. Assuming the database is the Northwinds database, and the XML looked like:

    <Customer CustomerID="VINET" ContactName="Paul Henriot">
       <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
          <OrderDetail ProductID="11" Quantity="12"/>
          <OrderDetail ProductID="42" Quantity="10"/>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
       <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
          <OrderDetail ProductID="72" Quantity="3"/>

    The sproc would look like:

    CREATE PROC sp_insert_customers @custdata ntext

    DECLARE @idoc int

    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @custdata

    -- Specify the table to insert into --
    INSERT INTO Customers

    -- Execute a SELECT statement using OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@idoc, '/ROOT/Customer',1)
          WITH (CustomerID  varchar(10),
                ContactName varchar(20))
    -- Don't forget to remove the parsed XML document from memory --            
    EXEC sp_xml_removedocument @idoc

    You can call this sproc any way you like:

    *  via ADO
    *  Create a SQLXML virtual Directory, and call or post to a template located there.
    *  etc.  There are a lot of ways to skin this particular cat.

    There are other methods using SQLXML as well.  My personal favorite is using UpdateGrams, because they are transactional, and a single update gram can either update, insert or delete a record.  It has a built-in method for handling concurrency issues (let's say someone else updates a record before you do, but after you've extracted the data from the database in order to determine what record to update).  These are well documented in SQLXML, but they did not work in the RTM version of SQL Server 2000, so they were added in later with the SQLXML releases.

    Mike Sharp

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API ( has made its way into the popular lexicon of the English language.  A few years ago, …
    Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now