Important alert: (current site time 7/15/2013 7:58:11 PM EDT)
 

VB icon

Currency Exchange rates from Central bank of Turkish Republic

Email
Submitted on: 5/23/2006 6:39:49 PM
By: Ertan SAYGI 
Level: Intermediate
User Rating: By 3 Users
Compatibility: SQL Server 2000
Views: 13122
 
     Getting XML formatted currency exchange rates data from Central bank of Turkish Republic by using stored procedures and saving it for further use.
 
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
--**************************************
-- for :Currency Exchange rates from Central bank of Turkish Republic
--**************************************
Feel free to use it in any application.
If you dont remove coder info i will be grateful...
May the source be with you
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
				
--**************************************
-- Name: Currency Exchange rates from Central bank of Turkish Republic
-- Description:Getting XML formatted currency exchange rates data from Central bank of Turkish Republic by using stored procedures and saving it for further use.
-- By: Ertan SAYGI
--
-- Inputs:Acess to http://www.tcmb.gov.tr/kurlar/today.xml 
Needs appropriate permissions on the server to create a table and stored procedures.
--
-- Returns:Currency Exchange Rates
--
-- Assumes:In English :
	
		 This code generates a table named 'DOVIZKURLARI' for saving currency exchange rates.
		 Then uses Microsoft's XMLDOM object to access Central Bank of the Republic of Turkey web services.
		 sp_merkezbankGetXML stored procedure does all parsing process, and calls another stored procedure 
		 named sp_kurbilgilerini_tabloya_yaz.
		 sp_kurbilgilerini_tabloya_yaz stored procedure, inserts data into DOVIZKURLARI table...
		
		 Central Bank of the Republic of Turkey, anounces exchange rates every workday at 15:30.
		 So you can create a job for automating this task. 
		 
		 Refer to http://www.tcmb.gov.tr/kurlar/today.xml for an example of XML file produced by the central bank.			 
	
 		 In Turkish :
		 Bu kod DOVIZKURLARI adinda bir tablo yaratip, sp_merkezbankGetXML stored prosedürü araciligi ile 
		 Microsoft XMLDOM nesnesini kullanarak Turkiye Cumhuriyeti Merkez Bankasi verilerine ulasir ve yorumlar...
		 sp_kurbilgilerini_tabloya_yaz stored prosedürü ile degiskenlere atanan degerleri, DOVIZKURLARI tablosuna yazar.
		 
		 Turkiye Cumhuriyeti Merkez Bankasi haftaici her gun, 15:30'da bu verileri gunceller.
		 Dolayisi ile SQL serverda bir job tanimi yapip, bu islemi otomatiklestirebilirsiniz.
		 Merkez bankasinin yayinladigi XML dosyasini http://www.tcmb.gov.tr/kurlar/today.xml adresinden inceleyebilirsiniz.
		 Please feel free to contact me with any questions or comments you have.
		 Herhangi bir soru ve yorum icin iletisim kurmaktan cekinmeyiniz.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1068&lngWId=5--for details.--**************************************

