Posts tagged XML

Wstęp

Klauzula FOR XML EXPLICIT jest bodaj najrzadziej używaną z klauzul FOR XML. O wiele częściej decydujemy się na użycie FOR XML PATH (od SQL Server 2005) lub FOR XML AUTO (SQL Server 2000), ponieważ obie klauzule wydają się prostsze w użyciu niż FOR XML EXPLICIT (jak to okreslił jeden z moich kolegów – “FOR XML EXPLICIT przy FOR XML PATH wygląda jak neanderltalczyk przy homo sapiens”). Ale mało kto bierze pod uwagę, że FOR XML EXPLICIT daje – jako jedna z dwóch klauzul FOR XML na SQL Server 2005 i nowszych oraz jako jedyna klauzula na SQL Server 2000 – możliwość zwracania dokumentów XML, w których znajdą się zarówno elementy, jak i atrybuty. A już prawie nikt nie wie, że jedynie FOR XML EXPLICIT umożliwia generowanie dokumentów XML zawierających sekcje CDATA. I to właśnie owo niedocenianie i unikanie klauzuli FOR XML EXPLICIT skłoniło mnie do napisania tego tekstu.

Generujemy poligon

Załóżmy, że mamy dwie, uproszczone do granic możliwości, powiązane ze sobą logicznie tabele – #Faktury i #Pozycje. Tabela #Faktury zawiera typowe dane nagłówkowe faktur – numer faktury, datę wystawienia i nazwę kontrahenta. Tabela #Pozycje zawiera pozycje (linijki) faktury, w których znajdują się: numer faktury (aby było łatwo powiązać pozycje z fakturami), numer pozycji na fakturze (takie typowe Lp), nazwę produktu, cenę (dla uproszczenia bez rozgraniczenia na netto i brutto oraz z pominięciem VAT-u) oraz ilość towaru. Kod do utworzenia tabel i wypełnienia ich danymi dwóch przykładowych faktur:

IF OBJECT_ID(N'tempdb.dbo.#Faktury') IS NOT NULL
  DROP TABLE #Faktury;
CREATE TABLE #Faktury (
  NrFaktury varchar(10),
  DataWystawienia datetime,
  Kontrahent varchar(100)
);

IF OBJECT_ID(N'tempdb.dbo.#Pozycje') IS NOT NULL
  DROP TABLE #Pozycje;
CREATE TABLE #Pozycje (
  NrFaktury varchar(10),
  NrPozycji int,
  Produkt varchar(50),
  Ilosc int,
  Cena money
);

INSERT INTO #Faktury (NrFaktury, DataWystawienia, Kontrahent)
SELECT '04/07/2010','20100716','FirmaA'
UNION ALL
SELECT '05/07/2010','20100716','FirmaB';

INSERT INTO #Pozycje (NrFaktury, NrPozycji, Produkt, Ilosc, Cena)
SELECT '04/07/2010',1,'Produkt1',10,100.00
UNION ALL
SELECT '04/07/2010',2,'Produkt2',2,10.00
UNION ALL
SELECT '04/07/2010',3,'Produkt3',1,10.50
UNION ALL
SELECT '05/07/2010',1,'Produkt1',5,10.00
UNION ALL
SELECT '05/07/2010',2,'Produkt3',1,10.50;

FOR XML EXPLICIT krok po kroku

Postawmy sobie pierwsze zadanie.

Zadanie: użyć klauzuli FOR XML EXPLICIT w zapytaniu odwołującym się do powyższych dwóch tabel, by uzyskać w wyniku dokument XML o następującej postaci:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>
  <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="100.0000" Ilosc="10" />
  <Pozycja NrPozycji="2" Produkt="Produkt2" Cena="10.0000" Ilosc="2" />
  <Pozycja NrPozycji="3" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
</Faktura>
<Faktura Numer="05/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaB</Kontrahent>
  <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="10.0000" Ilosc="5" />
  <Pozycja NrPozycji="2" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
</Faktura>

Rozwiązanie:

SELECT
  1 AS Tag,
  NULL AS Parent,
  F.NrFaktury AS [Faktura!1!Numer],
  F.DataWystawienia AS [Faktura!1!DataWystawienia!ELEMENT],
  F.Kontrahent AS [Faktura!1!Kontrahent!ELEMENT],
  NULL AS [Pozycja!2!NrPozycji],
  NULL AS [Pozycja!2!Produkt],
  NULL AS [Pozycja!2!Cena],
  NULL AS [Pozycja!2!Ilosc]
