Praca z XML

(1)

ITA-101 Bazy Danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 11 Wersja 1.0

Praca z XML

Spis treści

Praca z XML ... 1

Informacje o module ... 2

Przygotowanie teoretyczne ... 3

Przykładowy problem ... 3

Podstawy teoretyczne... 3

Przykładowe rozwiązanie ... 4

Porady praktyczne ... 8

Uwagi dla studenta ... 8

Dodatkowe źródła informacji... 8

Laboratorium podstawowe ... 9

Problem 1 (czas realizacji 45 min) ... 9

Laboratorium rozszerzone ... 15

Zadanie 1 (czas realizacji 90 min) ... 15

(2)

Informacje o module

Opis modułu

Wymiana danych z relacyjnymi bazami danych może być utrudniona ze względu na różnice programowo – sprzętowe itp. Rozwiązaniem jest język XML, który jest niezależny od standardów sprzętowych / programowych.

Cel modułu

Celem modułu jest zapoznanie się z możliwościami zastosowania języka XML w MS SQL 2008.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:

• wiedzieć, jak uzyskać dokument XML ze zwykłego wyniku zapytania

• nauczysz się sterować zapisem danych do dokumentu XML

• będziesz umiał zapisać informacje z dokumentu XML do bazy danych

• nauczysz się przeprowadzać walidację danych XML według zadanych schematów

• poznasz język XQuery, który umożliwia wykonywanie operacji na danych XML

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:

• wiedzieć, jak używać oprogramowania Microsoft Virtual PC

• znać podstawy obsługi systemu Windows 2000 lub nowszego

• znać podstawy obsługi SQL Server Management Studio Mapa zależności modułu

Zgodnie z mapą zależności przedstawioną na Rys. 1, istnieje konieczność wykonania wcześniej modułu 3.

Rys. 1 Mapa zależności modułu

(3)

Przygotowanie teoretyczne

Przykładowy problem

Firma National Insurance wykupiła niedawno udziały w mniejszej, prężnie działającej firmie informatycznej Miracle. Ponieważ dział IT w Miracle używał innej architektury bazodanowej, nastąpił problem z wymianą danych pomiędzy centralą a nowym oddziałem. Informatycy obydwu oddziałów wpadli na pomysł użycia uniwersalnego języka XML do wymiany danych pomiędzy platformami. Przed podjęciem stosownych działań dyrektor działu IT postanowił sprawdzić, co oferuje system MS SQL Server 2008 w tym zakresie.

Podstawy teoretyczne

XML (ang. eXtensible Markup Language) jest językiem znaczników (jak HTML), w którym to programista decyduje o tym, jaka będzie struktura znaczników. W ostatnich latach XML zyskał ogromną popularność jako format idealny do wymiany danych między aplikacjami, nośnik konfiguracji aplikacji, format zapisu danych. Znajomość możliwości XML i standardów skojarzonych jest dziś właściwie niezbędna nie tylko w pracy z bazami danych, ale również z technologiami programistycznymi.

W dzisiejszym świecie informacja jest przechowywana w różnych formatach. Jednym z dość często spotykanych formatów jest XML. Oznacza to, że system bazodanowy powinny oferować możliwość zapisu w swoich strukturach (w swoich bazach danych) danych przemieszanych ze znacznikami XML.

Format danych

Każdy system bazodanowy oferuje bogaty zestaw typów danych. Wśród tych typów można znaleźć sporo typów przechowujących tekst. Ponieważ XML jest formatem tekstowym, może być przechowywany jako tekst, ale typ danych, który w bazach danych ma służyć do przechowywania danych XML, powinien charakteryzować się czymś więcej niż tylko możliwością zapisu znaczników i zaszytych w nich informacji.

Jedną z podstawowych właściwości dokumentu XML jest wymóg, który mówi, że dokument XML musi posiadać odpowiednią formę. Oznacza to, że:

• dokument ma jeden element główny (ang. root), w którym zawarte są pozostałe elementy

• pojedynczy znacznik nie zawiera dwóch atrybutów o takiej samej nazwie

• wartości atrybutów powinny znaleźć się w ogranicznikach (cudzysłowy lub apostrofy)

• znaczniki nie powinny się „zazębiać”.

Poniższy listing ilustruje przykładowy fragment poprawnego dokument XML:

<stolica>Warszawa</stolica>

<glowne_miasta>Wrocław</glowne_miasta>

<glowne_miasta>Gdańsk</glowne_miasta>

