SQL Server 2005.
Zaawansowane
rozwiÄ…zania biznesowe
Autor: Igor Kruk, Artur MoS
cicki
ISBN: 978-83-246-1333-5
Format: 158x235, stron: 312
ZdobÄ…dx
wiedzÄ™ o tworzeniu zaawansowanych aplikacji bazodanowych!
" Jak używać tabel tymczasowych do tworzenia specjalnych hierarchii?
" Do czego służą zmienne tablicowe?
" Na czym polega konwertowanie danych relacyjnych do formatu XML?
SQL Server 2005 to pierwsza wersja serwera, w której dane XML są przechowywane
i przetwarzane faktycznie jako XML, a nie jako pochodne danych tekstowych
lub binarnych, jak to było w wersjach poprzednich. W SQL Server 2005 możemy użyć
typu danych XML jako kolumny, zmiennej lokalnej lub parametru. Możemy w niej
przechowywać całe dokumenty XML lub tylko ich fragmenty (niezawierające elementu
głównego, tzw. root node). Integracja z platformą Microsoft NET oraz ulepszone funkcje
Business Intelligence pozwalają programistom na skupienie się na najważniejszych
zadaniach, bez koniecznoS
ci pracy w nieznanym S
rodowisku, a przedsiębiorstwom dają
możliwoS
ć przekształcania informacji w lepsze rozwiązania biznesowe.
Książka  SQL Server 2005. Zaawansowane rozwiązania biznesowe przedstawia jeden
z najpopularniejszych serwerów bazodanowych służących do budowy różnych
systemów informatycznych, czyli SQL Server 2005. Ten obszerny podręcznik zawiera
szczegółowe informacje oraz przykłady dotyczące wielowymiarowych baz danych
oraz wymagań, jakie mogą pojawić się podczas budowy mechanizmów ich zasilania
danymi x
ródłowymi. Czytając go, dowiesz się, jak tworzyć efektywne i wydajne
aplikacje oraz nauczysz się wdrażać nowatorskie pomysły, które każdemu
przedsiębiorstwu przyniosą wymierne korzyS
ci biznesowe.
" Perspektywy
" Procedury i funkcje
" Wyzwalacze
" Dynamiczny SQL
" Tabele tymczasowe i zmienne tablicowe
" Transakcje i wyjÄ…tki w aplikacjach biznesowych
Wydawnictwo Helion
" Full Text Search
ul. KoS
ciuszki 1c
" XML
44-100 Gliwice
" Database Mail
tel. [phone]
" RozwiÄ…zania biznesowe
e-mail: [e-mail]
" Integracja z .NET i CLR
" SQL Server Integration Services
Dowiedz się, jak tworzyć efektywne aplikacje bazodanowe
i wdrażaj korzystne rozwiązania programistyczne dla biznesu!
Spis tre ci
Wst p .............................................................................................. 9
Rozdzia 1. Perspektywy .................................................................................. 11
Wst p .............................................................................................................................. 11
Informacje ogólne ........................................................................................................... 11
Sortowanie danych w perspektywie ............................................................................... 16
Od wie anie perspektyw ................................................................................................ 18
Opcje perspektywy ......................................................................................................... 21
ENCRYPTION ......................................................................................................... 21
SCHEMABINDING ................................................................................................ 22
CHECK OPTION ........................................................................................................... 23
Perspektywy indeksowane .............................................................................................. 25
Podsumowanie ................................................................................................................ 28
Rozdzia 2. Procedury i funkcje ........................................................................ 29
Wst p .............................................................................................................................. 29
Ogólne informacje na temat funkcji sk adowanych ........................................................ 29
Wywo ywanie funkcji a efektywno zapyta ................................................................ 32
U ywanie funkcji w ograniczeniach ............................................................................... 34
Funkcje a ograniczenie DEFAULT .......................................................................... 34
Funkcje a ograniczenie UNIQUE ............................................................................. 36
Funkcje a ograniczenie PRIMARY KEY ................................................................. 37
Funkcje a ograniczenie CHECK ............................................................................... 37
Funkcje uruchamiane dla ka dego wiersza ..................................................................... 38
Funkcje typu inline table-valued ..................................................................................... 39
Funkcje typu multi-statement table-valued ..................................................................... 41
Praktyczny przyk ad  obliczanie opó nie .................................................................. 45
Korzy ci wynikaj ce z zastosowania funkcji .................................................................. 52
Ogólne informacje o procedurach sk adowanych ........................................................... 52
Parametry wej ciowe procedury ..................................................................................... 55
Parametry wyj ciowe procedury ..................................................................................... 56
Klauzula EXECUTE AS w procedurach ........................................................................ 57
Praktyczny przyk ad  alokacja samochodów na zasoby osobowe ............................... 58
Podsumowanie ................................................................................................................ 65
6 SQL Server 2005. Zaawansowane rozwi zania biznesowe
Rozdzia 3. Wyzwalacze ................................................................................... 67
Wst p .............................................................................................................................. 67
Informacje ogólne ........................................................................................................... 67
Wyzwalacze typu AFTER .............................................................................................. 68
Tabele INSERTED i DELETED .................................................................................... 69
Identyfikacja rodzaju wyzwalacza .................................................................................. 70
Nieuruchamianie wyzwalaczy dla konkretnych instrukcji SQL ..................................... 72
CONTEXT_INFO  kontekst sesji w SQL Server 2005 .............................................. 73
Rekurencyjne i zagnie d one wywo ywanie wyzwalaczy .............................................. 75
Funkcja COLUMNS_UPDATED i predykat UPDATE
 selektywne wywo ywanie wyzwalaczy ................................................................... 78
