Spis treści:

Kategoria:HTMLSQL ServerXMLXQuery


SQL Server i łączenie danych XML z XQuery

Łączenie dwóch plików XML na podstawie klucza

Niewiele osób zdaje sobie sprawę z rzeczywistych możliwości SQL Server. Prawdą jest, że większość zastosowań sprowadza się do wykonywania prostych operacji SELECT, INSERT, DELETE i UPDATE. Dziś będzie coś nieco bardziej skomplikowanego. Przedstawiony skrypt na podstawie przekazanych danych w postaci XML oraz na podstawie wzorca dokumentu XHTML wytworzy pełny dokument, który przyjmie postać gotowej strony HTML.

Dane wejściowe

Aby lepiej zrozumieć całą koncepcję, przyjrzyjmy się przykładowemu wzorcowi:

<html>
<head><title></title></head>
<body>
<h1><span id="Head"></span></h1>
<div>Imię: <span id="FName"></span></div>
<div>Nazwisko: <span id="LName"></span></div>
</body>
</html>

To będzie nasz wzorzec dokumentu. Łatwo wyobrazić sobie tabelę z takimi wzorcami, służącą do generowania różnych typów dokumentów. Ja jednak poprzestanę na tym jednym. We wzorcu warto zwrócić uwagę na kilka elementów. Po pierwsze, pusty jest znacznik title. W to miejsce wrzucimy nazwę dokumentu, bo w HTML jest to całkiem naturalne. Po drugie - pozostałe pola identyfikowane będą na podstawie umieszczonych we wzorcu identyfikatorów HTML, czyli znaczników span z atrybutem id. Wydaje się to wszystko proste.

Skąd weźmiemy parametry? Jakie dane tam wstawimy. Ja zastosowałem następujące rozwiązanie: tytuł dokumentu, jako wartość pewna i zawsze obecna, przekazany zostanie w postaci jednego z argumentów, natomiast pozostałe pola przekazane zostaną w postaci dokumentu XML. Dlaczego tak? Przede wszystkim dlatego, że nie znamy dokładnej liczby parametrów ani ich typu. Wyklucza to lub bardzo mocno ogranicza tradycyjne parametry oraz parametry przekazywane w postaci tablic.

Dzięki takiemu rozwiązaniu możliwe staje się napisanie uniwersalnej metody, która przyjmie dwa prarametry: tytuł (xml lub nvarchar) oraz dane (xml).

Możliwości są ogromne. Na potrzeby przykładu posłużę się następującą instrukcją SQL, która nam takie dane wygeneruje:

DECLARE @arguments XML =
  (SELECT 'Nagłówek' Head,
          'Przykładowe imię' FName,
          'Przykładowe nazwisko' LName
   FOR XML RAW('Data'))

Wynik działania instrukcji SELECT ... FOR XML RAW zostanie zapisany w zmiennej @arguments, a jej zawartość będzie taka, jak poniżej:

<Data Head="Nagłówek"
      FName="Przykładowe imię"
      LName="Przykładowe nazwisko"/>

Formatowanie pliku XML z danymi nie ma znaczenia - nowe linie zostały dodane tylko w celu zwiększenia czytelności. Jak to teraz połączyć?

Modyfikowanie pliku XML w SQL Server

Istnieje kilka metod modyfikacji wartości typu XML. Najwygodniejsza z nich to użycie XQuery i instrukcji MODIFY. Kluczowe jest tutaj odpowiednie wskazanie elementu HTML. Popatrzmy na przykład:

DECLARE @html xml=N'
<html>
<head><title></title></head>
<body>
<h1><span id="Head"></span></h1>
<div>Imię: <span id="FName"></span></div>
<div>Nazwisko: <span id="LName"></span></div>
</body>
</html>'


DECLARE @title xml=N'Tytuł'

SET @html.modify('insert sql:variable("@title") into (/html/head/title)[1]')

SELECT @html

