niedziela, 3 sierpnia 2008

Importowanie danych z XML do bazy danych.

WstępAplikacje biznesowe coraz częściej wykorzystują dane pochodzących z róznych źródeł. Coraz częsniej dane te dostarczane są w formacie XML. SQL Server 2005 oferuje wiele funkcji, które umożliwiają przetwarzanie danych w formacie XML.
SQL Server 2005 potrafi przechowywać dane jako dokumenty XML, lub też przetwarzać je i przechowywac zachowując model relacyjnej bazy danych.


Praktyczne zastosowanie
Nie zanudzając dalej teorią pora przejść do konkretnego zastowanie XML w powiązaniu z SQL Server 2005. Poniższy przykład pokaże z jaką łatwością dane mogą zostać zaimportowane z pliku, lub łancucha znaków w formacie XML do tabeli bazy danych.
Poniższy przykład jako dokument XMl będzie wykorzystywał tabelę kursów walut publikowaną na stronach Narodowego Banku Polskiego.
Fragment dokumentu XML, z którego będę korzystał w dalszej częsci tego artykułu:


<tabela_kursow typ="A">
<numer_tabeli>150/A/NBP/2008</numer_tabeli>
<data_publikacji>2008-08-01</data_publikacji>
<pozycja>
<nazwa_waluty>dolar australijski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>AUD</kod_waluty>
<kurs_sredni>1,9339</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar Hongkongu</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>HKD</kod_waluty>
<kurs_sredni>0,2649</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar kanadyjski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>CAD</kod_waluty>
<kurs_sredni>2,0138</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar nowozelandzki</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>NZD</kod_waluty>
<kurs_sredni>1,5061</kurs_sredni>
</pozycja>
</tabela_kursow>


Potrzebne będą jeszcze tabele które będą przechowywać dane.

USE tempdb
GO
CREATE TABLE dbo.WALUTY (
KOD_WALUTY CHAR(3) PRIMARY KEY NOT NULL,
NAZWA_WALUTY VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.KURSY_WALUT (
KOD_WALUTY CHAR(3) NOT NULL,
KURS_SREDNI MONEY NOT NULL,
DATA DATETIME NOT NULL,
PRZELICZNIK SMALLINT NOT NULL,
PRIMARY KEY (KOD_WALUTY, DATA),
FOREIGN KEY (KOD_WALUTY) REFERENCES dbo.WALUTY (KOD_WALUTY)
)


Musimy jeszcze zadeklarować zmienną, która będzie przechowywała nasz dokument XML. Zmienna ta jest typu XML.

DECLARE @XMLDATA AS XML

Aby załadować zawartość pliku XMl do naszej zmeinnej skorzystamy z funkcji OPENROWSET. W SQL Server 2005 funkcja OPENROWSET umożliwia czytanie zawartości pliku bez konieczności korzystania z tabeli. Dzięki korzystamy tylko z jednej istrukcji SELECT.

SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X).

Mając utworzone tabele oraz zadeklarowaną zmienna przechowującą zawartość pliku XML możemy przejść do przetwarzania tych danych. W tym celu wykorzystamy procedure, która jako parametr będzie przyjmowała naszą zmienną XML, a raczej dokument XML, który załadowaliśmy wcześniej do zmiennej.
Procedura przyjmuje dokument XML jako swój parametr a nie scieżkę do pliku. Jest to o tyle istotne, że procedura ta może być wykorzystana w dowolnej aplikacji, która będzie przekazyła dokument XML jako łańcuch znaków. Aplikacja taka może np codziennie pobierać dane bezpośrednio ze stron NBP, i przekazywać zawarośc dokumentu do procedury, bez konieczności materializowania pliku na dysku. O tym jak łatwo można zamienić Dataset w aplikacji .NET do łańcucha znaków kilka słów poświęcę na koniec tego dokumentu.
Nazwijmy naszą procedurę IMPORT_NBP

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS


Na początek musimy zadeklarować zmienną oraz tymczasową tabelę, które pomogą nam w przetwarzaniu danych.

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)


Data publikacji występuje w naszym dokumencie XMl tylko raz. Aby kod był bardziej czytelny będziemy tę wartość przechowywać w zmiennej DATA_PUBLIKACJI.
Tymczasowa tabela KURSY_WALUT będzie przechowywała tymczasowo zawartość naszych elementów XML.
W celu przypisania wartości zmeinnej DATA_PUBLIKACJI skorzystamy z funkcji value.

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

