Lekcja 18. Użycie typu danych XML

Kiedy używać typu danych XML

Schemat bazy danych jest czasami zmienny. Pomyśl o sytuacji gdy musisz użyć wielu różnych schematów dla tego samego rodzaju zdarzenia. SQL Server posiada wiele takich przypadków. Data Definition Language (DDL) triggers i extended events są dobrym przykładem. Istnieje dziesiątki różnych zdarzeń DDL. Każdy z nich zwraca inną informację o zdarzeniu a każde zdarzenie zwraca dane z innym schematem. Dlatego triggery DDL zwracają informacje typu XML z pomocą funkcji eventdata(). Informacje te są łatwe do zarządzania i manipulowania. Dzięki takiej architekturze SQL Serwer będzie mógł dodawać kolejne zdarzenia DDL w przyszłości. Kolejnym przekładem jest XML showplan. Można wygenerować plan wykonania zapytania w formacie XML używając wyrażeń SET SHOWPLAN_XML i SET STATISTIC XML. Taki plan może być przechowywany i używany przez aplikacje. Możliwe jest również wymuszenie na optymalizatorze użycia konkretnego planu wykonania za pomocą hint'u USE PLAN. Innym miejscem do którego zastosowanie ma typ XML jest sytuacja w której dane są nieliczne i zawierają dużo wartości NULL oraz niektóre wartości nie są stosowane do wszystkich wierszy. Standardowym rozwiązaniem jest rozszerzenie modelu relacyjnego o podtypy. Rozwiązanie oparte na XML może być dużo łatwiejsze do zaimplementowania.

Metody typu XML

W lekcji poświęconej XQuery używany był typ danych XML i jego metoda query() która jako parametr używała wyrażenia XQuery. Typ danych XML zawiera pięć metod które akceptują wyrażenia XQuery jako parametr, są to:

query() - zapytania na XML'u. Zwraca dane typu XML.
value() - pobranie wartości atomowej. Może być stosowana gdziekolwiek wartości skalarne sa dozwolone np. w klauzuli SELECT. Jako pierwszy parametr przyjmuje wyrażenie XQuery, jako drugi zwracany typ danych. Musi zwracać wartość skalarną dlatego trzeba pobrać element o odpowiedniej pozycji w sekwencji, nawet jeżeli istnieje tylko jeden.
exist() - testowanie istnienia węzła w instancji XML. Typowym zastosowaniem jest klauzula WHERE. Zwraca 1 jeżeli wyrażenie XQuery zwraca niepusty wynik, 0 jeżeli wyrażenie XQuery zwraca pusty wynik i NULL jeżeli instancja XML jest NULL.
modify() - modyfikacja dokumentu XML. Standard W3C nie wspiera modyfikacji danych przy użyciu XQuery. SQL Serwer wprowadził własne rozszerzenie służące do modyfikacji danych XML które wspiera trzy słowa kluczowe (DML) modyfikujące dane: insert, delete, replace.
nodes() - rozdzielenie dokumentu XML na wiersze. Pomocne kiedy chcemy rozdzielić dane typu XML na dane relacyjne. Działanie tej funkcji jest podobne do wyrażenia OPENXML lecz zazwyczaj jest dużo szybsze. Metoda nodes() jest używana dla każdego wiersza w tabeli. Za pomocą operatora APPLY można wywołać prawostronne wyrażenie tablicowe dla każdego lewostronnego wiersza z części klauzuli FROM.

Typ XML i Dynamic Schema

Na przykładzie tabeli Products z bazy TSQL2012 pokazane zostanie jak stworzyć dynamiczny schemat w bazie relacyjnej. Załóżmy, że potrzebujemy przechowywać specjalne atrybuty dla napojów i inne atrybuty dla przypraw. Np przechowujemy procentową rekomendowaną dzienną ilość spożycia (RDA) witamin dla napojów i krótki opis dla przypraw. Możemy dodać kolumnę typu XML do tabeli Production.Products bazy TSQL2012 zwaną "additionalatributes". Ponieważ inne kategorie produktów nie mają atrybutów to musi być ona NULLABLE.

ALTER TABLE Production.Products
ADD additionalattributes XML NULL;