FROM #Faktury F
UNION ALL
SELECT
  2,
  1,
  P.NrFaktury,
  NULL,
  NULL,
  P.NrPozycji,
  P.Produkt,
  P.Cena,
  P.Ilosc
FROM #Pozycje P
ORDER BY [Faktura!1!Numer], Tag, [Pozycja!2!NrPozycji]
FOR XML EXPLICIT;

Komentarz (krok po kroku):

Aby zrozumieć, jak działa FOR XML EXPLICIT, uruchom najpierw kod powyższego zapytania bez klauzuli FOR XML EXPLICIT. Wynik powinien wyglądać następująco:

Kolorami żółtym i zielonym zaznaczyłem obie faktury i ich pozycje (wiersze zawierające dane nagłówkowe są oznaczone ciemniejszymi odcieniami).

Zwróć uwagę na kolejność wierszy. Nieprzypadkowo wiersze pozycji znajdują się tuż za wierszem nagłówka odpowiadającej pozycjom faktury. Sortowanie jest kluczem do sukcesu w zapytaniach z FOR XML EXPLICIT. W tym przypadku dane zostały posortowane względem kolejno: numeru faktury (tu bardzo ważna uwaga – zauważ, że w drugim zapytaniu SELECT wybierającym pozycje faktur także wybieram numer faktury – zapewniam w ten sposób wspólną kolumnę dla wszystkich wierszy wynikowych zapytania), kolumny Tag (o której za chwilę, a dzięki której wiersze nagłówków znajdują się w wyniku przed wierszami pozycji) oraz numeru pozycji (dzięki temu w dokumencie XML pozycje dla każdej faktury będą umieszczone zgodnie z kolejnością na fakturze). To uporządkowanie rekordów jest ważne, ponieważ FOR XML EXPLICIT najpierw buduje taką tabelę, a następnie działa na niej niczym kursor – buduje dokument XML doklejając odpowiednio spreparowane dane z kolejnych wierszy ujęte w elementy i atrybuty. To od kolejności wierszy zależy postać wynikowego dokumentu XML.

Zapytanie składa się z dwóch zapytań SELECT połączonych operatorem UNION ALL. W każdym z dwóch zapytań liczba kolumn jest taka sama (to oczywiste, bo takie są wymagania operatora UNION). Pierwsze dwie kolumny – Tag i Parent - są kolumnami specjalnymi umożliwiającymi poprawne działanie klauzuli FOR XML EXPLICIT. Każde zapytanie otrzymuje swój numer (Tag, wcale nie musi być unikalny) oraz numer “rodzica” (Parent, jeżeli jest NULL, rodzica nie ma). Numery te decydują, gdzie w strukturze wynikowego dokumentu XML zostaną umieszczone dane z poszczególnych wierszy.

Prześledźmy na podstawie dwóch pierwszych wierszy pokazanych poniżej, jak budowany jest wynikowy dokument XML.

Na początek brany jest pierwszy rekord. Dla niego w kolumnie Tag widnieje wartość 1. SQL Server szuka w tym wierszu, gdzie znajdują się wartości w kolumnach, które mają znacznik 1 w nazwie (np. Faktura!1!Numer). Nazwy kolumn decydują o tym, do którego Tag-a zalicza się wartość oraz jak będą nazywały się elementy / atrybuty. Jeżeli na końcu nazwy kolumny pojawi się słowo ELEMENT, wartość będzie umieszczona w elemencie (inaczej znajdzie się w atrybucie). Dla pierwszego wiersza SQL Server buduje więc coś takiego:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>

Nazwy kolumn zdecydowały, że elementem nadrzędnym jest <Faktura>, numer faktury znalazł się w atrybucie Numer, data wystawienia faktury w elemencie <DataWystawienia>, a nazwa kontrahenta w elemencie <Kontrahent>.