/*
Coder 		: Ertan SAYGI
		 ertan@ertansaygi.com	
		 esaygi@anet.net.tr
Date 		: 24.05.2006
RDBMS 		: MSSQL 2000
Description 	: 
		 In English :
	
		 This code generates a table named 'DOVIZKURLARI' for saving currency exchange rates.
		 Then uses Microsoft's XMLDOM object to access Central Bank of the Republic of Turkey web services.
		 sp_merkezbankGetXML stored procedure does all parsing process, and calls another stored procedure 
		 named sp_kurbilgilerini_tabloya_yaz.
		 sp_kurbilgilerini_tabloya_yaz stored procedure, inserts data into DOVIZKURLARI table...
		
		 Central Bank of the Republic of Turkey, anounces exchange rates every workday at 15:30.
		 So you can create a job for automating this task. 
		 
		 Refer to http://www.tcmb.gov.tr/kurlar/today.xml for an example of XML file produced by the central bank.			 
	
 		 In Turkish :
		 Bu kod DOVIZKURLARI adinda bir tablo yaratip, sp_merkezbankGetXML stored prosedürü araciligi ile 
		 Microsoft XMLDOM nesnesini kullanarak Turkiye Cumhuriyeti Merkez Bankasi verilerine ulasir ve yorumlar...
		 sp_kurbilgilerini_tabloya_yaz stored prosedürü ile degiskenlere atanan degerleri, DOVIZKURLARI tablosuna yazar.
		 
		 Turkiye Cumhuriyeti Merkez Bankasi haftaici her gun, 15:30'da bu verileri gunceller.
		 Dolayisi ile SQL serverda bir job tanimi yapip, bu islemi otomatiklestirebilirsiniz.
		 Merkez bankasinin yayinladigi XML dosyasini http://www.tcmb.gov.tr/kurlar/today.xml adresinden inceleyebilirsiniz.
		 Please feel free to contact me with any questions or comments you have.
		 Herhangi bir soru ve yorum icin iletisim kurmaktan cekinmeyiniz.
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DOVIZKURLARI]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DOVIZKURLARI]
GO
CREATE TABLE [dbo].[DOVIZKURLARI] (
	[KURID] [int] IDENTITY (1, 1) NOT NULL ,
	[CAD_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[CAD_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[USD_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[USD_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[DKK_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[DKK_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[SEK_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[SEK_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[CHF_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[CHF_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[NOK_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[NOK_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[JPY_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[JPY_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[SAR_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[SAR_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[KWD_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[KWD_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[AUD_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[AUD_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[EUR_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[EUR_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[GBP_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[GBP_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[RON_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[RON_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[IRR_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[IRR_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[SYP_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[SYP_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[JOD_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[JOD_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[BGL_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[BGL_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[ILS_ALIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[ILS_SATIS] [varchar] (15) COLLATE Turkish_CI_AI NULL ,
	[KUR_TARIH] [smalldatetime] NULL 
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[sp_merkezbankGetXML] AS
DECLARE @oXMLDom int	
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @bLoaded bit
DECLARE @xml as varchar(2000)
DECLARE @CADAlis as varchar(15)			/*CANADIAN DOLLAR Banknote Buying Rate*/
DECLARE @CADSatis as varchar(15)		/*CANADIAN DOLLAR Satis Kuru*/
DECLARE @USDAlis varchar(15)			/*US DOLLAR Banknote Buying Rate*/
DECLARE @USDSatis as varchar(15) 		/*US DOLLAR Banknote Selling Rate*/
DECLARE @DKKAlis varchar(15)			/*DANISH KRONE Banknote Buying Rate*/
DECLARE @DKKSatis as varchar(15)		/*DANISH KRONE Selling Rate*/
DECLARE @SEKAlis varchar(15)			/*SWEDISH KRONA Banknote Buying Rate*/
DECLARE @SEKSatis as varchar(15)		/*SWEDISH KRONA Banknote Selling Rate*/	
DECLARE @CHFAlis varchar(15)			/*SWISS FRANK Banknote Buying Rate*/
DECLARE @CHFSatis as varchar(15)		/*SWISS FRANK Banknote Selling Rate*/	
DECLARE @NOKAlis varchar(15)			/*NORWEGIAN KRONE Banknote Buying Rate*/
DECLARE @NOKSatis as varchar(15)		/*NORWEGIAN KRONE Banknote Selling Rate*/
DECLARE @JPYAlis varchar(15)			/*YEN Banknote Buying Rate*/
DECLARE @JPYSatis as varchar(15)		/*YEN Banknote Selling Rate*/
DECLARE @SARAlis varchar(15)			/*SAUDI RIYAL Banknote Buying Rate*/
DECLARE @SARSatis as varchar(15)		/*SAUDI RIYAL Banknote Selling Rate*/
DECLARE @KWDAlis varchar(15)			/*KUWAITI DINAR Banknote Buying Rate*/
DECLARE @KWDSatis as varchar(15)		/*KUWAITI DINAR Banknote Selling Rate*/
DECLARE @AUDAlis varchar(15)			/*AUSTRALIAN DOLL Banknote Buying Rate*/
DECLARE @AUDSatis as varchar(15)		/*AUSTRALIAN DOLL Banknote Selling Rate*/
DECLARE @EURAlis varchar(15)		 	/*EURO Banknote Buying Rate*/
DECLARE @EURSatis as varchar(15)		/*EURO Banknote Selling Rate*/
DECLARE @GBPAlis varchar(15)		/*POUND STERLING Banknote Buying Rate*/
DECLARE @GBPSatis as varchar(15)		/*POUND STERLING Banknote Selling Rate*/
DECLARE @RONAlis varchar(15)		/*NEW LEU Banknote Buying Rate*/
DECLARE @RONSatis as varchar(15)		/*NEW LEU Banknote Selling Rate*/
DECLARE @IRRAlis varchar(15)			/*RIAL Banknote Buying Rate*/
DECLARE @IRRSatis as varchar(15)		/*RIAL Banknote Selling Rate*/
DECLARE @SYPAlis varchar(15)			/*S.POUND Banknote Buying Rate*/
DECLARE @SYPSatis as varchar(15)		/*S.POUND Banknote Selling Rate*/
DECLARE @JODAlis varchar(15)			/*JORDANIAN DINAR Banknote Buying Rate*/
DECLARE @JODSatis as varchar(15)		/*JORDANIAN DINAR Banknote Selling Rate*/
DECLARE @BGLAlis varchar(15)			/*LEV Banknote Buying Rate*/
DECLARE @BGLSatis as varchar(15)		/*LEV Banknote Selling Rate*/
DECLARE @ILSAlis varchar(15)			/*SHEKEL Banknote Buying Rate*/
DECLARE @ILSSatis as varchar(15)		/*SHEKEL Banknote Selling Rate*/
EXEC @hr = sp_OACreate 'Microsoft.XMLDOM', @oXMLDom OUT
EXEC @hr = sp_OASetProperty @oXMLDom, 'async', false
EXEC @hr = sp_OASetProperty @oXMLDom, 'resolveExternals', false
EXEC @hr = sp_OASetProperty @oXMLDom, 'ServerHTTPRequest', false
EXEC @hr = sp_OAMethod @oXMLDom, 'load', @bLoaded OUT, 'http://www.tcmb.gov.tr/kurlar/today.xml'
EXEC @hr = sp_OAGetProperty @oXMLDom, 'xml', @xml OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(0).childnodes.item(5).nodeTypedValue', @USDAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(0).childnodes.item(6).nodeTypedValue', @USDSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(1).childnodes.item(5).nodeTypedValue', @CADAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(1).childnodes.item(6).nodeTypedValue', @CADSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(3).childnodes.item(5).nodeTypedValue', @DKKAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(3).childnodes.item(6).nodeTypedValue', @DKKSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(4).childnodes.item(5).nodeTypedValue', @SEKAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(4).childnodes.item(6).nodeTypedValue', @SEKSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(5).childnodes.item(5).nodeTypedValue', @CHFAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(5).childnodes.item(6).nodeTypedValue', @CHFSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(6).childnodes.item(5).nodeTypedValue', @NOKAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(6).childnodes.item(6).nodeTypedValue', @NOKSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(7).childnodes.item(5).nodeTypedValue', @JPYAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(7).childnodes.item(6).nodeTypedValue', @JPYSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(8).childnodes.item(5).nodeTypedValue', @SARAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(8).childnodes.item(6).nodeTypedValue', @SARSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(9).childnodes.item(5).nodeTypedValue', @KWDAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(9).childnodes.item(6).nodeTypedValue', @KWDSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(10).childnodes.item(5).nodeTypedValue', @AUDAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(10).childnodes.item(6).nodeTypedValue', @AUDSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(11).childnodes.item(5).nodeTypedValue', @EURAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(11).childnodes.item(6).nodeTypedValue', @EURSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(12).childnodes.item(5).nodeTypedValue', @GBPAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(12).childnodes.item(6).nodeTypedValue', @GBPSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(13).childnodes.item(5).nodeTypedValue', @RONAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(13).childnodes.item(6).nodeTypedValue', @RONSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(14).childnodes.item(5).nodeTypedValue', @IRRAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(14).childnodes.item(6).nodeTypedValue', @IRRSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(15).childnodes.item(5).nodeTypedValue', @SYPAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(15).childnodes.item(6).nodeTypedValue', @SYPSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(16).childnodes.item(5).nodeTypedValue', @JODAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(16).childnodes.item(6).nodeTypedValue', @JODSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(17).childnodes.item(5).nodeTypedValue', @BGLAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(17).childnodes.item(6).nodeTypedValue', @BGLSatis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(18).childnodes.item(5).nodeTypedValue', @ILSAlis OUT
EXEC @hr = sp_OAMethod @oXMLDom, 'getElementsByTagName("Currency").item(18).childnodes.item(6).nodeTypedValue', @ILSSatis OUT
EXEC @hr = sp_OADestroy @oXMLDom
EXEC sp_kurbilgilerini_tabloya_yaz @CADAlis, @CADSatis, @USDAlis, @USDSatis, @DKKAlis, 
@DKKSatis, @SEKAlis, @SEKSatis, @CHFAlis, @CHFSatis, 
@NOKAlis, @NOKSatis, @JPYAlis, @JPYSatis, @SARAlis, 
@SARSatis, @KWDAlis, @KWDSatis, @AUDAlis, @AUDSatis,
@EURAlis, @EURSatis, @GBPAlis, @GBPSatis,
@RONAlis, @RONSatis, @IRRAlis, @IRRSatis,
@SYPAlis, @SYPSatis, @JODAlis, @JODSatis,
@BGLAlis, @BGLSatis, @ILSAlis, @ILSSatis
GO
CREATE PROCEDURE [dbo].[sp_kurbilgilerini_tabloya_yaz] 
@CADAlis varchar(15), @CADSatis varchar(15), @USDAlis varchar(15), @USDSatis varchar(15), @DKKAlis varchar(15), 
@DKKSatis varchar(15), @SEKAlis varchar(15), @SEKSatis varchar(15), @CHFAlis varchar(15), @CHFSatis varchar(15), 
@NOKAlis varchar(15), @NOKSatis varchar(15), @JPYAlis varchar(15), @JPYSatis varchar(15), @SARAlis varchar(15), 
@SARSatis varchar(15), @KWDAlis varchar(15), @KWDSatis varchar(15), @AUDAlis varchar(15), @AUDSatis varchar(15),
@EURAlis varchar(15), @EURSatis varchar(15), @GBPAlis varchar(15), @GBPSatis varchar(15),
@RONAlis varchar(15), @RONSatis varchar(15), @IRRAlis varchar(15), @IRRSatis varchar(15),
@SYPAlis varchar(15), @SYPSatis varchar(15), @JODAlis varchar(15), @JODSatis varchar(15),
@BGLAlis varchar(15), @BGLSatis varchar(15), @ILSAlis varchar(15), @ILSSatis varchar(15)
AS
	INSERT INTO DOVIZKURLARI (CAD_ALIS, CAD_SATIS, USD_ALIS, USD_SATIS, DKK_ALIS, 
DKK_SATIS, SEK_ALIS, SEK_SATIS, CHF_ALIS, CHF_SATIS, 
NOK_ALIS, NOK_SATIS, JPY_ALIS, JPY_SATIS, SAR_ALIS, 
SAR_SATIS, KWD_ALIS, KWD_SATIS, AUD_ALIS, AUD_SATIS,
EUR_ALIS, EUR_SATIS, GBP_ALIS, GBP_SATIS,
RON_ALIS, RON_SATIS, IRR_ALIS, IRR_SATIS,
SYP_ALIS, SYP_SATIS, JOD_ALIS, JOD_SATIS,
BGL_ALIS, BGL_SATIS, ILS_ALIS, ILS_SATIS) 
values
(@CADAlis, @CADSatis, @USDAlis, @USDSatis, @DKKAlis, 
@DKKSatis, @SEKAlis, @SEKSatis, @CHFAlis, @CHFSatis, 
@NOKAlis, @NOKSatis, @JPYAlis, @JPYSatis, @SARAlis, 
@SARSatis, @KWDAlis, @KWDSatis, @AUDAlis, @AUDSatis,
@EURAlis, @EURSatis, @GBPAlis, @GBPSatis,
@RONAlis, @RONSatis, @IRRAlis, @IRRSatis,
@SYPAlis, @SYPSatis, @JODAlis, @JODSatis,
@BGLAlis, @BGLSatis, @ILSAlis, @ILSSatis)
GO


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.