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