Spis treści:

Kategoria:HTMLSQL ServerXML


Wykorzystanie SQL Server do tworzenia tabeli HTML

SQL Server, HTML i XML

Współczesne biznesowe metody zarządzania mają to do siebie, że wyjątkowo często posługują się pomocami w postaci wykresów i tabel. Z przykrością stwierdzam, że zmniejsza się rola rzeczywistej pracy, a zwiększa nakład na wszelkiego rodzaju techniki, które z założenia mają tę pracę przyspieszyć. Mniej istotne są wyniki pracy, a bardziej istotne słupki - jeżeli słupki się zgadzają, wszystko jest w porządku.

Nie będę się dziś zajmował wykresami. Zajmę się tabelami. Jak wiemy, SQL Server zwraca wyniki w postaci, którą można bez odrobiny strachu nazwać tabelą. Aby tę tabelę zaprezentować zarządowi, trzeba ją nieco przerobić. Technik obróbki surowych wyników SQL jest dużo. Dziś zajmę się przedstawieniem ich w postaci HTML-owej. Taka tabela składa się ze znaczników table, tr i td. Niektóre tabele mają nagłówki kolumn, które oznaczamy za pomocą atrybutu th.

Nietrudno jednocześnie przyznać, że struktura tabeli HTML jest zgodna ze strukturą prostego pliku XML. Nie poruszałem jeszcze tematu XML w ramach SQL Server. Z tym większą przyjemnością pokażę więc, jak można wykorzystać funkcje i instrukcje XML w SQL Server do rozwiązania zadania postawionego w temacie.

Zwrócenie wyników SQL w postaci tabeli HTML

Zanim przejdę do wyjaśnienia, przedstawmy sobie prosty przykład. Stwórzmy tabelę, uzupełnijmy ją danymi, tak jak w poniższym przykładzie:

CREATE TABLE dbo.Pracownik
(
ID int IDENTITY(1,1),
Imie nvarchar(32),
Nazwisko nvarchar(32),
Pesel varchar(11)
)

INSERT INTO dbo.Pracownik
VALUES
('Karol''Karaczan''67021212575'),
('Marcin''Kanibal''66010184728'),
('Teofil''Rumianek''59050739586')

Myślę, że nie trzeba tego szczegółowo opisywać. Przyjrzyjmy się teraz dwóm zapytaniom i porównajmy je:

--zwykłe pobranie rekordów
SELECT Nazwisko+', '+Imie AS ImieNazwisko, Pesel
FROM dbo.Pracownik

--pobranie rekordów w postaci XML
SELECT Nazwisko+', '+Imie AS ImieNazwisko, Pesel
FROM dbo.Pracownik
FOR XML PATH

Pierwsze zapytanie jest tak proste, że nie wymaga komentarza. Drugie natomiast, zakończone słowami kluczowymi FOR XML PATH sprawia, że wynik zostanie zwrócony w postaci XML. Ten wynikowy XML ma następującą postać:

<row>
  <NazwiskoImie>Karaczan, Karol</NazwiskoImie>
  <Pesel>67021212575</Pesel>
</row>
<row>
  <NazwiskoImie>Kanibal, Marcin</NazwiskoImie>
  <Pesel>66010184728</Pesel>
</row>
<row>
  <NazwiskoImie>Rumianek, Teofil</NazwiskoImie>
  <Pesel>59050739586</Pesel>
</row>

Wynik przypomina trochę XML, ale nim nie jest. Brak nam elementu głównego. Po drugie, każdy rekord w tabeli HTML powinien się znajdować w znaczniku tr, a nie row. Po trzecie, każda komórka powinna być oznaczona znacznikiem td. Aby zmienić znacznik row, który jest wstawiany domyślnie, należy po słowach kluczowych FOR XML PATH umieścić nazwę znacznika wstawianego dla każdego rekordu. Wartość podajemy w nawiasie, w postaci tekstowej. Aby natomiast dodać element nadrzędny całego pliku XML, po całym zapytaniu wstawiamy słowo kluczowe ROOT z paremetrem określającym nazwę głównego węzła XML. Tak jak wcześniej nazwę podajemy w nawiasie w postaci tekstowej. Przyjrzyjmy się zatem instrukcji SELECT po wprowadzeniu tych dwóch zmian i wynikowi zwracanemu z zapytania:

SELECT Nazwisko+', '+Imie AS NazwiskoImie, Pesel
FROM dbo.Pracownik
FOR XML PATH ('tr'), ROOT('table')

Wynik działania powyższej instrukcji będzie następujący:

<table>
  <tr>
    <NazwiskoImie>Karaczan, Karol</NazwiskoImie>
    <Pesel>67021212575</Pesel>
  </tr>
  <tr>
    <NazwiskoImie>Kanibal, Marcin</NazwiskoImie>
    <Pesel>66010184728</Pesel>
  </tr>
  <tr>
    <NazwiskoImie>Rumianek, Teofil</NazwiskoImie>
    <Pesel>59050739586</Pesel>
  </tr>
