User Id :    Password :      New Member   Forgot Password  
 
How to import data from an XML file to a table in SQL server 2005 or 2008
Description In this article I will show you how to import xml data into a table in SQL server 2005 or 2008. SQL server has strong querying capabilities to extract information from XML files.   No. of Views     10310
  Rating     4
Author Ferhan Siddiqui   Posted On     02 Aug 2010
Tags ADO.NET,XML,SQL SERVER,SQL SERVER : How To    

Sample Code   Download Code

How to import data from an XML file to a table in SQL server 2005 or 2008.

In this article I will show you how to import xml data into a table in SQL server 2005 or 2008.
SQL server has strong quering capabilities to extract information from XML files.

It first loads the xml file from a physical path to the SQL server memory using the BULK command of OPENROWSET. After that, using the XQuery capabilities of SQL Server, XML data is parsed to a normalized table format.

Code:
First, you need to create an xml file C:\Employees.xml and save the following XML data in it.
    
      
        1
        Kamran Khan
      
      
        2
        Ferhanuddin
      
      
        3
        Vivek Gupta
      
	  
        4
        Suman Kundu
      
	  
        5
        Deepika
      
    

Now create , a table Employees is created to store the XML data.

CREATE TABLE Employee(
		EmpId INT, 
		EmpName VARCHAR(50)
		     )



Finally, to insert recodes from the xml file to SQL table, use the following statement that will load the XML file, parse the XML elements to columns, and insert into the Employees table:


INSERT INTO Employee (EmpId, EmpName)
SELECT XmlQuery.Employee.query('EmpId').value('.', 'INT'),
       XmlQuery.Employee.query('EmpName').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(XmlQuery AS XML)
FROM OPENROWSET(
BULK 'C:\Employees.xml',
SINGLE_BLOB) AS T(XmlQuery)
) AS T(XmlQuery)
CROSS APPLY XmlQuery.nodes('Employees/Employee') AS XmlQuery(Employee);


Here is the output:


SELECT e.EmpId, e.EmpName FROM Employee e


EmpId       EmpName
----------- --------------------------------------------------
1           Kamran Khan
2           Ferhanuddin
3           Vivek Gupta
4           Suman Kundu
5           Deepika

(5 row(s) affected)


About Author

About Author I m a software developer having more than 3.5+ years experience. Ferhan
No Photo
 
Country N/A
Company Saffron Infotech Private Limited
Home Page N/A

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Sumit Gupta on 26 Jul 2011 at 11:28 PM
 
Nice Article. Thanks a lot. Keep it up..
 
 
 
Posted By Vikas Gupta on 03 Aug 2010 at 04:52 AM
 
Very helpful article. Really helped a lot.....Thanks.
 
 
 
Posted By Sony Sood on 02 Aug 2010 at 09:30 PM
 
Nice One. Helpful article.
 
 
   
Write your comment here.
Comment
Verification Code