Saturday 26 November 2016

Loading xml file Data in database in sql server

Suppose we have XML file and want to load that data in database. We can load documents into SQL Server using the OPENROWSET option. This will load the XML file into one large rowset, into a single row and a single column. This rowset can then be queried using the OPENXML function. The OPENXML function allows an XML document to be treated like a table.

Let’s see the example

I am creating a table

create table product
(
ProdictID varchar(10),
Name varchar(50),
ProductNumber varchar(50),
SafetyStockLevel varchar(50),
RecorderPoint varchar(50)
)
I have a XML file
 
I want to load this file to the database.

-- Loading the xml file
Declare @XmlFileData  Varchar(MAX)
Select @XmlFileData = BulkColumn
from OPENROWSET(Bulk 'G:\textxml.xml',Single_blob) xData;

-- Parse the XML into object
Declare @XmlData XML
SET @XmlData = CONVERT(XML, @XmlFileData, 1);

-- select the parsed XML data into our table
insert into test.dbo.product
SELECT
XmlData.value('ProdictID[1]','VARCHAR(1000)') as ProdictID,
XmlData.value('Name[1]','VARCHAR(1000)') as Name,
XmlData.value('ProductNumber[1]','varchar(1000)') as ProductNumber,
XmlData.value('SafetyStockLevel[1]','VARCHAR(1000)') as SafetyStockLevel,
XmlData.value('RecorderPoint[1]','VARCHAR(1000)') as RecorderPoint
FROM @XmlData.nodes('/product/row') as
x(XmlData)


When used with the BULK provider keyword you can name a data file to read as one of three types of objects:
Ø  SINGLE_BLOB, which reads a file as varbinary(max)
Ø  SINGLE_CLOB, which reads a file as varchar(max)
Ø  SINGLE_NCLOB, which reads a file as nvarchar(max)
OPENROWSET returns a single column.
Execute the sql script data will be inserted into the table.
 
See it into the table




Popular Posts