Load XML into Database

Load xml file into store procedure to insert into table.

Sample xml file:

<?xml version='1.0' encoding='UTF-8' ?>
<FctTypes>
 <reportType>SSS</reportType>
 <tradingDate>01-Jun-2017</tradingDate>
 <period>1</period>
 <Details>
   <facilityType>AAA</facilityType>
   <grossInjection>111.11</grossInjection>
   <netInjection>222.22</netInjection>
 </Details>
 <Details>
   <facilityType>BBB</facilityType>
   <grossInjection>333.33</grossInjection>
   <netInjection>555.55</netInjection>
 </Details>
 <Details>
   <facilityType>CCC</facilityType>
   <grossInjection>122.11</grossInjection>
   <netInjection>244.22</netInjection>
 </Details>
</FctTypes>
<FctTypes>
 <reportType>SSS</reportType>
 <tradingDate>01-Jun-2017</tradingDate>
 <period>2</period>
 <Details>
   <facilityType>AAA</facilityType>
   <grossInjection>111.11</grossInjection>
   <netInjection>222.22</netInjection>
 </Details>
 <Details>
   <facilityType>BBB</facilityType>
   <grossInjection>333.33</grossInjection>
   <netInjection>555.55</netInjection>
 </Details>
 <Details>
   <facilityType>CCC</facilityType>
   <grossInjection>122.11</grossInjection>
   <netInjection>244.22</netInjection>
 </Details>
</FctTypes>

In database store procedure:

@strMG01 contain the string of content in xml file which pass from the application.

DECLARE @docHandle INT
DECLARE @xml XML = @strMG01

 CREATE TABLE #TempMG01(
 [Period] [smallint] NULL,
 [ReportType] [varchar](2) NULL,
 [TradingDate] [datetime] NULL,
 [FacilityType] [varchar](60) NULL,
 [GrossInjection] [varchar](60) NULL,
 [NetInjection] [varchar](60) NULL
 )

 INSERT INTO #TempMG01

 SELECT 
 a.Period,
 a.ReportType,
 a.TradingDate,
 a.FacilityType,
 a.GrossInjection,
 a.NetInjection
 FROM(

 SELECT 
 tbl.TempMG01.value('reportType[1]', 'VARCHAR(2)') ReportType,
 tbl.TempMG01.value('tradingDate[1]', 'DATETIME') TradingDate,
 tbl.TempMG01.value('period[1]', 'smallint') Period,
 i.pd.value('facilityType[1]', 'VARCHAR(60)') FacilityType,
 i.pd.value('grossInjection[1]','VARCHAR(60)') GrossInjection,
 i.pd.value('netInjection[1]','VARCHAR(60)') NetInjection
 FROM @xml.nodes('//FctTypes') AS tbl(TempMG01)
 CROSS APPLY @xml.nodes('//FctTypes/Details') AS i(pd)
 ) a

after this you can just insert it into the table you intended with a insert statement.

Sample inserted record as below:

Untitled.jpg

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s