Przed wstawieniem danych do nowej kolumny, chcemy nadać ograniczenia wstawianych wartości dla tej kolumny. Powinniśmy użyć typu XML z walidacją schematu. Przy użyciu schematów XML możemy ograiczyć możliwe węzły, typy danych w węzłach itp. SQL Serwer pozwala na walidację XML za pomocą kolekcji schematów XML. Pozwala to na użycie różnych schematów dla przypraw i napojów. Utworzenie kolekcji schematów odbywa się za pomocą polecenia CREATE XML SCHEMA COLLECTION. Na wejściu dostarczamy dokument XSD. Najłatwiejszym sposobem stworzenia schematu XML jest stworzenie tabeli relacyjnej i użycie opcji XMLSCHEMA dla klauzuli FOR XML. Zapisanie zwracanego wyniku w zmiennej i użycie jej jako parametru wejściowego dla CREATE XML SCHEMA COLLECTION. Poniższy kod tworzy tymczasowe tabele dla napojów i przypraw po czym używa klauzuli SELECT z FOR XML do stworzenia schematu XML dla tych tabel. Zapisuje schematy jako zmienne i tworzy kolekcję schematów z e zmiennych. Po stworzeniu kolekcji tabele są usuwane.

-- Auxiliary tables
CREATE TABLE dbo.Beverages
(
percentvitaminsRDA INT
);
CREATE TABLE dbo.Condiments
(
shortdescription NVARCHAR(50)
);
GO
-- Store the Schemas in a Variable and Create the Collection
DECLARE @mySchema NVARCHAR(MAX);
SET @mySchema = N'';
SET @mySchema = @mySchema +
(SELECT *
FROM Beverages
FOR XML AUTO, ELEMENTS, XMLSCHEMA('Beverages'));
SET @mySchema = @mySchema +
(SELECT *
FROM Condiments
FOR XML AUTO, ELEMENTS, XMLSCHEMA('Condiments'));
SELECT CAST(@mySchema AS XML);
CREATE XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes AS @mySchema;
GO
-- Drop Auxiliary Tables
DROP TABLE dbo.Beverages, dbo.Condiments;
GO

Wygenerowany schemat:

<xsd:schema xmlns:schema="Beverages" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="Beverages" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="Beverages">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="percentvitaminsRDA" type="sqltypes:int" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<xsd:schema xmlns:schema="Condiments" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="Condiments" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="Condiments">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="shortdescription" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="50" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Kolejnym krokiem jest zmiana kolumny XML na schema-valideted.

ALTER TABLE Production.Products
ALTER COLUMN additionalattributes
XML(dbo.ProductsAdditionalAttributes);

Informacje o kolekcjach schematów można odczytać z widoków systemowych:

sys.xml_schema_collections
sys.xml_schema_namespaces
sys.xml_schema_components

Kolekcje schematów są przechowywane w SQL serwerze w postaci tabelarycznej. Można ich używać poza SQL serwerem, np w aplikacjach .NET. Przed użyciem nowego typu danych trzeba zadbać jeszcze o to aby schematy nie były przypisane złym kategoriom produktów. Można to rozwiązać za pomocą trigerów lub deklaracji ograniczeń constraint. Ograniczenia są preferowane , dlatego do schematów zostały dodane przestrzenie nazw.Nie można użyć typów danych XML wewnątrz ograniczeń T-SQL. Trzeba stworzyć dwie dodatkowe funkcje, pierwsza będzie pobierała nazwę kategorii produktu a druga będzie pobierała przestrzeń nazw kolumny XML. W sprawdzaniu ograniczeń (check constraint) będzie można sprawdzić czy wartość obu jest równa. Poniżej kod z funkcjami:

-- Function to Retrieve the Namespace
CREATE FUNCTION dbo.GetNamespace(@chkcol XML)
RETURNS NVARCHAR(15)
AS
BEGIN
RETURN @chkcol.value('namespace-uri((/*)[1])','NVARCHAR(15)')
END;
GO
-- Function to Retrieve the Category Name
CREATE FUNCTION dbo.GetCategoryName(@catid INT)
RETURNS NVARCHAR(15)
AS
BEGIN
RETURN
(SELECT categoryname
FROM Production.Categories
WHERE categoryid = @catid)
END;
GO
-- Add the Constraint
ALTER TABLE Production.Products ADD CONSTRAINT ck_Namespace
CHECK (dbo.GetNamespace(additionalattributes) =
dbo.GetCategoryName(categoryid));
GO

Przykład wprowadzania poprawnego XML do kolumny:

-- Beverage
UPDATE Production.Products
SET additionalattributes = N'
<Beverages xmlns="Beverages">
<percentvitaminsRDA>27</percentvitaminsRDA>
</Beverages>'
WHERE productid = 1;
-- Condiment
UPDATE Production.Products
SET additionalattributes = N'
<Condiments xmlns="Condiments">
<shortdescription>very sweet</shortdescription>
</Condiments>'
WHERE productid = 3;

Aby przetestować wprowadzone ograniczenia należy spróbować wstawić nieprawidłowe dane:

