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)