</table>

Zadanie prawie skończone. Mamy plik XML, znacznik table, w środku znaczniki tr. Brakuje nam w zasadzie tylko znaczników td dla każdej komórki. Jak to zrobić?

Znaczniki td w tabeli HTML

Przyznam się, że ten etap jest nieco bardziej skomplikowany. Wydaje się, że sprawa już jest oczywista. Najczęstsze próby dokończenia kierują nas w zła stronę. Spośród wielu błędnych prób najczęściej pojawiają się takie oto rozwiązania:

--Takie zapytanie nie zadziała
--Każdy rekord zostanie zwrócony w postaci
--<td>Karaczan, Karol67021212575</td>
SELECT Nazwisko+', '+Imie AS td, Pesel AS td
FROM dbo.Pracownik
FOR XML PATH ('tr'), ROOT('table')

--Takie zapytanie rónież nie zadziała
--Każdy rekord zostanie zwrócony w postaci
--<td>Karaczan, Karol&lt;/td&gt;&lt;td&gt;67021212575</td>
SELECT Nazwisko+', '+Imie + '</td><td>' + Pesel AS td
FROM dbo.Pracownik
FOR XML PATH ('tr'), ROOT('table')

Prawidłowe rozwiązanie jest nieco inne. Aby SQL Server tworząc elementy td nie zamieniał znaków specjalnych XML, należy wskazać, że każda z komórek jest również fragmentem XML. W Przeciwnym razie znak mniejszości < zostanie zamieniony na &lt;, znak większości > na &gt; i tak dalej. Co zatem trzeba zrobić? Skrypt mógłby wyglądać następująco:

Inne rozwiązania oczywiście istnieją, ale wymagają poznania dodatkowych technik. Alternatywne rozwiązanie zaprezentowane jest tutaj: Tworzenie tabeli HTML przy pomocy SQL Server - XQuery. Zachęcam do dzielenia się swoimi rozwiązaniami w komentarzu.
SELECT
(SELECT Nazwisko+', '+Imie AS 'td' FOR XML PATH(''), TYPE),
(SELECT Pesel AS 'td' FOR XML PATH(''), TYPE)
FROM dbo.Pracownik
FOR XML PATH ('tr'), ROOT('table')

Przyjrzyjmy się teraz wynikowi zapytania zaprezentowanego powyżej:

<table>
  <tr>
    <td>Karaczan, Karol</td>
    <td>67021212575</td>
  </tr>
  <tr>
    <td>Kanibal, Marcin</td>
    <td>66010184728</td>
  </tr>
  <tr>
    <td>Rumianek, Teofil</td>
    <td>59050739586</td>
  </tr>
</table>

Mamy to, czego oczekiwaliśmy. Znacznik table, w nim znaczniki tr, a w tych znacznikach tr znaczniki td z danymi z bazy. Wszystko za pomocą samego SQL Server. Drobnego wyjaśnienia wymagają wewnętrzne instrukcje SELECT. Jest to instrukcja podobna do zewnętrznej instrukcji SELECT. Warto zwrócić uwagę na obecność słów kluczowych FOR XML. W parametrze PATH nie potrzebujemy żadnych dodatkowych znaczników, więc podajemy pusty łańcuch znaków (wcześniej było to tr, bo chcieliśmy znacznik tr dla każdego rekordu przetwarzanego przez instrukcję). Nowością jest słowo kluczowe TYPE, które nakazuje SQL Server interpretować całą wartość jako typ XML. Trzeba wiedzieć, że domyślnie każdy wynik w postaci XML traktowany jest tak samo jak nvarchar(MAX). Konsekwencja takiego traktowania jest następująca: jeżeli zewnętrzna instrukcja SELECT napotka wartość tekstową, zamieni wszystkie znaki specjalne XML na ich zakodowane odpowiedniki (< na &lt;, > na &gt;). Jeżeli zastosujemy słowo kluczowe TYPE, znaki specjalne nie będą kodowane. A przecież o to nam chodzi.

SQL Server i nagłówki tabel HTML - th

Powszechną praktyką jest oznaczanie nagłówków tabel HTML za pomocą znaczników th. Propozycja twórców SQL Server jest następująca: należy zastosować instrukcję UNION ALL. Przyjrzyjmy się możliwemu rozwiązaniu zaprezentowanemu na listingu poniżej:

Warto zwrócić uwagę na różnicę pomiędzy UNION i UNION ALL - nie każdy o niej pamięta. Przejdź do artykułu: Różnica między UNION ALL i UNION w SQL Server.
SELECT
(SELECT 'Nazwisko, Imię' AS 'th' FOR XML PATH(''), TYPE),
(SELECT 'PESEL' AS 'th' FOR XML PATH(''), TYPE)
UNION ALL
SELECT
(SELECT Nazwisko+', '+Imie AS 'td' FOR XML PATH(''), TYPE),
(SELECT Pesel AS 'td' FOR XML PATH(''), TYPE)
FROM dbo.Pracownik
FOR XML PATH ('tr'), ROOT('table')