Wyzwalacze INSTEAD OF ............................................................................................ 80
Operacje wykonywane w wyzwalaczu na wybranych wierszach ................................... 82
Wyzwalacze i perspektywy ............................................................................................ 85
Wyzwalacze uruchamiane na poziomie bazy danych ..................................................... 87
Wyzwalacze uruchamiane na poziomie serwera baz danych .......................................... 91
Podsumowanie ................................................................................................................ 92
Rozdzia 4. Dynamiczny SQL ............................................................................. 93
Wst p .............................................................................................................................. 93
Informacje ogólne ........................................................................................................... 93
EXEC ............................................................................................................................. 94
EXEC AT ....................................................................................................................... 99
sp_executesql ................................................................................................................ 100
Limit instrukcji ............................................................................................................. 102
Sp_executesql i ustawienia rodowiskowe ................................................................... 102
Dynamiczne filtry ......................................................................................................... 103
Wstrzykiwanie SQL ..................................................................................................... 105
Dynamiczny pivot danych ............................................................................................ 110
Podsumowanie .............................................................................................................. 113
Rozdzia 5. Tabele tymczasowe i zmienne tablicowe ....................................... 115
Wst p ............................................................................................................................ 115
Informacje ogólne o tabelach tymczasowych ............................................................... 115
Globalne tabele tymczasowe ........................................................................................ 118
Zmienne tablicowe ....................................................................................................... 118
Baza tempdb ................................................................................................................. 120
Wyra enia tablicowe .................................................................................................... 121
Podsumowanie .............................................................................................................. 122
Rozdzia 6. Transakcje i wyj tki w aplikacjach biznesowych ............................ 123
Wst p ............................................................................................................................ 123
Informacje ogólne o transakcjach ................................................................................. 123
Blokady ........................................................................................................................ 125
Poziomy izolacji ........................................................................................................... 129
Poziom izolacji READ UNCOMMITTED ............................................................. 130
Poziom izolacji READ COMMITTED .................................................................. 131
Poziom izolacji SNAPSHOT ................................................................................. 132
Poziom izolacji READ COMMITTED SNAPSHOT ............................................. 134
Podsumowanie poziomów izolacji ......................................................................... 134
Poziomy zapisywania ................................................................................................... 134
Zakleszczenia ............................................................................................................... 135
Obs uga b dów w aplikacjach biznesowych ................................................................ 137
Transakcje a obs uga b dów ........................................................................................ 139
Podsumowanie .............................................................................................................. 140
Spis tre ci 7
Rozdzia 7. Full-Text Search ........................................................................... 141
Wst p ............................................................................................................................ 141
Us uga Full-Text Search ............................................................................................... 142
Tworzenie, modyfikowanie i usuwanie katalogu typu Full-Text .................................. 143
Tworzenie katalogu Full-Text z wykorzystaniem kreatora .................................... 145
Tworzenie katalogu Full-Text z poziomu kodu T-SQL .......................................... 147
Modyfikowanie katalogu Full-Text ........................................................................ 148
Usuwanie katalogu Full-Text ................................................................................. 148
Tworzenie, modyfikowanie i usuwanie indeksów typu Full-Text ................................ 149
Tworzenie indeksu Full-Text z wykorzystaniem kreatora ...................................... 149
Tworzenie indeksu Full-Text z poziomu kodu T-SQL ........................................... 152
Modyfikowanie indeksu Full-Text ......................................................................... 153
Usuwanie indeksu Full-Text ................................................................................... 155
Noise Files .................................................................................................................... 155
Uzyskiwanie metadanych o katalogach i indeksach Full-Text ..................................... 155
Podstawowe wyszukiwanie informacji ......................................................................... 156
Polecenie CONTAINS ................................................................................................. 156
Wyszukiwanie podstawowe ................................................................................... 157
Wyszukiwanie z wykorzystaniem wieloznaczników .............................................. 158
Wyszukiwanie wed ug blisko ci wyst pienia s ów ................................................ 158
Wyszukiwanie z wykorzystaniem form fleksyjnych .............................................. 159
Wyszukiwanie z wykorzystaniem tezaurusa .......................................................... 159
Wyszukiwanie wed ug wagi s ów .......................................................................... 160
Polecenie FREETEXT .................................................................................................. 161
Polecenie CONTAINSTABLE ..................................................................................... 162
Polecenie FREETEXTABLE ....................................................................................... 163
Wyszukiwanie informacji w plikach PDF .................................................................... 164
Podsumowanie .............................................................................................................. 166
Rozdzia 8. XML ............................................................................................ 167
Wst p ............................................................................................................................ 167
Informacje o formacie XML ......................................................................................... 168
Przechowywanie danych XML w SQL Server 2005 .................................................... 168
Sprawdzanie poprawno ci danych XML przy u yciu schematów ................................ 170
Metody dost pu do danych XML i ich obs ugi ............................................................. 173
Metoda exist ........................................................................................................... 173
Metoda query ......................................................................................................... 174
Metoda value .......................................................................................................... 174
Metoda nodes ......................................................................................................... 175
Metoda modify ....................................................................................................... 176
Konwertowanie danych relacyjnych do formatu XML ................................................. 178
Polecenie FOR XML .............................................................................................. 178
Polecenie OPENXML ............................................................................................ 187
Podsumowanie .............................................................................................................. 190
Rozdzia 9. Database Mail .............................................................................. 191
Wst p ............................................................................................................................ 191
Aktywowanie us ugi Database Mail ............................................................................. 192
Konfigurowanie us ugi Database Mail ......................................................................... 192
Testowanie us ugi Database Mail ................................................................................. 198
Wysy anie wiadomo ci e-mail ...................................................................................... 199
Monitorowanie us ugi Database Mail ........................................................................... 203
Dodatkowe procedury zwi zane z us ug Database Mail ............................................. 205
Podsumowanie .............................................................................................................. 205
8 SQL Server 2005. Zaawansowane rozwi zania biznesowe
Rozdzia 10. Rozwi zania biznesowe ................................................................. 207
Wst p ............................................................................................................................ 207
Pobieranie elementów z hierarchii wymiaru Parent-Child ............................................ 207
Rekurencyjne pobieranie elementów wymiarów .......................................................... 215
Generowanie tabeli wymiaru Multilevel na podstawie Parent-Child ............................ 221
Alternatywne hierarchie ............................................................................................... 230
Pobieranie informacji o tygodniach z przedzia u czasowego ....................................... 233
Automatyczne wykrywanie nowych elementów wymiarów ......................................... 236
Szybki mechanizm odnajdowania zwielokrotnionych rekordów .................................. 238
Optymalizacja wstawiania danych i wi zy integralno ci .............................................. 238
Algorytm przeliczania danych ko cowych, rednich i przyrostowych
z akumulacji MTD na QTD i YTD ............................................................................ 240
Wykorzystanie j zyka MDX .................................................................................. 241
Typy zasilanych danych ......................................................................................... 242
Podsumowanie .............................................................................................................. 244
Rozdzia 11. Integracja z .NET i CLR ................................................................. 245
Wst p ............................................................................................................................ 245
Co to jest .NET i CLR? ................................................................................................ 246
Na czym polega integracja SQL Server 2005 z CLR? .................................................. 247
Kiedy u ywa obiektów CLR? ..................................................................................... 248
Schemat u ywania obiektów CLR ................................................................................ 249
W czenie obs ugi obiektów CLR w SQL Server 2005 ................................................ 250
Przyk ady obiektów CLR ............................................................................................. 251
UDF typu Scalar ..................................................................................................... 251
UDF typu Table-Value ........................................................................................... 257
User-Defined Trigger ............................................................................................. 263
User-Defined Type ................................................................................................. 267
User-Defined Aggregate ......................................................................................... 273
Zarz dzanie obiektami ASSEMBLY ............................................................................ 277
Pobieranie metadanych o obiektach ASSEMBLY ................................................. 277
Zmiana poziomu zabezpiecze ............................................................................... 279
Usuwanie obiektów ASSEMBLY .......................................................................... 280
Podsumowanie .............................................................................................................. 280
Rozdzia 12. SQL Server Integration Services .................................................... 281
Wst p ............................................................................................................................ 281
Business Intelligence Development Studio ................................................................... 282
Architektura .................................................................................................................. 282
Control Flow ................................................................................................................. 282
Kontenery ............................................................................................................... 283
Zadania ................................................................................................................... 283
Procedury przep ywu zada ................................................................................... 286
Data Flow ..................................................................................................................... 286
ród a ..................................................................................................................... 287
Transformacje ........................................................................................................ 287
Destinations ............................................................................................................ 290
Event Handlers ............................................................................................................. 290
Variables ................................................................................................................ 291
Deployment .................................................................................................................. 291
Bezpiecze stwo SSIS ................................................................................................... 296
Migracja DTS 2000 do SSIS 2005 ............................................................................... 297
Logowanie .................................................................................................................... 297
Podsumowanie .............................................................................................................. 299
Skorowidz ....................................................................................................... 301
Rozdziażÿ 8.
XML
Wstżÿp
SQL Server 2005 to pierwsza wersja serwera, w której dane XML sżÿ przechowywane
i przetwarzane faktycznie jako XML, a nie jako pochodne danych tekstowych lub bi-
narnych, jak to byżÿo w poprzednich wersjach serwera. Zażÿadowanie danych XML np.
w SQL Server 2000 byżÿo stosunkowo proste, jednak jużÿ dostżÿp do tych danych, mo-
dyfikowanie i wyszukiwanie konkretnych obiektów wymagażÿy zżÿożÿonych operacji.
SQL Server 2000 umożÿliwiażÿ wykonanie tylko dwóch poleceżÿ zwiżÿzanych z obsżÿugżÿ
formatu XML:
żÿ OPENXML  umożÿliwia zażÿadowanie dokumentu XML do pamiżÿci SQL Servera,
a nastżÿpnie utworzenie z niego zbioru rekordów relacyjnych.
żÿ FOR XML  umożÿliwia zapis danych relacyjnych, bżÿdżÿcych wynikiem zapytania
SQL do postaci XML.
Wkrótce po dacie premiery SQL Server 2000 Microsoft zrozumiażÿ, żÿe jego najnowsza
platforma bazodanowa nie wspiera obsżÿugi danych i formatu XML na tyle, by sprostażÿ
oczekiwaniom i wymaganiom biznesowych użÿytkowników i twórców aplikacji w tym
zakresie. Widzżÿc, jak ważÿnym formatem w żÿwiecie biznesowych aplikacji bazoda-
nowych stażÿ siżÿ XML, Microsoft chciażÿ za wszelkżÿ cenżÿ zwiżÿkszyżÿ jego integracjżÿ
z SQL Server 2000. Jednak po oficjalnej premierze serwera firma mogżÿa zaproponoważÿ
tylko darmowe dodatki  pakiety, które rozszerzażÿy SQL Server w tym zakresie. Pierw-
szym takim pakietem byżÿ SQLXML (XML for SQL Server), który dostarczażÿ m.in. na-
rzżÿdzia do bardzo szybkiego wczytywania danych XML. Kolejnym pakietem byżÿ MSXML
(Microsoft XML Core Services), który zawierażÿ m.in. parser XML. Jużÿ wtedy jasne byżÿo,
żÿe w kolejnej wersji SQL Server musi nastżÿpiżÿ rewolucja w podejżÿciu do formatu XML.
Integracjżÿ XML z SQL Server 2005 należÿy rozpatryważÿ w nastżÿpujżÿcych obszarach:
żÿ nowy typ danych XML,
żÿ ograniczenia w kolumnach typu XML,
168 SQL Server 2005. Zaawansowane rozwiżÿzania biznesowe
żÿ XML Schema Collection,
żÿ metody dostżÿpu i obsżÿugi danych XML.
Powyżÿszym zagadnieniom pożÿwiżÿcony zostażÿ ten rozdziażÿ ksiżÿżÿki. Zanim jednak zaj-
miemy siżÿ szczegóżÿami tych zagadnieżÿ, przypomnijmy sobie podstawowe informacje
na temat danych, plików i formatu XML.
Informacje o formacie XML
Skrót XML pochodzi od Extensible Markup Language (z ang.: rozszerzalny jżÿzyk znacz-
ników). Jżÿzyk ten sżÿużÿy do żÿatwego przechowywania i wymiany danych pomiżÿdzy
różÿnymi aplikacjami, systemami i platformami. Format XML jest obecnie wykorzysty-
wany w wielu obszarach informatycznych. Swojżÿ popularnożÿżÿ zawdziżÿcza temu, żÿe jest
stosunkowo prosty do zrozumienia oraz żÿe zapisywany jest w postaci plików teksto-
wych. Czyni go to bardzo żÿatwo edytowalnym. Niewżÿtpliwżÿ zaletżÿ formatu XML jest
takżÿe fakt, żÿe umożÿliwia on separacjżÿ warstwy danych od warstwy prezentacji. żÿatwo
zrozumieżÿ to przy porównaniu go z jżÿzykiem HTML, w którym poszczególne tagi
okreżÿlajżÿ, w jaki sposób zawarte w pliku dane sżÿ prezentowane. XML takżÿe skżÿada siżÿ
z tagów (elementów), jednak w tym przypadku opisujżÿ one przechowywane dane, nie
mówiżÿc nic na temat ich prezentacji w aplikacjach czy przeglżÿdarce. O ile w HTML
mamy do dyspozycji zdefiniowany zbiór tagów, np. Igor Kruk, w XML definiu-
jemy wżÿasne tagi na potrzeby danych, które chcemy w tym pliku zapisażÿ, np.
żÿIgor Kruk. Tak jak w HTML poszczególne tagi majżÿ swoje opcje, np.
, tak teżÿ w XML do elementów przypisywane sżÿ atrybuty, które
lepiej opisujżÿ te elementy. W poniżÿszym przykżÿadzie przechowujżÿcym dane o klien-
tach użÿyte zostażÿy atrybuty Gender (z ang.: pżÿeżÿ) i Name (z ang.: nazwisko):



