/fdata%2F2470622%2Favatar-blog-1074652166-tmpphpjYbJuw.jpg){kind=avatar}
Verarbeiten von EZB Währungskursen
Umrechnungskurse für Währungen werden in vielen Fachbereichen und Anwendungsfällen benötigt. Der Börsenbroker benötigt sie im Sekundentakt für die Aktienbewertung, beim Verkauf von Import-Artikel mit Tagespreisen wird der aktuelle Tageskurs benötigt und die Buchhaltung verwendet für die Fremdwährung OP Bewertung einen Monats-Durchschnittskurs.
Die EZB Europäische Zentralbank veröffentlich die Tages-Umrechnungskurse seit Anfang 1999 als XML Datei im Internet. Man kann eine Datei mit den Kursen des letzten Banktages (TARGET Tag), der letzten 90 Tagen und die gesamte Aufstellung erhalten.
Diese Xml Daten bieten sich natürlich dazu an, sie einzulesen und per T-SQL weiter zu verarbeiten, um so eine Referenztabelle mit Umrechnungskursen zu pflegen. Etwas gekürzt sieht der Inhalt der Xml Datei so aus:
<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01"
xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time='2011-04-18'>
<Cube currency='USD' rate='1.4275'/>
<Cube currency='JPY' rate='118.25'/>
<Cube currency='BGN' rate='1.9558'/>
<Cube currency='CZK' rate='24.188'/>
</Cube>
</Cube>
</gesmes:Envelope>
Grundsätzlich ist das Verarbeiten kein Problem, allerdings muss man, um die Xml Daten überhaupt lesen zu können, zunächst die verwendete Namespace mittels WITH XmlNameSpaces deklarieren, hier gesmes:Envelope. Will man zugleich auch noch CTE (Common Table Expressions) verwenden, muss man nur berücksichtigen, das XmlNameSpaces an erste Stelle der WITH Anweisung gesetzt wird, die CTE’s dürfen erst darauf folgen.
Hat man das Namespace erst einmal deklariert, erfolgt das weitere Auslesen der Daten wie bei allen anderen Xml Daten auch. Das ganz Statement für eine Tabelle, das Einlesen und Umwandeln sieht dann so aus:
-- ECB Währungskurse
-- Tabelle für die Tageskurse anlegen, sofern noch nicht vorhanden.
IF OBJECT_ID('[dbo].[EcbDailyExchangeRates]', 'U ') IS NULL
CREATE TABLE [dbo].[EcbDailyExchangeRates]
( [ExcDate] date NOT NULL
,[Currency] char(3) NOT NULL
,[Rate] decimal(19, 6) NOT NULL
,CONSTRAINT [PK_EcbDailyExchangeRates] PRIMARY KEY CLUSTERED
( [ExcDate] ASC
,[Currency] ASC
) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
);
GO
-- Sourcen der Ecb Xml Dateien:
-- Letzter Tag: http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
-- Letzten 90 Tage: http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml
-- Komplett: http://www.ecb.int/stats/eurofxref/eurofxref-hist.xml
-- Laden der Xml Datei in eine Variable
DECLARE @ecb XML;
SET @ecb = (SELECT CONVERT(xml, EcbSrc.BulkColumn) AS XmlRates
FROM OPENRowsET( BULK N'D:\eurofxref-hist.xml'
,SINGLE_BLOB) AS EcbSrc);
-- Selektion der Kurse und Einfügen von fehlenden Werten
;WITH XMLNAMESPACES
( 'http://www.gesmes.org/xml/2002-08-01' as gesmes
,DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref')
INSERT INTO [dbo].[EcbDailyExchangeRates]
( [ExcDate], [Currency], [Rate])
SELECT Cubes.Rows.value('@time', 'date') AS [ExcDate]
,Nodes.Rows.value('@Currency', 'char(3)') AS [Currency]
,Nodes.Rows.value('@Rate', 'decimal(19, 6)') AS [Rate]
FROM @ecb.nodes('/gesmes:Envelope/Cube/*') AS Cubes(Rows)
CROSS APPLY
Cubes.Rows.nodes('Cube') as Nodes(Rows)
LEFT JOIN
EcbDailyExchangeRates AS Dst
ON Dst.ExcDate = Cubes.Rows.value('@time', 'date')
AND Dst.Currency = Nodes.Rows.value('@Currency', 'char(3)')
WHERE Dst.ExcDate IS NULL
AND NOT Nodes.Rows.value('@Currency', 'char(3)') IS NULL;
/idata%2F2470622%2FDESCRIBE_SC1.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
