Read XML in SQL Server

Kailash Chandra Behera | Wednesday, June 03, 2020

Introduction

In one of my article (Export SQL Table 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 blog Read XML in SQL Server, 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 accessing remote data from remote servers or data sources like data files, XML files, etc.

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

This function has two parameters called the BULK provider and the BULK option. The 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 the data file is provided as the BULK provider.

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

Same as SINGLE_NCLOB is reading data from the file as UNICODE and it returns the content of the file as a single-row, single-column RowSet having type VARCHAR(MAX). SINGLE_BLOB Returns the contents of the 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>

Read XML in SQL Server

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 XML content. 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  
 )

Read XML in SQL Server

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;

Read XML in SQL Server

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.
read xml in sql server

Read XML in SQL Server

Related Articles

  1. Import Excel in C#
  2. Reading Excel file in AngularJS
  3. Creating controller for File Upload
  4. Validating Excel Sheet in C#
  5. Export DataTable To Excel in C#
  6. Export HTML Table to Excel Using JavaScript
  7. Export Table Data into XML in SQL
  8. Export to Excel in MVC

Thanks

By at