Generujeme XML z MS SQL Serveru

1. dubna 2004

•

Základním posláním relačních databází je uchovávat strukturovaná data. Dotazovací příkaz SELECT však odjakživa vracel požadovaná data ve dvourozměrných tabulkách bez jakékoli další struktury. Microsoft SQL Server 2000 a novější nám ale umožňuje získávat data také v oblíbeném strukturovaném formátu XML. Podrobnosti se dozvíte v tomto článku.

O čem to bude? Nejdříve si vysvětlíme, k čemu nám výsledky dotazu v XML mohou být a jak je můžeme dále zpracovat. Dále si povíme, jak o tyto data požádat, a co od výsledku můžeme čekat. Nakonec si ukážeme, jak přesně specifikovat pravidla pro generování takového XML výstupu.

Proč vůbec generovat XML?

Zjednodušeně řečeno, protože výstup pomocí tabulky nemusí vždy stačit. Tabulka je totiž maximálně dvourozměrná a my občas potřebujeme mít data strukturovanější. Další rozměr můžeme přidat zvýšením množství tabulek a zvýšením množství dotazů, tohle je však směr, kterým bychom rozhodně neměli postupovat. Zvyšují se přitom totiž režijní náklady pro zpracování celého procesu, a to určitě nechceme.

Proč tedy využívat jakýchsi primitivních tabulek? Ty databáze sice používá interně a díky nim jsou sice data v databázi uloženy velice efektivně, ovšem se strukturou dat je to horší. Ta se udržuje pomocí relací mezi tabulkami. Když už databázový stroj nutíme, aby tyto relace vyhodnotil a vrátil nám související záznamy, proč po něm nechtít, aby nám je dodal v nějakém dobře strukturovaném formátu? A tím je právě XML.

Dnes již máme mnoho nástrojů, které s XML pracují, počínaje XSLT a konče například webovými službami. S dalším zpracováním si tedy hlavu lámat nemusíme, bude totiž ještě jednodušší, než práce s tabulkovými daty, ve kterých bychom pravděpodobně museli procházet postupně jeden záznam za druhým. Nebojte se používat moderní technologie. Netvrdím, že dosáhnete lepších výkonů, avšak rozhodně si zkrátíte dobu vývoje, zmenšíte chybovost a zajistíte si snadnou údržbu a rozšiřitelnost projektu.

Jak to celé pracuje?

Nejznámějším příkazem jazyka SQL je SELECT, který slouží k dotazování na data. Microsoft Transact-SQL k němu přidává své vlastní vylepšení, díky kterému se dotaz chová nápadně odlišně. Jak je již zřejmé, dotaz totiž ze svého výsledku vygeneruje XML data a uloží je jako řetězce do několika řádků výsledné tabulky, takže je stačí přebrat, slepit dohromady a zabalit do kořenového elementu.

Podívejme se na syntaktické prvky tohoto příkazu využívaných pro export do XML (jedná o modifikátory, které se uvádějí na konci dotazu):

FOR XML RAW: V tomto nejjednodušším případě je dotaz vyhodnocen běžným způsobem a každý řádek výsledku je zobrazen do jednoho XML elementu, přičemž každý jeho sloupec se stane atributem tohoto elementu. Vidíte, že zde nedosáhneme zlepšení struktury výsledků, takže pojďme dále.
FOR XML AUTO: Každá tabulka, kterou dotaz zpracovává, vytvoří jednu úroveň struktury a každý její řádek se stane XML elementem. Sloupce tabulky jsou XML atributy nebo volitelně podelementy. Řádky z každé následující tabulky se stanou dalšími podřízenými elementy. Tuto situaci si podrobně rozebereme níže.
FOR XML EXPLICIT: Tento přístup je nejsložitější, avšak také nejmocnější. Uspořádání všech XML elementů a atributů je totiž plně v moci příkazu SELECT. Vytvoření kýženého dotazu ovšem není zdaleka triviální.

Volitelně můžeme přidat k dotazu navíc také modifikátor XMLDATA oddělený čárkou. Při jeho použití se pro XML dokument vygeneruje také příslušná XML šablona.

Automatický mód

