19. XML i XQuery

Microsoft SQL Server 2000 umożliwiał export relacyjnych danych z i do XML. Jednakże jedynym sposobem na przechowywanie danych typu XML było pole typu string. Jest to bardzo nieefektywny sposób przechowywania tego typu danych. Ograniczenia jakie to powoduje można mnożyć:

1. brak możliwości sprawdzenia poprawności XML
2. brak możliwości wyszukiwania odpowiednich tagów
3. brak możliwości zmiany poszczególnych wartości

Oczywiście wszystkie te problemy można ominąć poprzez napisanie odpowiednich funkcji bazodanowych. Ale żadna z tych opcji nie jest udostępniona przez starszą wersje serwera.

Większość tych ograniczeń została usunięta w SQL Server 2005 dzięki wprowadzanie typu danych XML. Pole to może być indeksowane, zmieniane i oczywiście bez żadnych problemów przeglądane. Dodatkowo został udostępniony nowy język XQuery, za pomocą którego możemy bezpośrednio manipulować na danych, zmieniać dane zaszyte wewnątrz któregoś z pól XML oraz wyciągać pojedyncze wartości.
Jak wcześniej wspomniałem głównym zastosowaniem XML jest komunikacja. Zacznijmy więc od bardzo życiowego przypadku. Jesteśmy administratorem bazy danych w firmie AdventureWorks i dostaliśmy bezpośrednie polecenia na utworzenie bilansu sprzedaży każdego z produktów. Ponieważ nasz przełożony używa Excela musimy mu podać dane w takim formacie, aby program ten bez problemu mógł sobie z nimi poradzić. Oczywiście rozwiązaniem naszego problemu będzie XML. Najpierw musimy stworzyć odpowiednie zapytanie, ale to już nie powinno nam sprawić problemów.

SELECT [Production].[Product].[Name], SUM(OrderQty) AS Quantity
FROM [Sales].[SalesOrderHeader]
            INNER JOIN [Sales].[SalesOrderDetail]
                        ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
            INNER JOIN [Production].[Product]
                        ON [Production].[Product].[ProductID] = [Sales].[SalesOrderDetail].[ProductID]
GROUP BY [Production].[Product].ProductID, [Production].[Product].[Name]
ORDER BY Quantity DESC

Teraz wystarczy dodać do naszego zapytania klauzulę FOR XML AUTO i już mamy gotowy plik, który możemy bezpośrednio wysłać do osoby zamawiającej.

SELECT [Production].[Product].[Name], SUM(OrderQty) AS Quantity
FROM [Sales].[SalesOrderHeader]
            INNER JOIN [Sales].[SalesOrderDetail]
                        ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
            INNER JOIN [Production].[Product]
                        ON [Production].[Product].[ProductID] = [Sales].[SalesOrderDetail].[ProductID]
GROUP BY [Production].[Product].ProductID, [Production].[Product].[Name]
ORDER BY Quantity DESC
FOR XML AUTO

Jak widać, nie ma potrzeby rozpisywać się, jak działa powyższa składnia. Poprzez proste dodanie do zapytanie klauzuli otrzymujemy wynik w postaci XML. Gdy dodamy do naszej opcji FOR XML AUTO dodatkową instrukcję  ELEMENTS, będziemy mogli zmienić sposób organizacji danych. Nie mamy już jednego elementu i dane jako atrybuty, lecz dane które wcześniej były atrybutami teraz są jak elementy w nagłówku Produkt.

SELECT [Production].[Product].[Name], SUM(OrderQty) AS Quantity
FROM [Sales].[SalesOrderHeader]
            INNER JOIN [Sales].[SalesOrderDetail]
                        ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
            INNER JOIN [Production].[Product]
                        ON [Production].[Product].[ProductID] = [Sales].[SalesOrderDetail].[ProductID]
GROUP BY [Production].[Product].ProductID, [Production].[Product].[Name]
ORDER BY Quantity DESCFOR XML AUTO, ELEMENTS

Dzięki użyciu nowej opcji TYPE w klauzuli FOR XML mamy możliwość traktowania wyników jako pojedynczej kolumny w głównym zapytaniu z informacją o nagłówku.

Kolejną nową opcją, do jakiej mamy dostęp w opcji FOR XML jest PATH. Ułątwia ona bardziej świadome tworzenie konstrukcji XML z możliwością tworzenia pojedynczych pól typu XML wewnątrz tabel. Poniższy przykład pokazuje, jak możemy stworzyć z danych już istniejących tabelę z polem XML.

