• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1060
  • Last Modified:

Import data from XML file to database table.

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

-- Execute a SELECT statement using OPENXML rowset provider.
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now