Myślę, że nie trzeba specjalnie opisywać składni. Aby przekazać do instrukcji XQuery zmienną SQL, należy skorzystać z funkcji sql:variable. Funkcja stanowi pewien pomost między dwoma różnymi kontekstami (SQL i XQuery). Ścieżka (/html/head/title)[1] to wskazanie właściwego elementu. Jak to w HTMLu bywa, nadrzędny element to html, potem mamy head i title. Zaskakująca może być cyfra 1 w nawiasie prostokątnym i trochę o niej napiszę. Oznacza ona pierwszy węzeł we wskazanej ścieżce. Instrukcja insert into XQuery wymaga pojedynczego węzła. Jeżeli takiego nie dostarczymy, otrzymamy następujący komunikat:

Msg 2226, Level 16, State 1, Line 13
XQuery [modify()]: The target of 'insert' must be a single node, found 'element(title,xdt:untyped) *'

XQuery rozróżnia przypadki, w których węzłów może być wiele - poinformuje nas o tym, więc nie ma problemu. Pamiętajmy, że element może mieć w sobie wiele innych elementów. Ta jedynka wskazuje: weź jako ten nasz pojedynczy węzeł pierwszy element z brzegu. HTML teoretycznie powinien mieć tylko jeden element title, ale XQuery działa w świecie XMLa. Tam nikt nam nie zabroni wstawienia kilku elementów title. XQuery nic o zawartości nie wie. To my wiemy i musimy napisać właściwą instrukcję.

Co zrobić z pozostałymi parametrami? Generalnie to samo co z parametrem określającym tytuł okna. Większy wysiłek spowodowany jest tylko tym, że dane zaszyte są w innym pliku XML.

Pobieranie danych z pliku XML

Zanim przejdziemy do złączenia wzorca z danymi pokażę, w jaki sposób dane mogą być pobierane. Nic nie stoi na przeszkodzie, aby użyć instrukcji OPENXML, ale skoro korzystamy z XQuery, to korzystajmy do końca. W XQuery dla SQL Server do pobierania danych służy instrukcja - query. Problem w tym, że zwraca ona tylko jeden element. My natomiast chcemy z tego zrobić tabelę. Nie zagłębiając się w szczegóły (o tym może innym razem), należy skorzystać z metody nodes. Metoda, podobnie jak użyta wcześniej mocify oraz wspomniana query, działa w odniesieniu do typu xml. Postępowanie będzie następujące: skorzystamy z metody nodes, aby rozdzielić poszczególne atrybuty, a następnie query, aby pobrać z nich wartości. Przykład pokazany jest na poniższym listingu:

DECLARE @arguments XML =
  (SELECT 'Nagłówek' Head,
          'Przykładowe imię' FName,
          'Przykładowe nazwisko' LName
   FOR XML RAW('Data'))