<glowne_miasta>Kraków</glowne_miasta>

<glowne_miasta>Poznań</glowne_miasta>

</panstwo>

<stolica>Berlin</stolica>

<glowne_miasta>Monachium</glowne_miasta>

</panstwo>

(4)

<stolica>Praga</stolica>

</panstwo>

</panstwa>

Idealny format do przechowywania danych XML powinien zapewniać automatyczną kontrolę poprawności zapisu danych.

Walidacja

Bardzo często struktura dokumentów XML jest w jednoznaczny sposób określona przy pomocy innych standardów, takich jak XML Schema czy DTD (ang. Document Type Definition). Szczególnie XML Schema jest doskonałym formatem opisującym struktury XML. Idealnie zatem byłoby, gdyby system bazodanowy umożliwiał wykorzystanie standardów walidujących XML do kontroli struktury danych zapisywanych w bazach danych.

MS SQL Server 2000 wprowadził możliwość konwersji danych pobieranych z bazy do dokumentu XML oraz danych z tych plików na wiersze bazy danych. Umożliwiają to słowa kluczowe języka Transact-SQL:

• FOR XML

• OPENXML

MS SQL Server 2005 rozszerza możliwości w/w słów kluczowych, dzięki czemu można sterować sposobem tworzenia dokumentu XML (dokładne informacje w dokumentacji do tych poleceń).

Dodatkowo umożliwiono tworzenie natywnych typów danych XML pozwalających na tworzenie własnych zmiennych oraz dodawanie kolumn do danych XML. Odpowiedzialne za to jest polecenie CREATE XML SCHEMA COLLECTION (dokumentacja: http://msdn2.microsoft.com/en- us/library/ms176009.aspx), a w połączeniu z XQuery (język pozwalający na pisanie zapytań przeszukujących dokumenty XML, dokumentacja: http://msdn2.microsoft.com/en- us/library/ms189075(SQL.100).aspx) pozwala na pisanie zapytań do treści XML, których wynik także jest przedstawiony w dokumencie XML. Innym udoskonaleniem jest możliwość nadania indeksów kolumnom typu XML. Pozwala to na zwiększenie ogólnej wydajności.

MS SQL 2008 wprowadził dalsze udoskonalenia w zakresie walidacji danych. Dodano możliwość stworzenia schematu który może być dopasowany do dowolnych danych. Dodano słowo kluczowe let do XQuery (podstawy można opanować na bazując na tutorialach:

http://www.w3schools.com/xquery/default.asp) dzięki, któremu można przypisywać wartości do zmiennych. Ponadto za pomocą XQuery można wykonywać operacje na danych XML za pomocą insert, replace value of, delete, modify, a MS SQL Server 2008 pozwala na użycie danych XML jako argumentu dla polecenia insert.

Przykładowe rozwiązanie

Rozpoczynając pracę z plikami XML należy zastanowić się jakie dane będziemy w nich przechowywać. Dane z bazy wybieramy np.: poleceniem SELECT. Aby trafiły one do pliku XML na końcu skryptu SQL należy dopisać sekwencję FOR XML z odpowiednimi parametrami. Przykładowo, wybierając dane z tabeli Customers utworzenie pliku XML zapewni nam sekwencja

FOR XML RAW('Customer'), ELEMENTS, ROOT

Po uruchomieniu takiego kodu w Management Studio jako rezultat pojawi nam się odnośnik do pliku XML tak jak pokazano na Rys. 2:

(5)

Rys. 2 Generowanie kodu XML

W tym momencie uzyskaliśmy wynik zapytania w postaci dokumentu XML. Klikając na odnośnik możemy obejrzeć strukturę pliku a następnie zapisać go jako zwykły plik systemowy. Znając podstawy języka XML łatwo wprowadzać modyfikacje i rozbudowywać tego typu pliki nawet poza środowiskiem bazodanowym. Wystarczy dysponować dowolnym edytorem testu.

Rys. 3 Wyświetlenie kodu XML

Pracując z plikami XML zawsze należy zwracać uwagę na sposób kodowania znaków. Warto wymusić odpowiednie kodowanie umieszczając na początku pliku następującą sekwencję

<?xml version="1.0" encoding="utf-16"?>

Co prawda same polecenia XML nie zawierają w swojej strukturze znaków narodowych ale dane pojawiające się w plikach przeważnie takie posiadają. Aby uniknąć przekłamań rozsądnie rozpocząć plik XML tak jak pokazano na rys 4.

(6)

Rys. 4 Dodanie tagu kodowania

W tym momencie posiadamy pełnowartościowy plik XML zawierający dane z naszej bazy. Tak uzyskane dane można z łatwością przenieść do innej bazy danych np. MySql, ponieważ zostały zapisane w uniwersalnym formacie (należy jedynie pamiętać o odpowiednim kodowaniu znaków).

Isnieje możliwość wyeksportowania do pliku XML bardziej złożonych danych. Poniższy kod SQL wybierze wszystkie zamówienia, których ID_Klienta jest równe "ALFKI".

declare @CustomerID nchar(5) select @CustomerID = 'ALFKI'

SELECT 1 as Tag, NULL as Parent,

CustomerID as [Customer!1!customerid], ContactName as [Customer!1],

NULL as [Order!2!orderid], NULL as [Order!2]

FROM Customers

where Customers.CustomerID = @CustomerID UNION

SELECT 2 as tag, 1 as parent,

Customers.CustomerID, Customers.ContactName, Orders.OrderID,

Orders.ShipAddress FROM Customers, Orders

WHERE (Customers.CustomerID = @CustomerID) AND (Customers.CustomerID = Orders.CustomerID) FOR XML EXPLICIT

Możemy więc jasno i precyzyjnie określi interesujący nas zakres danych, który będzie wyeksportowany z systemu bazodanowego.

Porównaj działanie polecenia FOR XML z atrybutami RAW, AUTO oraz PATH oraz atrybutami ELEMENTS, ROOT (oraz bez tych opcji) i innymi zgodnie z dokumentacją na:

http://msdn2.microsoft.com/en-us/library/ms173812.aspx

Posiadając gotowy plik XML zawierający dane wyeksportowane z systemu bazodanowego możemy w prosty sposób wczytać te dane do swojej bazy. W tym celu należy posłużyć się zmiennymi T-SQL typu XML i wczytać do tak zadeklarowanej zmiennej dane ze wskazanego pliku. Taką operację wykonuje przykładowy kod SQL:

DECLARE @xmlDoc XML

SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK 'C:\pliczek.xml', CODEPAGE='utf-16', SINGLE_NCLOB ) AS xmlData)

