Import XML Files into SQL Server Table

Introduction

In one of my article (Export Table Data in to XML in SQL) we have discussed how to export table content in XML format, there we have discussed the various options available to export table content into XML.

Here in this article we will discuss how to import an XML file content into SQL Server Table. Here we will also discuss how to fetch XML content from XML Column of the table.

Getting Started

The OPENROWSET is a SQL function introduction by Microsoft SQL Server which allows to access remote data from remote server or data source like data file, xml file etc.

The OPENROWSET function can be used in the FROM clause as well as INSERT, UPDATE and DELETE statement.

This function has two parameter called BULK provider and BULK option. BULK provider enables data from a file to be read and returned as a row set. BULK Uses the BULK rowset provider for OPENROWSET to read data from a file.

In SQL Server, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement, the path of data file is provided as BULK provider.

BULK option describes how the data will be read from data source. There are three types of BULK option that are SINGLE_CLOB, SINGLE_NCLOB and SINGLE_BLOB. SINGLE_CLOB is reading data from file as ASCII and it returns content of file as a single-row, single-column rowset having type VARCHAR(MAX).

Same as SINGLE_NCLOB is reading data from file as UNICODE and it returns content of file as a single-row, single-column rowset having type VARCHAR(MAX). SINGLE_BLOB Returns the contents of file as a single-row, single-column rowset of type.

It is recommended that you import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions.

Demonstration-1 Importing XML File into SQL Table

This demonstration reads an xml file having name demo.xml. the xml file contains customer name and purchased order details. See the xml content.

 <ROOT>  
  <Customers>  
   <Customer CustomerName="Ganesh Generals" CustomerID="001" Contact="XXXXXXXXXX">  
    <Orders>  
     <Order OrderDate="2019-10-17T00:00:00" OrderID="OD17102019001">  
      <OrderDetail Quantity="5" ProductID="10" />  
      <OrderDetail Quantity="12" ProductID="11" />  
      <OrderDetail Quantity="10" ProductID="42" />  
     </Order>  
    </Orders>  
   </Customer>  
   <Customer CustomerName="Krishna Stores" CustomerID="002" Contact="XXXXXXXXXX">  
    <Orders>  
     <Order OrderDate="2019-10-17T00:00:00" OrderID="OD17102019002">  
      <OrderDetail Quantity="12" ProductID="11" />  
      <OrderDetail Quantity="10" ProductID="42" />  
     </Order>  
    </Orders>  
   </Customer>  
   <Customer CustomerName="Mukunda Generals" CustomerID="003" Contact="XXXXXXXXXX">  
    <Orders>  
     <Order OrderDate="2019-09-17T00:00:00" OrderID="OD17102019003">  
      <OrderDetail Quantity="3" ProductID="72" />  
     </Order>  
    </Orders>  
   </Customer>  
  </Customers>  
 </ROOT>  

Open a new notepad Copy the above content into a notepad and save the notepad into computer drive as XML. Open your Microsoft SQL Server Management Studio, login into the SQL server and select the database name where you want to conduct this demonstration.

Copy the below code which creates a new table named XMLContentTable having two column ID and XMLContent. Click on the New Query, past the copied code on it and click on Execute.

 CREATE TABLE XMLContentTable  
 (  
 Id INT IDENTITY PRIMARY KEY,  
 XMLContent XML  
 )  

Again, copy the below code which helps to read XML content. click on the New Query and past the copied code. Change the path of xml file, mention the path including the file name where you save the xml file.

 INSERT INTO XMLContentTable(XMLContent)  
 SELECT CONVERT(XML, BulkColumn) AS BulkColumn   
 FROM OPENROWSET(BULK 'D:\Demo.xml', SINGLE_BLOB) AS x;  

In the above code snipped I had mentioned the file path as ‘D:\Demo.xml' as i had stored the xml file in ‘D:\Demo.xml'. my xml file name is Demo.xml.

Finally execute the code. The above code reads content of xml file and inserts the content into XMLContent column of XMLContentTable table.

Demonstration-2 Fetching XML Content in TABLE format

Here we will fetch the inserted XML content from table XMLCntentTables and will display the content in table format in SQL result window. Copy the below code, run it and see the result.

Related Articles

1. Export Table Data in to XML in SQL

Thanks
Kailash Chandra Behera