-- String instead of int
UPDATE Production.Products
SET additionalattributes = N'
<Beverages xmlns="Beverages">
<percentvitaminsRDA>twenty seven</percentvitaminsRDA>
</Beverages>'
WHERE productid = 1;
-- Wrong namespace
UPDATE Production.Products
SET additionalattributes = N'
<Condiments xmlns="Condiments">
<shortdescription>very sweet</shortdescription>
</Condiments>'
WHERE productid = 2;
-- Wrong element
UPDATE Production.Products
SET additionalattributes = N'
<Condiments xmlns="Condiments">
<unknownelement>very sweet</unknownelement>
</Condiments>'
WHERE productid = 3;

Wynik zapytania: Msg 6926, Level 16, State 1, Line 2 XML Validation: Invalid simple type value: 'twenty seven'. Location: /*:Beverages[1]/*:percentvitaminsRDA[1] Indeksy XML Typ XML jest typem zajmującym dużo miejsca w pamięci. Może zajmować do 2GB w każdej kolumnie. Skanowanie sekwencyjne danych XML nie jest wydajne. Z danymi relacyjnymi można stworzyć indeks na kolumnie pozwalający na przeszukanie indeksu zamiast skanowania tabeli. Tak samo można utworzyć specjalne indeksy na kolumnach XML. Taki indeks zawiera rozdrobnione dane dla dokumentów XML. Liczba wierszy indeksu jest proporcjonalna do liczby węzłów w dokumencie XML. Zwykły index XML pozwala na szybkie znalezienie wierszy z użyciem funkcji exist(). Po stworzeniu takiego indeksu można stworzyć jeszcze trzy rodzaje indeksów dla kolumny XML:

PATH - użyteczny gdy zapytania używają wyrżeń ścieżki. Przyspiesza metody exist() i value() dla wyszczególnionych ścieżek dokumentu XML.
VALUE - użyteczny gdy zapytania bazują na wartościach i ścieżka nie jest wyszczególniona.
PROPERTY - użyteczny gdy zapytnie pobiera jedną lub więcej wartości z instancji XML za pomocą metody value().

Ćwiczenia

I. Użycie metod value() i exist().

Utwórz instancję dokumentu XML która jest poniżej:

DECLARE @x AS XML;
SET @x = N'



Customer NRZBB

2007-10-03T00:00:00


2007-10-13T00:00:00


2008-03-16T00:00:00




Customer MLTDN

2006-09-18T00:00:00


2008-03-04T00:00:00


';

Napisz zapytanie które pobierze nazwę pierwszego klienta jako wartość skalarną
```
SELECT @x.value('(/CustomersOrders/Customer/companyname)[1]',
'NVARCHAR(20)')
AS [First Customer Name];
```
Wynik: First Customer Name -------------------- Customer NRZBB

Sprawdź czy nazwa firmy i adres istnieją w węźle klienta.

SELECT @x.exist('(/CustomersOrders/Customer/companyname)')
AS [Company Name Exists],
@x.exist('(/CustomersOrders/Customer/address)')
AS [Address Exists];

Wynik: Company Name Exists Address Exists ------------------- -------------- 1 0

Użycie metod query(), nodes(), i modify()

Utwórz instancję dokumentu XML która jest poniżej:

DECLARE @x AS XML;
SET @x = N'



Customer NRZBB

2007-10-03T00:00:00


2007-10-13T00:00:00


2008-03-16T00:00:00




Customer MLTDN

2006-09-18T00:00:00


2008-03-04T00:00:00


';

Napisz zapytanie które zwróci wszystkie zamówienia dla pierwszego klienta jako XML.

SELECT @x.query('//Customer[@custid=1]/Order')
AS [Customer 1 orders];

Wynik:

2007-10-03T00:00:00


2007-10-13T00:00:00


2008-03-16T00:00:00

Podziel informacje o zamówieniach dla pierwszego klienta używając metody nodes().
```
SELECT T.c.value('./@orderid[1]', 'INT') AS [Order Id],
		T.c.value('./orderdate[1]', 'DATETIME') AS [Order Date]
FROM @x.nodes('//Customer[@custid=1]/Order') AS T(c);
```
Wynik: Order Id Order Date ----------- ----------------------- 10692 2007-10-03 00:00:00.000 10702 2007-10-13 00:00:00.000 10952 2008-03-16 00:00:00.000

Zaktualizuj nazwę pierwszego klienta po czym pobierz jego nazwę.

SET @x.modify('replace value of
/CustomersOrders[1]/Customer[1]/companyname[1]/text()[1] with "New Company Name"');

SELECT @x.value('(/CustomersOrders/Customer/companyname)[1]', 'NVARCHAR(20)') AS [First Customer New Name];

Wynik: First Customer New Name ----------------------- New Company Name

Podsumowanie

Typ danych XML może być używany w różnych scenariuszach w bazie relacyjnej.
Typ danych XML pozwala na walidację treści za pomocą kolekcji schematów.
Można wykonywać działania na kolumnach XML za pomocą metod XML.