Import Multiple XML file and parse to tabular data in SQL Server

In this article, we will see as how we can upload the xml file from physical drive and then parse it to tabular data. Our problem statement is say we want to upload the entire xml files that we received in one particular day. The logs of file received are maintained in a table named LogDetails.

We are going to use an xml file that can be downloaded here and contains data from AdventureWorks Database.

So let's first download the files from here. After downloading the xml file, store it directly under C:/ drive for the sake of simplicity.    

We will create the log table that stores as when the file was received as below:

----Create table LogDetails

CREATE TABLE [dbo].[LogDetails](

    [FileName] [varchar](50) NOT NULL,

    [DateReceived] [datetime] NULL)

 

---Populate the table . It is to be noted that filename here should be same as one you have stored in C Drive    

    

    insert [LogDetails] values('Log1.xml',GETDATE())

    insert [LogDetails] values('Log2.xml',GETDATE())

    insert [LogDetails] values('Log3.xml',GETDATE())

---We first declare few parameter that we will use to assign different values

declare @firstIteration as int

declare @filename as nvarchar(max)

declare @secondIteration as int

declare @sql as nvarchar(max)

set @sql =''

 

--We will create a temporary table to store xml file. Before we create it, we need to make sure if such file name is already in

--memory for a particular session then we would need to drop it.

 

if exists(select name from tempdb..sysobjects where name='#t')

begin

drop table #t

end

create table #t(xmlfile xml , id int identity(1,1))

 

--Initialize your parameter's for iteration

 

set @firstIteration =1

set @secondIteration =1

 

--iteration starts from 1 and goes till maximum # of files received on particular day

 

while(@firstIteration >=1 and @firstIteration <=(select Max(r) from

(SELECT distinct [FileName],ROW_NUMBER () over (order by [FileName]) as r

FROM dbo.[LogDetails]

where cast(DateReceived as date)=(select MAX(cast(datereceived as Date))

FROM dbo.[LogDetails]))A))

begin

 

--below path you can change if your Log1.xml, Log2.xml, Log3.xml are in different directories. But make sure they all are in one directoery

 

set @filename =N'C:/'

+(select ltrim(rtrim([FileName])) from

(SELECT distinct [ArchivedFileName],ROW_NUMBER () over (order by [FileName]) as r

FROM dbo.[LogDetails]

where cast(DateReceived as date)=(select MAX(cast(datereceived as Date))

FROM dbo.[LogDetails]))A where r=@firstIteration)

set @sql=' insert #t

select * FROM OPENROWSET(BULK N'''+@filename+''',SINGLE_BLOB) AS x'

 

exec sp_executesql @sql

 

--@tb is the temporary table that is generated. If you want you can keep a table for this say ProductUpdate table having all the fields of @tb and

--then insert all the data from @tb to ProductUpdate

 

Declare @xmldata as xml

declare @tb as table(ProductID varchar(50), Name varchar(50), StandardCost varchar(50), ProductNumber varchar(50)

,id int identity(1,1))

 

set @xmldata =(select i from #t where id=@secondIteration)

 

;WITH XMLNAMESPACES(DEFAULT 'http://devtechie.com/Products.xsd')

insert @tb

 

select P.Products.query('./ProductID).value('.','varchar(50)') AS ProductID,

P.Products.query('./Name).value('.','varchar(50)') AS Name,

P.Products.query('./StandardCost).value('.','varchar(50)') As StandardCost,

P.Products.query('./ProductNumber).value('.','varchar(50)') As ProductNumber

from @xmldata.nodes('Products/Products') as P(Products)

 

set @firstIteration =@firstIteration +1

set @secondIteration =@secondIteration +1

 

END

 

select * from @tb

 

.

Tags: , , , , , ,

SQL Server | XML