Proszę zwrócić uwagę na fakt, że funkcja value jak i wszystkiego pozostałe fukcje oraz elementy XML są pisane małymi literami. XML rozróżnia wielkość liter. Funkcja text() odczytuje zawartość elementu data_publikacji. Proszę zwrócić uwagę na index elementu [1]. Oznacza to, że funkcja value pobiera pierwszy element data_publikacji w dokumencie XML. Indeksowanie elementów rozpoczyna się od wartości 1, inaczej jak ma to miejsce w .NET Framework, gdzie indeksowanie rozpoczyna się domyślnie od 0. Jeżeli w naszym dokumecie występowałoby więcej elementów data_publikacji moglibyśmy użyć innej wartości indeksu. Zawartość naszego elementu data_publikacji odczytujemy jako DATETIME. Uzywając funkcji value musimy podać jakiego typu jest nasz element.
Mając już datę publikacji przejdźmy do odczytywanie kolejnych danych. Teoretycznie moglibyśmy to zrobić w ten sam sposób jak odczytaliśmy datę, zmieniając tylko element XML z /tabela_kursow/data_publikacji na /tabela_kursow/pozycja/nazwa_valuty. Z praktycznego punktu widzenia jest to jednak zadanie bardzo karkołomne gdyz najpierw musielibyśmy znać ilość elementów w naszym dokumencie XML a później pobierać każdy z nich z osobna.
Aby sprawić, żeby nasz kod był bardziej uniwersalny skorzystamy z dwóch dodatkowych funkcji, służących do przetwarzania dokumentów XML. Będą to funkcje query i nodes.
Poniższe zapytanie zwróci nam wszystkie elementy pozycja z osobna.

SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)

Teraz korzystając z kolejnego, zewnętrznego zapytania możemy pobrać wartości każdego z elementów naszego dokumentu XML. Wszystkie te dane wstawimy do naszej tabeli tymczasowej KURSY_WALUT.

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)


W tym miejscu poświęcę kilka słów średniemu kursowi wymiany. Nasz dokument XML przechowuje tę wartość formie liczby zmienno-pozcyjnej, używając jako separatora dziesiętnego przecinka. SQL Server 2005 domyślnie używa jako separatora kropki. Gdybyśmy odczytali wartośc kurs_sredni jako typ MONEY w naszej tabeli znaleźlibyśmy nie 1,9339 a 19339.00. Dlatego też odczytujemy tę wartość jako typ NVARCHAR, a funkcja REPLACE zatępuje znaki [,] na [.].
W ten sposób mamy już nasze dane z pliku XML w tabeli tymczasowej.
Na koniec pozostaje nam zapisanie wszystkich dancyh do naszych tabel docelowych.
Najpierw zapisujemy dane do tabeli WALUTY, sprawdzając przy okazji czy podane waluta już nie istnieje w naszej bazie danych.

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)


Mając uzupełnioną tabelę z walutami możemy zapisać kursy walut, przy okazji sprawdzając czy kurs waluty dla danego dnia już nie istnieje.

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)


Na koniec pozostaje nam usunięcie tabeli tymczasowej

DROP TABLE #KURSY_WALUT

A także ostatnie

GO

Pora teraz sprawidzić naszą procedurę.

DECLARE @XMLDATA AS XML
SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X)

EXEC IMPORT_NBP @XMLDATA

SELECT * FROM dbo.KURSY_WALUT
SELECT * FROM dbo.WALUTY


Jak już wspomniałem wcześniej procedura ta może zostać wykorzystana w aplikacji, która pobiera dane ze strony www, a później przekazuje cały dataset jako parametr w procedurze.
Zamiana całego dataset w .NET na łańcuch znaków w formacie XML to tylko jedna instrukcja:
Dataset1.GetXml


Podsumowanie
Jak widać na powyższym przykładzie w SQL Server 2005 bardzo łatwo możemy pzretwarzać dane w formacie XML. Dane nie muszą być przetwarzane w aplikacji, tylko wszystko przekazane zostaje na ‘barki’ serwera bazy danych.


Na koniec kompletny kod naszej procedury.

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)

DROP TABLE #KURSY_WALUT

GO