...

Przechowywanie danych XML
w SQL Server 2005
W SQL Server 2005 możÿemy użÿyżÿ typu danych XML jako kolumny, zmiennej lokalnej
lub parametru. W kolumnie tego typu możÿemy przechowyważÿ cażÿe dokumenty XML
lub tylko ich fragmenty (niezawierajżÿce elementu gżÿównego, tzw. root node). Typu da-
nych XML użÿywamy w taki sam sposób jak innych typów. Poniżÿszy przykżÿad tworzy
tabelżÿ myMixes, w której bżÿdziemy przechowyważÿ dane na temat muzycznych mega-
miksów. W kolumnie Title zapisany bżÿdzie tytużÿ miksu, kolumna Tracklist bżÿdzie
zażÿ przechowyważÿa listżÿ utworów w formacie XML:
Rozdziażÿ 8. żÿ XML 169
CREATE TABLE myMixes
(MixID int IDENTITY(1,1) PRIMARY KEY,
Title varchar(255) NOT NULL,
Tracklist XML NULL)
Wstawimy teraz dane do utworzonej tabeli. W pierwszym przykżÿadzie wykorzystana
zostażÿa instrukcja INSERT, w której dane sżÿ wstawiane jako zwykżÿy tekst i zamieniane
przy użÿyciu funkcji CAST na typ XML.
INSERT INTO myMixes (Title, Tracklist)
VALUES ('Party_Beats_Vol.2_mixed_by_Raven',
CAST('


Raven - Intro
Track02  Title02
Track03  Title03
Track04  Title 04

' as XML
)
)
W drugim przykżÿadzie deklarujemy zmiennżÿ mixInfo typu XML, przypisujemy jej war-
tożÿżÿ, a nastżÿpnie wstawiamy przy użÿyciu polecenia INSERT do tabeli myMixes:
DECLARE @mixInfo XML
SET @mixInfo =
CAST('


Raven - Intro
Track02  Title02
Track03  Title03
Track04  Title 04

' as XML
)
INSERT INTO myMixes (Title, Tracklist)
VALUES ('Orange_Dance_mixed_by_Raven', @mixInfo)
W obydwu przykżÿadach dane zostażÿy jawnie skonwertowane na typ XML. Podczas tej
konwersji SQL Server wykonażÿ tylko podstawowe sprawdzenie, czy dane majżÿ format
XML, np. czy wszystkie tagi otwierajżÿce majżÿ odpowiednie tagi zamykajżÿce. Nie spraw-
dza natomiast, czy majżÿ one okreżÿlonżÿ  oczekiwanżÿ przez nas  strukturżÿ. W powyżÿ-
szych przykżÿadach struktura danych XML zakżÿadażÿa istnienie elementów Mix, Tracks
i Track z atrybutem id. Gdybyżÿmy podjżÿli próbżÿ wstawienia do tabeli myMixes danych
XML w innej strukturze, SQL Server nie zgżÿosiżÿby bżÿżÿdu, bo nie wie tak naprawdżÿ, jaka
powinna byżÿ struktura wstawianych danych. Do wprowadzania ograniczeżÿ na dane XML
sżÿużÿżÿ schematy XML.
170 SQL Server 2005. Zaawansowane rozwiżÿzania biznesowe
Sprawdzanie poprawnożÿci danych XML
przy użÿyciu schematów
Schematy XML (XML Schema Difinition) przechowywane sżÿ w plikach XSD. Zawierajżÿ
one informacje, jak powinien wyglżÿdażÿ poprawny plik XML zwiżÿzany z danym sche-
matem. Schematy XML mogżÿ byżÿ wykorzystywane przez SQL Server 2005 do kontro-
lowania poprawnożÿci danych XML podczas ich dodawania i modyfikowania. Schematy
XML sżÿ przechowywane w SQL Server 2005 jako obiekty. Listżÿ wszystkich dostżÿpnych
schematów XML otrzymamy, wykonujżÿc poniżÿsze zapytanie:
SELECT * FROM sys.XML_schema_collections
Tworzenie obiektów danych typu XML wymaga zdefiniowania w bazie danych zbiorów
schematów. Sżÿużÿy do tego instrukcja CREATE XML SCHEMA COLLECTION. Instrukcja ta
tworzy zbiór schematów mogżÿcy skżÿadażÿ siżÿ z jednego lub wiżÿcej schematów, z któ-
rych każÿdy opisuje jednżÿ przestrzeżÿ nazw. Skżÿadnia tego polecenia jest nastżÿpujżÿca:
CREATE XML SCHEMA COLLECTION [ . ]sql_identifier
AS Expression
Polecenie przyjmuje dwa parametry. Pierwszy to unikalna nazwa schematu, drugi to
jego definicja.
Listżÿ dostżÿpnych w bazie danych schematów XML znajdziemy w lokalizacji: Nazwa
bazy danych/Programmability/Types/XML Schema Collections.
Zobaczmy jeden przykżÿadowy schemat Production.ManuInstructionsSchemaCollection
z bazy AdventureWorks:
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
żÿProductModelManuInstructions"
elementFormDefault="qualified">