SELECT @xmlDoc

(7)

Wynikiem jest wczytana treść wcześniej utworzonego pliku, znajdująca się w jednej kolumnie typu XML co ilustruje rysunek 5.

Rys. 5 Wczytanie pliku XML

W operowaniu na danych XML pomocny jest XQuery, czyli język zapytań właśnie dla tego typu dokumentów.

Do tej pory jedynie wyświetlaliśmy zawartość wczytanego pliku XML. Nic nie stoi na przeszkodzie aby za pomocą pliku mapującego dokonać jego zapisu w odpowiedniej tabeli bazy danych. Plik mapujący odzwierciedla dane z pliku XML na tabele w przykładowej bazie Northwind:

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

</ElementType>

</ElementType>

</Schema>

Pozostaje nam tylko stworzyć odpowiedni skrypt Visual Basic, który zrealizuje wstawianie danych do bazy według zadanego wcześniej schematu:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

objBL.ConnectionString = "Provider=SQLOLEDB;Data Source=EVALUATION;Initial Catalog=Northwind;Integrated Security=SSPI;"

objBL.ErrorLogFile = "c:\error.log"

objBL.Execute "c:\customersmapping.xml", "c:\customers.xml"

Set objBL = Nothing

Skrypt połączy się z naszym serwerem bazodanowym a następnie wykona zapytania SQL zgodnie z plikami XML co ilustruje rysunek 6.

(8)

Rys. 6 Wynik zapytania do bazy po aktualizacji danych

Porady praktyczne

Praca z plikami XML może być wydajna i przyjemna. Warto jednak zaznaczyć, że szczególnie początkujący użytkownicy mogą popełniać drobne, ale męczące błędy związane głównie z nieścisłościami w nazewnictwie:

• Zawsze sprawdzaj poprawność nazw wszelkich plików i ich ścieżek dostępu. Często niemożność zlokalizowania pliku skutkuje komunikatami o innych błędach.

• Poprawne określenie kodowania zapobiega przekłamaniom w obróbce danych. Czasami złe kodowanie w ogóle wyklucza wykonanie danej operacji.

• Dowiedź się nieco więcej o podstawach programowania w VBscript. Jest to bardzo prosty, obiektowy język programowania, wykorzystywany w budowie skryptów.