Mějme nějakou databázi (NorthWind) a v ní tabulky, mezi kterými jsou vytvořené relace pomocí primárních a cizích klíčů. Tabulka Customers má primární klíč CustomerID, na který ukazuje cizí klíč CustomerID v tabulce Orders. Vezměmě si nejdříve naprosto banální dotaz na obsah těchto tabulek se spojením souvisejících záznamů:

SELECT Customers.CustomerID,
Customers.ContactName,
Orders.OrderID
FROM Orders, Customers
WHERE Customers.CustomerID = Orders.CustomerID

Výsledek takového dotazu je naprosto zřejmý. Každý řádek obsahuje jak hodnoty z tabulky Customers, tak z tabulky Orders. Pokud bychom chtěli z těchto dat vygenerovat HTML (například vnořené seznamy) pomocí některé server-side technologie, museli bychom pravděpodobně nad tabulkou poměrně náročně iterovat, protože je nutné při každé iteraci kontrolovat, zda se náhodou nejedná o další záznam z tabulky Customers. To by totiž v našem konkrétním příkladě znamenalo ukončit starý seznam a přejít na nový.

Parametr FOR XML AUTO je lékem na tuto nemoc. Stačí tato kouzelná slovíčka připsat k dotazu a máme vystaráno. Data se pěkně seřadí do přehledné struktury XML výstupu. Pro zpracování dat do vnořených seznamů můžeme vykonat XML transformaci, nebo si například dokument projít pomocí XML DOM a zpracovat ho postupně. V každém případě již nemusíme hlídat, která data patří k jednomu záznamu z tabulky Customers, protože to je již dáno organizací dat. Ukažme si to tedy na výsledku dotazu:

<Customers CustomerID=“ALFKI“ ContactName=“Maria Anders“>
  <Orders OrderID=“10643″ />
  <Orders OrderID=“10692″ />
  …
</Customers>
…

Jak vidíte, XML elementy jsou pojmenovány podle tabulek, ze kterých záznamy vzešly. Každý sloupec je přitom nahrazen XML atributem. Velmi jednoduché. Takto je možné navrhnout libovolnou mnohaúrovňovou strukturu.

Organizace XML elementů se neřídí relacemi mezi tabulkami, jak by se mohlo na první pohled zdát. Ve skutečnosti je dána pořadím sloupců, jak jsou uvedeny v příkazu SELECT. Na nejvyšší úrovni struktury jsou záznamy z tabulky Customers právě proto, že je v dotazu uveden její sloupec CustomerID na prvním místě. SQL server pak prochází následující sloupce a pro každý sloupec, který je z další tabulky, vytvoří nový podřízený element k předešlému. Stačí trochu experimentovat a není problém do tohoto systému proniknout.

Tato technika má však (ne)jeden podstatný nedostatek. Neřeší situaci, kdy na jednu tabulku odkazuje více tabulek, například pokud by existovala tabulka CustomerDetails a tu bychom chtěli do výsledků zahrnout. V takovém případě prostě obecně pohoří i operace typu JOIN nad těmito tabulkami, protože v podstatě takovouto strukturu není možné dvourozměrnou tabulkou snadno vyjádřit. Naštěstí zde máme jiný mód XML výstupu.

Upřesňující mód

V tomto módu příkaz SELECT nerozhoduje pouze o datových hodnotách, které budou do výsledku zahrnuty, avšak také přesně definuje, jakým způsobem budou tyto data organizována. K tomu je třeba dodržet určitou šablonu, se kterou vás zde seznámím – téma je však již poměrně náročné a ve většině případů vám bude stačit automatický mód, takže tento oddíl můžete klidně vynechat.

Chceme-li generovat komplikovanější XML výstupy, musíme si nejdříve ujasnit, jak má XML vypadat. Podle něj totiž budeme navrhovat takzvanou obecnou tabulku (universal table). V našem případě budeme požadovat následující výstup:

<Customer CustomerID=“ALFKI“ ContactName=“Maria Anders“>
  <Order OrderID=“10643″ />
  <Order OrderID=“10692″ />
  …
  <CustomerDetail CustomerDetailID=“ID1″ />
  <CustomerDetail CustomerDetailID=“ID2″ />
  …
</Customer>
…