SELECT TOP 2 SalesOrderHeader.SalesOrderID AS SalesOrderID,
            OrderDate AS OrderDate,
            CustomerID AS CustomerID,
            (SELECT ProductID AS '@ProductID',
                        OrderQty AS '@OrderQty',
                        UnitPrice AS '@UnitPrice'
            FROM Sales.SalesOrderDetail AS SalesOrderDetail
            WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
            FOR XML PATH ('SalesOrderDetail'), type) AS 'ProductsLine'
FROM Sales.SalesOrderHeader AS SalesOrderHeader
FOR XML PATH ('SalesOrderHeader')

Skrypt ten wymaga małego wyjaśnienia. Po pierwsze: nazwa kolumny określa ścieżkę w XML. Argument za opcją Path określa nazwę kolumny. Prefiks @ mówi, że kolumna wynikowa jest atrybutem, a nie elementem w polu XML.

Teraz, kiedy już wiemy, w jaki sposób możemy transformować dane do typu XML sprawdźmy jak można korzystać z danych, które zostały nam podane. Typowym przykładem korzystania z informacji zgromadzonych w XML może być słownik zawierający nazwy krajów. W każdym większym systemie bazodanowym zachodzi konieczność przechowywania danych dotyczących poszczególnych krajów. Tak więc nie ma potrzeby przy każdym większym projekcie wprowadzać je od nowa. Załóżmy więc, że do naszego projektu bazy danych Deanery chcemy wczytać plik z nazwami krajów. Użyjemy do tego klauzuli OPENXML.

Przypomnę tylko strukturę tabeli Country

CREATE TABLE [dbo].[Country]
(
    [CountryID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [CountryName] [nvarchar](64) NOT NULL
)

i może przejść do pisania skryptu.

DECLARE @xml int
DECLARE @xmlData varchar(512)

SET @xmlData = '<Countires><Name>Czechy</Name>
<Name>Anglia</Name>
<Name>Francja</Name>
<Name>Hiszpania</Name>
<Name>Portugalia</Name>
<Name>Ukraina</Name>
</Countires>'

EXEC sp_xml_preparedocument @xml OUTPUT, @xmlData
INSERT INTO Country([CountryName])
SELECT [text]
FROM OPENXML (@xml, '/Countires/Name/')
WHERE [text] IS NOT NULL

EXEC sp_xml_removedocument @xml

Najpierw zdefiniowaliśmy tekst XML. Jest to w naszym przypadku plik z danymi, które chcemy zaimportować. Kolejnym krokiem jest zdefiniowanie wskaźnika na plik. Mówiąc troszeczkę jaśniej, po tym jak wczytamy plik do pamięci SQL Server, będziemy chcieli na nim operować. Aby móc go bez problemu „wyciągnąć z pamięci” dostaniemy numer, pod jakim został on zapisany. Numer ten nazywamy wskaźnikiem. Teraz kiedy mamy już i dane i wskaźnik na plik, używamy systemowej procedury, dzięki której wczytamy naszego XML do pamięci.

EXEC sp_xml_preparedocument @xml OUTPUT, @xmlData

Linia ta mówi po prostu: zrób mi dokument z tego pliku, a jego numer wpisz do @xml.

Teraz jest już z górki. Standardowe zapytanie wpisujące do naszego słownika Country dane krajów. Klauzula

FROM OPENXML (@xml, '/Countires/Name/')

daje informacje o tym, że czytamy określony plik i tylko konkretny jego tag, ponieważ plik mógłby posiadać kody pocztowe bądź telefony kierunkowe do poszczególnych państw, a my chcemy wybrać tylko dane dotyczące nazwy kraju. Ostatni etap to usunięcie z zasobów serwera utworzonego dokumentu.

EXEC sp_xml_removedocument @xml

Ostatnią sprawą jaką chciałbym poruszyć jest pole typu XML. Jak mówiłem we wcześniejszym rozdziale dotyczącym typów danych, jest to typ, który pojawił się dopiero w tej wersji SQL Server. Umożliwia on nie tylko korzystanie z danych XML za pomocą języka T-SQL, ale również może być używany jako inny typ danych, włączając w to zmienne i kolumny. Ten nowy typ danych posiada dodatkowo język zapytań XQuery umożliwiający edycję dodawanie i usuwanie danych znajdujących się wewnątrz pola.

Podsumowanie

XML umożliwia programistom i projektantom systemów informatycznych elastyczną pracę z danymi. Komunikacja pomiędzy różnymi platformami informatycznymi za pomocą Web services działa dzięki rozpowszechnieniu się standardu XML. Kanały informacyjne RSS działające przy każdym większym portalu również istnieją wyłącznie dzięki XML.

Artykuł ten zaledwie napomknął o możliwościach SQL Server 2005 w tej dziedzinie. Indeksowanie, zarządzanie, tworzenie widoków i schematów - to wszystko i wiele innych aspektów, które mogłyby pochłonąć cały taki kurs.