SELECT CAST(Tab.Col.query('local-name(.)'AS varchar(20)) Name,
CAST(Tab.Col.query('data(.)'AS xml) Value
FROM @arguments.nodes('/Data/@*') Tab(Col)

Znów należałoby opisać wiele zagadnień, które mogą być niejasne. Zajmę się tylko kluczowymi do zrozumienia - z resztą odsyłam do dokumentacji. Zacznę od sekcji FROM. To tam wybieramy wszystkie węzły z głównego węzła data. Znak @ oznacza, że interesują nas tylko atrybuty (to w sensie XMLa też węzły). Następnie, już w sekcji SELECT korzystamy z - no właśnie. Metoda nodes wymaga przezwania rezultatu w postaci dwuczłonowej, tj. tabela(kolumna). Taka jest składnia i nie da się tu nic innego zrobić. Za pomocą tej dwuczłonowej nazwy odnosimy się do węzłów w sekcji SELECT korzystając z metody query. Na każdym z takich węzłow można wykonać kilka operacji. My potrzebujemy nazwy atrybutu (do pobrania za pomocą funkcji local-name z XQuery), oraz wartości atrybutu ((do pobrania za pomocą funkcji data z XQuery). Kropka oznacza bieżący węzeł. Tutaj również możemy wykonywać zapytania, wskazywać ścieżki, przechodzić w różnych kierunkach drzewa xml. Znów temat rzeka. Oprócz wspomnianych instrukcji Query mamy jeszcze proste rzutowania - ich, mam nadzieję, nie trzeba wyjaśniać. Popatrzmy jeszcze na końcowy rezultat takiego zapytania:

NameValue
HeadNagłówek
FNamePrzykładowe imię
LNamePrzykładowe nazwisko

Taką instrukcję możemy połączyć z odpowiednim kursorem i modyfikować za pomocą XQuery kolejne pola.

Łączenie danych ze wzorcem

Myślę, że nadszedł już czas pokazania końcowego rozwiązania. Pokazano je poniżej:

DECLARE @html xml=N'
  <html>
  <head><title></title></head>
  <body>
  <h1><span id="Head"></span></h1>
  <div>Imię: <span id="FName"></span></div>
  <div>Nazwisko: <span id="LName"></span></div>
  </body>
  </html>'

DECLARE @title xml=N'Tytuł'
DECLARE @arguments XML =
  (SELECT 'Nagłówek' Head,
          'Przykładowe imię' FName,
          'Przykładowe nazwisko' LName
   FOR XML RAW('Data'))

SET @html.modify('insert sql:variable("@title") into (/html/head/title)[1]')

DECLARE @attr varchar(20)
DECLARE @val xml
DECLARE Cur CURSOR FOR
SELECT CAST(Tab.Col.query('local-name(.)'AS varchar(20)) Name,
CAST(Tab.Col.query('data(.)'AS xml) Value
FROM @arguments.nodes('/Data/@*') Tab(Col)

OPEN Cur
FETCH NEXT FROM Cur INTO @attr, @val
WHILE @@FETCH_STATUS=0
BEGIN
  SET @html.modify('
  insert sql:variable("@val") into (//span[@id=sql:variable("@attr")])[1]'
)
  FETCH NEXT FROM Cur INTO @attr, @val
END
CLOSE Cur
DEALLOCATE Cur
SELECT @html

Dla tych, którym nie chce się samemu przetestować rezultatów, pokażę jeszcze wynik działania powyższego skryptu:

<html>
  <head>
    <title>Tytuł</title>
  </head>
  <body>
    <h1>
      <span id="Head">Nagłówek</span>
    </h1>
    <div>Imię: <span id="FName">Przykładowe imię</span></div>
    <div>Nazwisko: <span id="LName">Przykładowe nazwisko</span></div>
  </body>
</html>

Jak można zauważyć, do wzorca zostały wstawione odpowiednie dane, a całość jest pełnoprawnym dokumentem HTML, gotowym do opublikowania w sieci.

Podsumowanie

Zrealizowanie podobnego zadania możliwe jest przy pomocy innych technik. Nic nie stoi na przeszkodzie, aby to samo realizował serwer aplikacji lub strony WWW. Celem artykułu było pokazanie, że nie zawsze potrzebny nam jest kod zarządzany (a podejrzewam, że pierwsza myśl większości po zdefiniowaniu zadania tam by pofrunęła). Nie potrzeba też żadnej dodatkowej aplikacji i zapewne wzmożonego transferu sieciowego. Na koniec zaś - artykuł posłużył do pokazania kilku ciekawych technik, które, być może, przydadzą się. Wtedy, gdy najmniej się tego będziemy spodziewać.

Dla celów, nazwijmy to, przemysłowych, wypadałoby zawrzeć kod w jakiejś procedurze lub funkcji. Pozostawiam to już czytelnikom.

Kategoria:HTMLSQL ServerXMLXQuery

, 2013-12-20

Brak komentarzy - bądź pierwszy

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?