Przyznaję, że kiedyś omijałem temat XML w T‑SQL szerokim łukiem. Jakoś mi to nie pasowało. Tabele są przecież bardzo wygodne, mają czytelne wiersze, pola, typy a XML to taka „bryła” tekstu, która nie jest ani ładna ani zapewne wygodna w obróbce. Obecnie szerokim łukiem omijam takie myślenie o XML 🙂
Mój pierwszy kontakt z wykorzystaniem metod XML w T‑SQL wynikał o dziwo bardziej z poczucia estetyki niż zwykłej potrzeby. Miałem przygotowane zapytania, które generowały interesujące mnie zestawienia (każdy administrator ma swoje ulubione zestawienia, które z mniejszym lub większym entuzjazmem przegląda przynajmniej raz dziennie 😉 ) i przeglądając je któregoś dnia naszła mnie pewna myśl – a dlaczego nie wysyłać sobie tego na maila?
Aby nie zajmować tylko samym opowiadaniem napiszmy sobie jakiś przykładowy batch którym moglibyśmy się „pobawić”. Przygotujmy sobie np. zestawienie wszystkich typów danych zapisanych w sys.types zaczynających się na literę D, gdzie intersować nas będzie nazwa typu, jego identyfikator w systemie oraz maksymalna długość
select name, system_type_id, max_length from sys.types where name like 'd%';
Takie zestawienie moglibyśmy zapisać jako tekst np. w następujący sposób
declare @typy nvarchar(max); select @typy = concat( coalesce(@typy + char(13), ''), name, ', ', system_type_id, ', ', max_length) from sys.types where name like 'd%'; declare @zestawienie nvarchar(max) = N'Zestawienie typów danych na literę D' + char(13) + N'----------------------------------------' + char(13) + @typy + char(13) + N'----------------------------------------'; select @zestawienie;
W efekcie możemy uzyskać np. taki rezultat (aby zobaczyć efekt z przechodzeniem do nowych linii należy wyświelić wynik jako tekst a nie grid).
Zestawienie typów danych na literę D ---------------------------------------- date, 40, 3 datetime2, 42, 8 datetimeoffset, 43, 10 datetime, 61, 8 decimal, 106, 17 ----------------------------------------
Takie coś możemy oczywiście zapakować sobie do maila i wysłać jako zestawienie. Ja jednak odczuwałem estetyczny niedosyt – zapragnąłem opakować sobie te dane w ładną tabelkę, a skoro mail jest właściwie tekstem to pomyślałem, że najłatwiej można będzie to osiągnąć przy pomocy kodu HTML. Moje pierwsze podejście do problemu można streścić mniej więcej tak
declare @typy nvarchar(max); select @typy = concat( @typy, '<tr>', '<td>', name, '</td>', '<td>', system_type_id, '</td>', '<td>', max_length, '</td>', '</tr>', char(13)) from sys.types where name like 'd%' order by name asc; declare @zestawienie nvarchar(max) = N'Zestawienie typów danych na literę D' + char(13) + N'<table>' + char(13) + N'<tr>' + N'<th>Nazwa</th><th>system id</th><th>max długość</th>' + N'</tr>' + char(13) + @typy + N'</table>'; select @zestawienie;
Efekt działania nie jest trudny do przewidzenia
Zestawienie typów danych na literę D <table> <tr><th>Nazwa</th><th>system id</th><th>max długość</th></tr> <tr><td>date</td><td>40</td><td>3</td></tr> <tr><td>datetime</td><td>61</td><td>8</td></tr> <tr><td>datetime2</td><td>42</td><td>8</td></tr> <tr><td>datetimeoffset</td><td>43</td><td>10</td></tr> <tr><td>decimal</td><td>106</td><td>17</td></tr> </table>
Jak widać rezultat jest co prawda całkiem przyzwoity ale sposób jego osiągnięcia nie przypadł mi specjalnie do gustu. Ktoś mógłby co prawda pokusić się o pytanie – skoro działa to co chcesz tam jeszcze zmieniać? Mi to jednak nie pasowało i zacząłem szukać innego rozwiązania. Wtedy właśnie „wujek Google” zasugerował mi abym zainteresował się funkcjami XML w T‑SQL, których istnienie do tamtej chwili wypierałem ze swojej świadomości. W końcu jednak doszło do naszego pierwszego starcia.
Sama konstrukcja XML budowanego z wyniku zapytania SQL nie jest właściwie skomplikowana. Zasadniczo wyróżnia się formułka FOR XML z różnymi opcjami gdzieś przy końcu zapytania (za wyjątkiem trybu EXPLICIT ponieważ w tym przypadku zapytanie wybierające dane musi być zbudowane wg pewnych reguł). Wspomniana klauzula FOR XML pracuje w czterech trybach: RAW, AUTO, EXPLICIT oraz PATH. Wszystkie cztery tryby wraz z pozostałymi argumentami klauzuli są opisane na stronach MSDN. Zachęcam do przestudiowania wszystkich jak i do samodzielnej „zabawy” – w końcu praktyka jest o wiele cenniejsza niż teoria 😉
To co będzie nam potrzebne to przygotowanie dwóch elementów tabeli. Jedną z nich jest nagłówek tabeli zawierający nazwy kolumn, drugą natomiast będą wiersze z danymi. Na pierwszy rzut okna może się to wydawać skomplikowane ale zapewniam, że da się to wszystko zrobić w „jednym zapytaniu” (nie obiecuję jednak, że będzie ono proste 😉 ). Złożoność polega na tym, iż musimy „opakować” w znaczniki TR dwa rodzaje elementów – TH oraz TD. Do tego to wszystko musimy jeszcze ubierać w znacznik TABLE. Intuicja podpowiada tutaj, że nasze zapytanie będzie składało się z trzech części, z czego jedna będzie tak jakby obejmowała dwie pozostałe. Spróbujmy zatem rozbić nasz problem na trzy mniejsze.
Na pierwszy ogień weźmy sekcję nagłówka. Przyjmijmy, że nasze kolumny nazywają się odpowiednio Nazwa, System ID oraz Max Długość. Spróbujmy teraz przygotować nasz wiersz nagłówka przy pomocy dostępnych trybów klauzuli FOR XML.
Poniżej treść zapytania oparta o klauzulę FOR XML RAW. Tryb RAW wymaga nazwania kolumn – jak widać wszystkim nadałem tę samą nazwę TH (co jest dozwolone). Kluczową sprawą jest tutaj wykorzystanie atrybutu ELEMENTS, bez którego nie mógłbym nazwać wszystkich kolumn tak samo (bez tego atrybutu tryb RAW próbowałby potraktować zwracane kolumny jako atrybuty elementu XML – z tym atrybutem każde pole zostaje wydzielone do osobnego taga zgodnego ze swoją nazwą). Argument podany w nawiasie przy RAW to określenie nazwy elementu XML odzwierciedlającego cały wiersz.
select 'Nazwa' as th,
'System ID' as th,
'Max Długość' as th
for xml raw('tr'), elements;
Uzyskanie takiego samego efektu przy pomocy klauzuli FOR XML PATH również jest możliwe ale wygląda nieco inaczej. Podobnie jak w trybie RAW w nawiasie podajemy nazwę elementu obejmującego cały pojedynczy wiersz zwracany w zapytaniu (w tym przypadku również będzie to TR). Podobnie jak poprzednio nazwałem również kolumny (nazwa zgodna z tagiem jaki chcemy uzyskać). Widać jednak, iż w odróżnieniu od poprzedniej wersji brakuje tutaj atrybutu ELEMENTS oraz pojawiły się dodatkowe „puste” kolumny. Ich obecność wynika z faktu, iż tryb PATH stara się zwrócić wszystkie pola w jednym ciągu – jeśli będą one miały taką samą nazwę to zostaną w ramach tagu połączone w jeden łańcuch znaków. Aby do tego nie doszło wprowadziłem puste i nienazwane pola jako separatory co pozwoli nam uzyskać pożądany efekt.
select 'Nazwa' as th, '',
'System ID' as th, '',
'Max Długość' as th
for xml path('tr');
Wykorzystanie klauzuli FOR XML AUTO jest trochę „niezgrabne” gdyż wymaga w tej sytuacji zastosowania niezbyt ładnych konstrukcji. Przede wszystkim tryb ten wymaga co najmniej jednej nazwanej tabeli, której nazwa (lub alias) posłuży za nazwę elementu XMl dla każdego wiersza (odpowiednik argumentu z nawiasu przy trybach RAW oraz PATH). Ponieważ nie mamy tutaj zbytnio żadnej tabeli do wykorzystania dlatego zdecydowałem się na jej zbudowanej w oparciu o polecenie SELECT, które musi zwracać co najmniej jedno nazwane pole. Podobnie jak w przypadku trybu RAW nadanie identycznych nazw kolumnom wiąże się wykorzystaniem atrybutu ELEMENTS (ogólnie tryb AUTO jest bardzo podobny do RAW). Zniknęły również puste pola, które widzieliśmy w zapytaniu z PATH.
select 'Nazwa' as th, 'System ID' as th, 'Max Długość' as th from (select null as n) as tr for xml auto, elements;
Pozostała nam jeszcze do wypróbowania klauzula FOR XML EXPLICIT, której konstrukcja do najłatwiejszych nie należy. Istotne w tym trybie jest odpowiednie przygotowanie pól. Dwa z nich – TAG oraz PARENT, są wymagane do poprawnego zbudowania struktury. Pozostałe pola natomiast definiują dane przekazywane do struktury XML.
select 1 as Tag, null as Parent, 'Nazwa' as [tr!1!th!element], 'System ID' as [tr!1!th!element], 'Max Długość' as [tr!1!th!element] for xml explicit;
Dla ścisłości przypomnę, iż wszystkie cztery powyższe metody zwracają ten sam rezultat, którym jest następujący kod HTML (wcięcia dodałem ręcznie dla lepszej czytelności)
<tr>
<th>Nazwa</th>
<th>System ID</th>
<th>Max Długość</th>
</tr>
Budowanie części z danymi będzie wyglądało analogicznie jak w powyższych przypadkach. Proponuję zatem od razu przejść do trzeciego etapu, czyli obudowania obu części (nagłówka i danych) tagiem TABLE. Oczywiście można to zrealizować poprzez połączenie łańcuchów znaków ale mi bardziej podoba się zbudowanie całej struktury w oparciu o metody XML. Podobnie jak wyżej podam przykładową realizację dla każdego z trybów klauzuli FOR XML.
W przypadku klauzuli FOR XML RAW całkowite zapytanie będzie miało postać
select
( select 'Nazwa' as th,
'System ID' as th,
'Max Długość' as th
for xml raw('tr'), elements),
( select name as td,
system_type_id as td,
max_length as td
from sys.types
where name like 'd%'
order by name asc
for xml raw('tr'), elements)
for xml raw('table'), elements;
Jeśli przyjrzymy się wynikowi tego zapytania to naszym oczom ukaże się co prawda kompletna struktura tabelki ale wszystkie tagi znajdujące się wewnątrz znacznika TABLE zamiast znaków < oraz > będą miały kody < oraz >. Aby wyeliminować takie zachowanie musimy przekazać do klauzul budujących XML informacje, iż zwracane wyniki mają być typu XML. Aby to osiągnąć musimy dodać atrybut TYPE do klauzul FOR XML. W tym przypadku nasze zapytanie musimy zmodyfikować w następujący sposób
select
( select 'Nazwa' as th,
'System ID' as th,
'Max Długość' as th
for xml raw('tr'), elements, type),
( select name as td,
system_type_id as td,
max_length as td
from sys.types
where name like 'd%'
order by name asc
for xml raw('tr'), elements, type)
for xml raw('table'), elements;
Jak widać wystarczy dodać TYPE tylko do zapytań „wewnętrznych” aby efekt był w pełni zadowalający. Do pełni „szczęścia brakuje nam już tylko zapamiętania wynikowego XML jako łańcucha znaków (w tym celu należy wykonać rzutowanie typu XML na typ zgodny ze zmienną @typy). W tym celu zmodyfikujemy nasze zapytanie w następujący sposób (w kolejnych przykładach będę podawać od razu podobną postać)
declare @typy nvarchar(max);
select @typy = cast((
select
( select 'Nazwa' as th,
'System ID' as th,
'Max Długość' as th
for xml raw('tr'), elements, type),
( select name as td,
system_type_id as td,
max_length as td
from sys.types
where name like 'd%'
order by name asc
for xml raw('tr'), elements, type)
for xml raw('table'), elements)
as nvarchar(max));
select @typy;
W przypadku trybu FOR XML PATH nasze zapytanie będzie mogło przyjąć postać
declare @typy nvarchar(max);
select @typy = cast((
select
( select 'Nazwa' as th, '',
'System ID' as th, '',
'Max Długość' as th
for xml path('tr'), type),
( select name as td, '',
system_type_id as td, '',
max_length as td
from sys.types
where name like 'd%'
order by name asc
for xml path('tr'), type)
for xml path('table'))
as nvarchar(max));
select @typy;
W trybie FOR XML AUTO nasze zapytanie może wyglądać np. tak
declare @typy nvarchar(max); select @typy = cast(( select ( select 'Nazwa' as th, 'System ID' as th, 'Max Długość' as th from (select null as n) as tr for xml auto, elements, type), ( select name as td, system_type_id as td, max_length as td from sys.types as tr where name like 'd%' order by name asc for xml auto, elements, type) from (select null as n) as [table] for xml auto) as nvarchar(max)); select @typy;
Pozostał nam jeszcze tryb FOR XML EXPLICIT, przy którym niestety trzeba się trochę „nagimnastykować”. Problem stanowią tutaj powtarzające się nazwy kolumn. W celu rozwiązania owego problemu należy tak jakby osobno przygotować tabelę z danymi i ją dopiero obudować „strukturą” XML. W efekcie nasze zapytanie dla tego trybu może wyglądać następująco
declare @typy nvarchar(max); select @typy = cast(( select Tag, Parent, [table] as [table!1], th1 as [tr!2!th!element], th2 as [tr!2!th!element], th3 as [tr!2!th!element], td1 as [tr!3!td!element], td2 as [tr!3!td!element], td3 as [tr!3!td!element] from ( select 1 as Tag, null as Parent, null as [table], null as th1, null as th2, null as th3, null as td1, null as td2, null as td3 union all select 2 as Tag, 1 as Parent, null, 'Nazwa', 'System ID', 'Max Długość', null, null, null union all select 3 as Tag, 1 as Parent, null, null, null, null, [name], cast(system_type_id as varchar(max)), cast(max_length as varchar(max)) from sys.types where name like 'd%') as dane for xml explicit, type) as nvarchar(max)); select @typy;
Niezależnie od przyjętej metody mając zapisany kod HTML w zmiennej @typy możemy ostatecznie wykorzystać poniższy kod do przygotowania zestawienia
declare @zestawienie nvarchar(max) = N'Zestawienie typów danych na literę D' + char(13) + N'----------------------------------------' + char(13) + @typy + char(13) + N'----------------------------------------'; select @zestawienie;
Nie chcę tutaj wybierać żadnej z powyższych metod jako lepszej od innych. Każda z nich ma swoje plusy i minusy i może okazać się lepsza od innych w konkretnych przypadkach. Mam jednak nadzieję, że udało mi się przybliżyć odrobinę temat przygotowania XML przy pomocy T‑SQL i pokazać, że przygotowywanie XML wcale nie jest takie straszne 🙂
