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

 

Sending email through store procedure

Database email setup

  • Double click ‘Databse Mail’ to start setup

e1

Create mail configuration

  • select as below

e2

  • key in the profile name and click ‘Add’

e3

  • key in email address and the server ip address

e4

  • add the SMTP email address and server ip address

e5

  • click ‘Next’

e6

  • click ‘Finish’

e7

 

Store Procedure

Parse in the profile to be use to send email, recipient email, email body message and email subject.

e8

 

Web Programming guide – Inserting data using SQL data source controller

In developing web base system, retrieving and inserting data is a basic thing. As in ASP.NET, it makes development easy. As we’re going to explore one of the control-SqlDataSource control which I have tested it out with a simple web application form name ‘NewContract.aspx’ in SCM test n error 2 project.

NewContract.aspx
NewContract.aspx

Continue reading