• Czytaj uważnie dziennik błędów. W większości przypadków dziennik wskazuje na poprawne rozwiązanie problemu.

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium jeśli:

• rozumiesz, czym różni się język XML od HTML

• rozumiesz zasadę działania struktury XML Schema

• umiesz zdefiniować przykładową strukturę pliku XML

• umiesz podać przykład zastosowania narzędzia XQuery.

Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.

Dodatkowe źródła informacji

1. William R. Stanek, Microsoft XML – Vademecum, Microsoft Press, 2002 2. Elizabeth Castro, Po prostu XML, Helion, 2001

3. Priscilla Walmsley, Wszystko o XML Schema, WNT, 2007

4. Przemysław Kozienko, Krzysztof Gwiazda, XML na poważnie, Helion, 2002

5. Scott Short , Zastosowanie XML do tworzenia usług internetowych na platformie Microsoft .NET, Microsoft Press 2003

(9)

Laboratorium podstawowe

Problem 1 (czas realizacji 45 min)

Jesteś administratorem systemu bazodanowego w firmie National Insurance. Twój szef zlecił Ci rozpoznanie możliwości płynących z użycia języka XML w systemie MS SQL Server 2008. W pierwszej kolejności postanowiłeś użyć narzędzi dostępnych w systemie na roboczym serwerze Evaluation i sprawdzonej bazie danych Northwind.

Zadanie Tok postępowania

1. Nawiązywanie połączenia z SQL Server 2008

• Zaloguj się do maszyny wirtualnej BD jako użytkownik Administrator z hasłem P@ssw0rd.

• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.

• W oknie logowania kliknij Connect.

2. Wygenerowan ie pliku XML na podstawie danych z bazy

• W oknie Object Explorer rozwiń listę tabel bazy danych Northwind -> Databases -> Tables.

• Kliknij prawym klawiszem myszki tabelę Customers i wybierz opcję Select Top 1000 Rows.

Rys. 7 Wybieranie danych z bazy Northwind

• W okienku, które się pokaże, zamień wartość 1000 na 10 (nie potrzeba tyle danych do testów), a na samym końcu skryptu dopisz:

FOR XML RAW('Customer'), ELEMENTS, ROOT

• wykonaj zapytanie klikając przycisk Execute.

(10)

• Kliknij na odnośnik <Root><Customer>… w celu otwarcia okna z kodem XML.

Rys. 9 Wyświetlenie kodu XML

• zapisz ten wynik do pliku: c:\customers.xml klikając na menu File->Save As.

• Możesz otworzyć plik w przeglądarce, aby zobaczyć jego strukturę, jednak zanim to zrobisz, dopisz na samym początku, najlepiej w edytorze w MS SQL, następującą linię:

<?xml version="1.0" encoding="utf-16"?>

(11)

Rys. 10 Dodanie tagu kodowania

• zamknij utworzone zapytania w głównym oknie Management Studio

3. Wczytanie danych XML do MS SQL

• Kliknij przycisk NewQuery.

• Wpisz następujący kod SQL do nowego okna zapytania:

DECLARE @xmlDoc XML

SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK

'C:\customers.xml', CODEPAGE='utf-16', SINGLE_NCLOB ) AS xmlData) SELECT @xmlDoc

• Kliknij przycisk Execute.

Rys. 11 Wczytanie pliku XML

Jeśli nie można wczytać pliku albo na początku treści pojawiają się tzw. „krzaczki”, to proszę się cofnąć do momentu zapisu i zamiast przez edytor z MSSQL, proszę zapisać metodą kopiowania i wklejania treści dokumentu XML do notatnika

4. Odczyt danych XML - poruszanie się po drzewie danych

W operowaniu na danych XML pomocny jest XQuery, czyli język zapytań właśnie dla tego typu dokumentów.

• Zmień zapytanie SQL z poprzedniego zadania na następujące:

DECLARE @xmlDoc XML

SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK

'C:\customers.xml', CODEPAGE='utf-16', SINGLE_NCLOB ) AS xmlData) SELECT @xmlDoc.query(

'<Customers>

(12)

{

for $i in /root/Customer where $i/Country="Mexico"

return <Order>

{$i/CompanyName}

</Order>

}

</Customers>' )

• Uruchom zapytanie klikając przycisk Execute.

• Kliknij na odnośnik do kodu XML.

5. Eksport wybranych danych do pliku XML

• W nowym oknie wpisz następujący kod SQL:

use Northwind

declare @CustomerID nchar(5) select @CustomerID = 'ALFKI'

SELECT 1 as Tag, NULL as Parent,

CustomerID as [Customer!1!customerid], ContactName as [Customer!1],

NULL as [Order!2!orderid], NULL as [Order!2]

FROM Customers

where Customers.CustomerID = @CustomerID UNION

SELECT 2 as tag, 1 as parent,

Customers.CustomerID, Customers.ContactName, Orders.OrderID,

Orders.ShipAddress FROM Customers, Orders

WHERE (Customers.CustomerID = @CustomerID) AND (Customers.CustomerID = Orders.CustomerID) FOR XML EXPLICIT

• Kliknij na nowy odnośnik do kodu XML.

• Dodaj w pierwszej linijce pliku określenie kodowania znaków:

<?xml version="1.0" encoding="unicode"?>

(13)

Rys. 13 Dodanie tagu kodowania znaków

• Kliknij na menu File->Save As.

• Zapisz kod XML do pliku C:\explicit.xml.

W następnym kroku wczytasz wcześniej zapisany plik explicit.xml i uruchomisz zapytanie SQL zliczające ilość wystąpień tagu Order, każdego elementu Customer:

• W nowym oknie wpisz następujący kod SQL:

DECLARE @xmlDoc XML

SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK 'C:\Documents and Settings\Administrator\Desktop\explicit.xml', CODEPAGE='unicode', SINGLE_NCLOB ) AS xmlData)

SELECT @xmlDoc

SELECT @xmlDoc.query(

'<Orders>

{

for $i in /Customer

let $count :=count($i/Order) return

{$i/Customer}

<ItemCount>{$count}</ItemCount>

</OrdersNumber>

}

</Orders>')

• Kliknij na nowy odnośnik do kodu XML.

Rys. 14 Wyświetlenie wyników z kodu XML

6. Zapisanie wczytanych danych XML do tabeli za pomocą pliku mapującego

• Kliknij menu START->Programs->Accessories->Notepad.

• Wpisz w oknie notatnika następujący kod XML, w którym znajdować się będą informacje, które chcemy zapisać w bazie:

<?xml version="1.0" encoding="unicode"?>

<root>

<CompanyName>Teeest</CompanyName>

<City>Warsaw</City>

</Customer>

</root>

• Zapisz plik pod nazwą c:\customers.xml.

(14)

• W oknie notatnika kliknij menu File->New.

• Wpisz następujący kod XML, który odzwierciedla dane z pliku XML na tabele w bazie Northwind:

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

</ElementType>

</ElementType>

</Schema>

• Zapisz plik pod nazwą c:\customersmapping.xml.

• W oknie notatnika kliknij menu File->New.

• Wpisz następujący skrypt Visual Basic, który zrealizuje wstawianie danych do bazy według zadanego wcześniej schematu:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBL.ConnectionString = "Provider=SQLOLEDB;Data

Source=EVALUATION;Initial Catalog=Northwind;Integrated Security=SSPI;"

objBL.ErrorLogFile = "c:\error.log"

objBL.Execute "c:\customersmapping.xml", "c:\customers.xml"

Set objBL = Nothing

• Zapisz plik pod nazwą c:\ Insertcustomers.vbs.

• Przejdź na dysk lokalny C: i uruchom plik Insertcustomers.vbs.

Rys. 15 Wynik zapytania do bazy po aktualizacji danych

Możesz sprawdzić za pomocą SQL Management Studio->Object Explorer, czy rzeczywiście zaszły zmiany w tabeli Customers bazy Northwind.

(15)

Laboratorium rozszerzone

Zadanie 1 (czas realizacji 90 min)

Zapoznałeś się z podstawami obsługi języka XML w firmowym systemie bazodanowym.

Postanowiłeś wykorzystać zdobytą wiedzę do przeniesienia danych z bazy PraceDyplomowe znajdującej się w centrali do bazy w nowym oddziale firmy. Zanim jednak dane trafią do oddziału, będziesz chciał zweryfikować poprawność zapisu w plikach XML:

• Utwórz skrypty SQL zapisujące do plików XML jak najwięcej informacji z bazy danych PraceDyplomowe

• Utwór nową, roboczą bazę PraceDyplomowe_TEMP

• Odzyskaj zapisane dane do bazy PraceDyplomowe_TEMP za pomocą odpowiedniego skryptu Visual Basic.

Zapisz swoje uwagi w pliku raportu i przedyskutuj z innymi zespołami.