Struktura obsahuje tři druhy objektů. Říkejme jim tagy. Jedná se o Customer, Order a CustomerDetail. Musíme si uvědomit jejich uspořádání, protože to pak budeme definovat v tabulce. Nejdříve bychom si měli tagy očíslovat. Tedy Customer bude mít pořadové číslo 1, Order 2 a CustomerDetail 3. Tato čísla později použijeme jako odkazy na jednotlivé tagy.

Každý záznam obecné tabulky zastupuje jeden záznam v jedné z analyzovaných tabulek. Jelikož má být při exportu nahrazen příslušným tagem, musíme jej opatřit metadaty, které udávají, na jakém místě se mají v dokumentu nacházet.

Metadata jsou tvořena dvěma sloupci, oba jsou typu „int“. První sloupec s názvem Tag identifikuje tag, pod kterým se záznam zobrazí, další sloupec pojmenovaný Parent určuje nadřazený (rodičovský) tag. Pokud se má jednat o tag na nejvyšší úrovni, bude mít Parent hodnotu NULL.

Všechny následující sloupce již obsahují samotná exportovaná data. Jejich názvy přitom musí dodržovat předem danou konvenci:

NázevTagu!IdTagu!NázevAtributu[!direktiva]

Název sloupce tedy deklaruje, k jakému tagu patří, jak se tento tag jmenuje a jakým atributem bude sloupec označen. Direktivy jsou nepovinné, podíváme se na ně později.

Pokud náš dotaz vytvoří podobnou obecnou tabulku, může se do ní pustit XML export, který ji lineárně prochází řádek po řádku a skládá z jejích dat XML dokument.

Po mírně teoretickém úvodu si zkusíme vygenerovat první úroveň celého dotazu, tedy objekty Customer:

select 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID]
from Customers
for xml explicit

Teď si vyzkoušíme přidání dalších objektů, a to Order. Budeme přidávat nejen další řádky, ale i sloupce, protože rozšiřujeme sortiment datových prvků (přidáváme OrderID). Ve sloupcích, které se netýkají aktuálního záznamu, můžeme uložit hodnoty NULL. Přidání dalších hodnot provedeme připojením dalšího dotazu a výsledky posléze seřadíme, aby zapadaly do požadované struktury.

select 1 as Tag,
    NULL as Parent,
    Customers.CustomerID as [Customer!1!CustomerID],
    NULL as [Order!2!OrderID]
from Customers
union all
select 2,
    1,
    Orders.CustomerID,
    Orders.OrderID
from Orders
order by [Customer!1!CustomerID], [Order!2!OrderID]
for xml explicit

V přidaném dotazu je třeba vygenerovat hodnotu i do sloupce Customer!1!CustomerID, aby pak mohly být záznamy z obou tabulek vzájemně správně seřazeny. Pokud bychom sem dali hodnotu NULL, všechny objekty Order by se zařadily až pod poslední Customer a ne pod ten související. Také si všimněte, že zde vůbec není použito spojení obou tabulek, nalezení souvisejících záznamů se provede prostým seřazením výsledků obou dotazů.

Přidání posledního objektu (CustomerDetail) je analogické a není třeba ho ukazovat (už jen proto, že tato tabulka v databázi NorthWind vlastně ani neexistuje). Můžete si to zkusit za domácí úkol. Já bych se rád ještě vrátil k názvům datových sloupců a ukázal vám některé direktivy.

Direktivy názvů sloupců

ID, IDREF, IDREFS: Tyto direktivy slouží k vytváření odkazů skrz dokument. Nebudu je rozebírat, jen byste měli vědět, že existují, možná se vám to někdy bude hodit.
hide: Data tohoto sloupce nebudou exportována. Vhodné pro sloupce, které slouží pouze pro seřazování výsledků.
element: Data nebudou zobrazeny jako XML atributy, nýbrž samostatné elementy podřízené příslušnému tagu. Data jsou pak textovým obsahem tohoto elementu.
xml: Data jsou XML kódem, který se má vložit do dokumentu přímo, bez dalších úprav.
xmltext: Data jsou XML kódem, které může SQL server upravit dle svých potřeb.
cdata: Data jsou bez dalších úprav vloženy do XML sekce CDATA.

Připomínám, že direktivy se zapisují do názvu sloupečku na konec, tedy například Order!2!OrderID!element způsobí vypsání tohoto sloupce ne jako atribut, ale jako oddělený XML element.