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