Rozdziażÿ 8. żÿ XML 171

























W powyżÿszym schemacie znajdziemy definicjżÿ elementu Location:

Elementowi temu przypisany jest atrybut LocationID:

Parametr use="required" oznacza, żÿe atrybut ten musi wystżÿpiżÿ w pliku XML powiżÿ-
zanym z tym schematem. Parametr type="xsd:integer" oznacza, żÿe atrybut LocationID
musi byżÿ typu cażÿkowitoliczbowego.
Spróbujmy teraz utworzyżÿ nowżÿ tabelżÿ zawierajżÿcżÿ kolumnżÿ typu XML, do której
przypiszemy omawiany schemat. Nastżÿpnie przetestujemy dziażÿanie sprawdzania po-
prawnożÿci wstawianych danych XML przez ten schemat.
Poniżÿsze polecenie T-SQL tworzy tabelżÿ o nazwie TEST skżÿadajżÿcżÿ siżÿ z dwóch kolumn:
identyfikatora wiersza i danych w formacie XML. Do tabeli przypisywany jest schemat
Production.ManuInstructionsSchemaCollection.
CREATE TABLE dbo.TEST
(
rowID int IDENTITY(1,1) PRIMARY KEY,
dane XML (Production.ManuInstructionsSchemaCollection) NULL
)
Pierwszy przykżÿad wstawia do tabeli TEST dane XML zgodne z cażÿym schematem
Production.ManuInstructionsSchemaCollection:
172 SQL Server 2005. Zaawansowane rozwiżÿzania biznesowe
INSERT INTO TEST (dane) VALUES (
CAST(
'
żÿLaborHours="0.0">

String


' AS XML))
Jeżÿli dokżÿadnie przeanalizowalibyżÿmy treżÿżÿ wczeżÿniej omawianego schematu, znależÿ-
libyżÿmy odwożÿania do wszystkich elementów z powyżÿszego przykżÿadu.
W kolejnym przykżÿadzie atrybutowi LocationID przypiszemy wartożÿżÿ tekstowżÿ  nie-
zgodnżÿ ze schematem:
INSERT INTO TEST (dane) VALUES (
CAST(
'
żÿLaborHours="0.0">

String


' AS XML))
Próba wykonania tego polecenia zakożÿczy siżÿ nastżÿpujżÿcym komunikatem o bżÿżÿdzie,
informujżÿcym, żÿe wartożÿżÿ atrybutu LocationID jest niepoprawna:
XML Validation: Invalid simple type value: 'tekst'. Location: /*:root[1]/
żÿ*:Location[1]/@*:LocationID
W kolejnym przykżÿadzie pominiemy definicjżÿ wszystkich atrybutów elementu Location.
INSERT INTO TEST (dane) VALUES (
CAST(
'


String


' AS XML))
Przy próbie wykonania tego polecenia dostaniemy nastżÿpujżÿcy komunikat:
XML Validation: Required attribute 'LocationID' is missing. Location: /*:root[1]/
żÿ*:Location[1]
Rozdziażÿ 8. żÿ XML 173
Zwróżÿmy uwagżÿ, żÿe SQL Server 2005 informuje, żÿe brak tylko atrybutu LocationID. Nie
wspomina natomiast nic o pozostażÿych atrybutach: LotSize, SetupHours, MachineHours,
LaborHours. Wynika to z tego, żÿe atrybuty te nie majżÿ w definicji schematu Production.
żÿManuInstructionsSchemaCollection parametru use="required".
Jak zauważÿyliżÿmy w powyżÿszych przykżÿadach, dziżÿki przypisywaniu schematów XML
do kolumn tego typu mamy możÿliwożÿżÿ automatycznej weryfikacji poprawnożÿci wstawia-
nych i modyfikowanych danych. Przedstawione w tym rozdziale informacje dotyczżÿce
schematów XML obejmujżÿ tylko czżÿżÿżÿ tej tematyki. Zainteresowanych tematem sche-
matów XML odsyżÿamy na stronżÿ http://www.w3.org/XML/Schema.
Metody dostżÿpu
do danych XML i ich obsżÿugi
W SQL Server 2005 dostżÿpnych jest piżÿżÿ metod umożÿliwiajżÿcych operowanie na danych
typu XML. Sżÿ to:
żÿ exist,
żÿ nodes,
żÿ query,
żÿ value,
żÿ modify.
Metody exist, nodes, query, value zalicza siżÿ do jżÿzyka XQuery, który sżÿużÿy do wyszu-
kiwania informacji w danych typu XML. Przyjrzyjmy siżÿ teraz bliżÿej poszczególnym
metodom.
Metoda exist
Metoda exist pozwala sprawdziżÿ, czy w danych XML istnieje okreżÿlony obiekt. Zwraca
ona wartożÿżÿ 1 (True), gdy obiekt znajduje siżÿ w danych XML, w przeciwnym razie
zwracana jest wartożÿżÿ 0 (False). Posżÿużÿmy siżÿ omawianym wczeżÿniej schematem XML
i sprawdżÿmy, czy w tabeli test w kolumnie dane znajduje siżÿ element Location z atry-
butem LocationID o wartożÿci 1. Poniżÿsze polecenie T-SQL wykonuje to sprawdzenie:
SELECT * FROM test
WHERE dane.exist('declare namespace t="http://schemas.microsoft.com/sqlserver/
żÿ2004/07/adventure-works/ProductModelManuInstructions";
/t:root/t:Location[@LocationID=1]') = 1
Zwróżÿmy uwagżÿ na konstrukcjżÿ tego polecenia. Metoda exist jest wykonywana na
kolumnie dane, którżÿ wczeżÿniej zdefiniowaliżÿmy jako typu XML. W nawiasie znajduje
siżÿ deklaracja przestrzeni nazw t, a nastżÿpnie odwożÿanie do danych w postaci /rodzic/
potomek[atrybut].
174 SQL Server 2005. Zaawansowane rozwiżÿzania biznesowe
Należÿy takżÿe zaznaczyżÿ, żÿe w powyżÿszym przykżÿadzie adres URL w deklaracji prze-
strzeni nazw musi znajdoważÿ siżÿ w jednym wierszu. W ksiżÿżÿce zostażÿ zżÿamany ze wzglżÿ-
du na ograniczenia w druku.
Metoda query
Wykorzystujżÿc metodżÿ query oraz poprawnie zdefiniowane zapytanie XQuery,
mamy żÿatwy dostżÿp do danych XML. Metoda pobiera fragment danych XML i zwraca je
w postaci tekstowej, a nie XML. W poniżÿszym przykżÿadzie odwożÿujemy siżÿ do bazy
AdventureWorks i tabeli Production.ProductionModel. Pobierane sżÿ elementy steps
z danych XML dla wiersza z identyfikatorem 10.
SELECT
ProductModelID,
Instructions.query('declare namespace t="http://schemas.microsoft.com/
żÿsqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/t:root/t:Location/t:step') AS Steps
FROM Production.ProductModel
WHERE ProductModelID = 10
Deklarowanie przestrzeni nazw wewnżÿtrz zapytania T-SQL wpżÿywa negatywnie na jego
czytelnożÿżÿ. Lepszym rozwiżÿzaniem jest wykorzystanie polecenia WITH NAMESPACE ().
Powyżÿsze zapytanie bżÿdzie wyglżÿdażÿ teraz nastżÿpujżÿco:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
żÿProductModelManuInstructions' AS t)
SELECT ProductModelID, Instructions.query('/t:root/t:Location/t:step') AS Steps
FROM Production.ProductModel
WHERE ProductModelID = 10
Metoda value
Metoda value sżÿużÿy do pobierania konkretnych wartożÿci elementów lub ich atrybutów
w postaci jednego z podstawowych typów danych, np. int, varchar. Metoda ta przyj-
muje dwa argumenty. Pierwszy to poprawnie skonstruowane polecenie XQuery, drugi
zażÿ to nazwa podstawowego typu danych, w którym majżÿ byżÿ zwrócone wyniki. Do-
zwolone sżÿ wszystkie typy danych oprócz XML, image, text, ntext, timestamp oraz typów
zdefiniowanych przez użÿytkownika.
Wykorzystajmy ponownie tabelżÿ Production.ProductModel z bazy AdventureWorks.
Zażÿóżÿmy, żÿe chcemy uzyskażÿ wartożÿżÿ atrybutu LotSize, drugiego elementu Location
dla wiersza z identyfikatorem 10. Zapytanie możÿe wyglżÿdażÿ tak jak poniżÿej (dla czytel-
nożÿci i przejrzystożÿci zapytania ponownie zastosowaliżÿmy polecenie WITH NAMESPACE):
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
żÿProductModelManuInstructions' AS t)
SELECT ProductModelID,
Instructions.value('(/t:root/t:Location/@LotSize)[2]',
'decimal (5,2)') AS Location
FROM Production.ProductModel
WHERE ProductModelID = 10
Rozdziażÿ 8. żÿ XML 175
W wyniku otrzymujemy jeden wiersz:
ProductModelId Location
10 1.00
Cażÿy czas pamiżÿtajmy, żÿe adres URL w deklaracji przestrzeni nazw musi znajdoważÿ
siżÿ w jednym wierszu.
Jeżÿli taka konwersja nie bżÿdzie możÿliwa, SQL Server 2005 zgżÿosi bżÿżÿd. W poniżÿszym
przykżÿadzie chcemy uzyskażÿ wartożÿżÿ elementu material i nadażÿ jej dziesiżÿtny typ danych:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
żÿProductModelManuInstructions' AS t)
SELECT ProductModelID,
Instructions.value('(/t:root/t:Location/t:step/t:material)[2]',
'decimal (5,2)') AS Location
FROM Production.ProductModel
WHERE ProductModelID = 10
Przy próbie wykonania tego polecenia dostaniemy komunikat o bżÿżÿdzie:
Error converting data type nvarchar to numeric.
Wynika to z faktu, żÿe wartożÿżÿ elementu material ma postażÿ tekstowżÿ. Zatem w po-
prawnym zapytaniu zwracanej wartożÿci możÿe byżÿ przypisany typ danych varchar. Przed-
stawia to poniżÿszy przykżÿad:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
żÿProductModelManuInstructions' AS t)
SELECT ProductModelID,
Instructions.value('(/t:root/t:Location/t:step/t:material)[2]',
'varchar(255)') AS Material
FROM Production.ProductModel
WHERE ProductModelID = 10
W wyniku otrzymujemy jeden wiersz:
ProductModelId Material
10 Acme Polish Cream
Metoda nodes
Metoda nodes umożÿliwia przeksztażÿcenie danych typu XML w dane o strukturze rela-
cyjnej. Dla każÿdego wystżÿpienia odpowiednio zdefiniowanego w zapytaniu XQuery
elementu tworzony jest oddzielny wiersz w wynikowej tabeli relacyjnej. Wróżÿmy do
przykżÿadu z tego rozdziażÿu dotyczżÿcego danych XML o megamiksach. W poniżÿszym
przykżÿadzie w pierwszej kolejnożÿci definiowane sżÿ dane w postaci XML. Nastżÿpnie
w zapytaniu T-SQL metoda nodes konwertuje każÿde wystżÿpienie elementu Track na
postażÿ relacyjnżÿ  jednego wiersza w tabeli Tab w kolumnie Col. W klauzuli SELECT
posżÿużÿymy siżÿ poznanżÿ wczeżÿniej metodżÿ value do uzyskania wartożÿci każÿdego elementu:
176 SQL Server 2005. Zaawansowane rozwiżÿzania biznesowe
DECLARE @mixInfo XML
SET @mixInfo =
CAST('


Raven - Intro
Track02  Title02
Track03  Title03
Track04  Title 04

' as XML
)
SELECT Tab.Col.value('.', 'varchar(255)') AS tracklist
FROM @mixInfo.nodes('/Mix/Tracks/Track') as Tab(Col)
Wynikiem zapytania sżÿ cztery wiersze:
tracklist
Raven - Intro
Track02  Title02
Track03  Title03
Track04  Title 04
Zwróżÿmy uwagżÿ, żÿe gdybyżÿmy nie użÿyli w klauzuli SELECT jednej z XML-owych metod
(exist, query, value) i spróbowali wykonażÿ nastżÿpujżÿce zapytanie:
SELECT * FROM @mixInfo.nodes('/Mix/Tracks/Track') as Tab(Col)
to SQL Server zwróciżÿby komunikat o bżÿżÿdzie:
The column 'Col' that was returned from the nodes() method cannot be used directly.
żÿIt can only be used with one of the four XML datatype methods exist(), nodes(),
żÿquery(), and value() or in IS NULL and IS NOT NULL checks.
Metoda modify
Metoda modify jest rozszerzeniem opracowanym przez firmżÿ Microsoft do jżÿzyka XQuery
i nazwanym XML Data Manipulation Language  XML DML. Standardowe metody
jżÿzyka XQuery, omówione wczeżÿniej w tym rozdziale, sżÿużÿżÿ tylko do pobierania informa-
cji z XML. Metoda modify, jako jedyna, umożÿliwia modyfikowanie danych XML.
XML DML udostżÿpnia nowe polecenia:
żÿ insert,
żÿ delete,
żÿ replace value of.
Do modyfikowania danych XML bżÿdziemy wykorzystyważÿ standardowe polecenie
UPDATE, w którym wykorzystamy metodżÿ modify z jednym z powyżÿszych poleceżÿ.