Zapytanie się trochę rozrasta, ale przy odrobinie praktyki takie tabelki można robić niemal automatycznie. Słowo automatycznie skłania do tego, aby stworzyć jakiś automat. Niech komputer robi za nas! Na początek polecam jednak pisanie takich instrukcji samodzielnie, aż nam wejdzie w krew. Popatrzmy jeszcze na wygenerowany plik XML i na samą tabelę:

<table>
  <tr>
    <th>Nazwisko, Imię</th>
    <th>PESEL</th>
  </tr>
  <tr>
    <td>Karaczan, Karol</td>
    <td>67021212575</td>
  </tr>
  <tr>
    <td>Kanibal, Marcin</td>
    <td>66010184728</td>
  </tr>
  <tr>
    <td>Rumianek, Teofil</td>
    <td>59050739586</td>
  </tr>
</table>

Uwaga! Tabela zawiera styl, nie jest dokładnie taka, jak wygenerowana przez skrypt SQL.

Nazwisko, Imię PESEL
Karaczan, Karol 67021212575
Kanibal, Marcin 66010184728
Rumianek, Teofil 59050739586

Numerowanie rekordów

Czas na coś bardziej rozbudowanego, coś, co zostało zaproponowane w komentarzu. Jak dodać kolumnę, której nie ma w tabeli? Rozwiązanie jest następujące: zamiast odwoływać się do fizycznej tabeli, należy odwołać się do wyrażenia tablicowego. Takim wyrażeniem tablicowym może być dowolna tabela pochodna lub wspólne wyrażenie tablicowe. Dla niektórych może to brzmieć groźnie, ale w rzeczywistości jest dośćproste. Tabela pochodna powstaje w wyniku wykonania instrukcji SELECT. Wniosek: należy wykonać SELECT z instrukcji SELECT. Popatrzmy na przykład zaprezentowany na poniższym listingu:

SELECT
(SELECT LP AS 'td' FOR XML PATH(''), TYPE),
(SELECT Nazwisko+', '+Imie AS 'td' FOR XML PATH(''), TYPE),
(SELECT Pesel AS 'td' FOR XML PATH(''), TYPE)
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY Nazwisko) LP,
  Nazwisko, Imie, Pesel
  FROM dbo.Pracownik
AS WyrazenieTablicowe
FOR XML PATH ('tr'), ROOT('table')

Do ponumerowania rekordów użyto funkcji ROW_NUMBER, a same rekordy uporządkowane są alfabetycznie według nazwiska. Przyjrzyjmy się jeszcze zwracanej tabeli w postaci kodu HTML:

<table>
  <tr>
    <td>1</td>
    <td>Kanibal, Marcin</td>
    <td>66010184728</td>
  </tr>
  <tr>
    <td>2</td>
    <td>Karaczan, Karol</td>
    <td>67021212575</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Rumianek, Teofil</td>
    <td>59050739586</td>
  </tr>
</table>

Jak już wcześniej wspomniałem, wyrażeniem tablicowym może być (prawie) dowolna instrukcja SELECT. Dozwolone są między innymi operacje złączenia, grupowania, agregacje i wiele, wiele innych. O samym numerowaniu więcej można przeczytać tutaj: Numerowanie rekordów zwracanych z SQL Server.

Podsumowanie

Generowanie tabel HTML z poziomu SQL Server daje nam ogromne możliwości, a przede wszystkim ogromną elastyczność. Umieszczenie skryptu w procedurze składowanej pozwala na modyfikacje tabeli na stronie internetowej bez zmiany w samym kodzie aplikacji, bez konieczności restartu serwera. Można pójść o krok dalej i umieścić wszystkie skrypty prezentujące dane w postaci tabel w jednej przestrzeni nazw SQL Server (tzw. schema) i dynamiczne umieszczać je na liście możliwych zestawień. Dodanie nowej tabeli będzie się sprowadzało tylko i wyłącznie do dodania kolejnej procedury. Jest to z pewnością rozwiązanie szybsze w przypadku takiego szefa, który często zmienia decyzje o tym, co ma być wyświetlane w tabelach i co chwilę zgłasza zapotrzebowanie na nowe raporty.

Kategoria:HTMLSQL ServerXML

, 2013-12-20

Komentarze:

rkubien (2012-10-19 21:01:40)
Fajne. A da się dodać kolumnę l.p. - numerowanie wierszy? Kombinuję, kombinuję i ni czorta nie wychodzi...
PD (2012-10-24 12:42:12)
Dziękuję za sugestię. Uzupełniłem wpis o dodatkowy podpunkt związany z numerowaniem wierszy.
Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za rozjasnienie zagadnienia upsert. wlasnie sie ucze programowania :).
Co się stanie gdy spróbuję wyszukać:
SELECT * FROM NV_Airport WHERE Code='SVO'
SELECT * FROM V_Airport WHERE Code=N'SVO'
(odwrotnie są te N-ki)
Będzie konwersja czy nie znajdzie żadnego rekordu?