Następnie brany jest drugi wiersz. W nim w kolumnie Tag SQL Server znajduje wartość 2, zatem uwzględnia w tym wierszu tylko te kolumny, w których nazwach występuje znacznik 2 (np, Pozycja!2!NrPozycji). Ponieważ w kolumnie Parent znajduje się wartość 1, a poprzedni wiersz miał Tag równy 1 (patrz czerwona strzałka na rysunku obrazującym wiersze), SQL Server wie, że XML powstały z bieżącego wiersza należy zagnieździć w XML-u powstałym z poprzedniego wiersza. Dzięki temu dokument zbudowany z dwóch wierszy wygląda tak:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>
  <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="100.0000" Ilosc="10" />

I tak kolejne pozycje będą doklejane wewnątrz znacznika <Faktura>, aż zostanie napotkany wiersz, w którym w kolumnie Tag będzie znajdowała się wartość 1 (będzie to początek następnej faktury). Wówczas element <Faktura> otwarty przy tworzeniu XML-a z pierwszego wiersza zostanie zamknięty, czyli pojawi sie koniec elementu – </Faktura>.

FOR XML EXPLICIT – tips & tricks

A teraz spróbujmy zmodyfikować nieco zadanie.

Zadanie: użyć klauzuli FOR XML EXPLICIT w zapytaniu odwołującym się do tabel #Faktury i #Pozycje, by uzyskać w wyniku dokument XML o następującej postaci:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>
  <Pozycje>
    <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="100.0000" Ilosc="10" />
    <Pozycja NrPozycji="2" Produkt="Produkt2" Cena="10.0000" Ilosc="2" />
    <Pozycja NrPozycji="3" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
  </Pozycje>
</Faktura>
<Faktura Numer="05/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaB</Kontrahent>
  <Pozycje>
    <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="10.0000" Ilosc="5" />
    <Pozycja NrPozycji="2" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
  </Pozycje>
</Faktura>

Różnica w stosunku do poprzedniego zadania polega na tym, że teraz chcemy pozycje mieć otoczone znacznikami <Pozycje> i </Pozycje>.

Rozwiązanie:

SELECT
  1 AS Tag,
  NULL AS Parent,
  F.NrFaktury AS [Faktura!1!Numer],
  F.DataWystawienia AS [Faktura!1!DataWystawienia!ELEMENT],
  F.Kontrahent AS [Faktura!1!Kontrahent!ELEMENT],
  NULL AS [Pozycje!2],
  NULL AS [Pozycja!3!NrPozycji],
  NULL AS [Pozycja!3!Produkt],
  NULL AS [Pozycja!3!Cena],
  NULL AS [Pozycja!3!Ilosc]
FROM #Faktury F
UNION ALL
SELECT
  2,
  1,
  F.NrFaktury,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL
FROM #Faktury F
UNION ALL
SELECT
  3,
  2,
  P.NrFaktury,
  NULL,
  NULL,
  NULL,
  P.NrPozycji,
  P.Produkt,
  P.Cena,
  P.Ilosc
FROM #Pozycje P
ORDER BY [Faktura!1!Numer], Tag, [Pozycja!3!NrPozycji]
FOR XML EXPLICIT;

I teraz zadanie dla Ciebie – spróbuj prześledzić działanie FOR XML EXPLICIT w powyższym kodzie (kieruj się wskazówkami podanymi przeze mnie w pierwszym zadaniu – zakomentuj na przykład klauzulę FOR XML EXPLICIT i uruchom zapytanie ponownie, by zobaczyć, jak układają się wiersze). Powyższy trick możesz stosować wszędzie tam, gdzie chodzi o wygenerowanie dodatkowego elementu, w którym zostaną osadzone elementy z danymi (tak, to jest metoda na wygenerowanie elementu ROOT na SQL Server 2000!).

Podsumowanie

Wszystkich tych, którzy dotąd omijali FOR XML EXPLICIT szerokim łukiem, zachęcam do poeksperymentowania. Sugeruję obejrzeć różne warianty, oglądać, jak zmienia się wynikowy XML w zależności od sortowania i wartości w kolumnach Tag i Parent. Zabawę polecam zwłaszcza osobom, którym przychodzi w pracy generować XML-a na SQL Server 2000. Tam klauzula FOR XML EXPLICIT może być niekiedy jedynym sensownym ratunkiem w sytuacji, gdy mamy wygenerować dokument XML o z góry narzuconej strukturze.