Zapytania do danych XML w SQL

Zakres zagadnień dotyczących obecności XML’a w SQL Server jest szeroki. Pisanie zapytań do dokumentów XML, to bardzo często wykorzystywana w praktyce umiejętność. Dlatego też, postanowiłem umieścić rozdział wprowadzający do XML w ramach tego kursu.

Na początek, zaprezentuję ogólną koncepcję standardu XML i jego typowe zastosowania w praktyce. Pokażę gotowe recepty, przykłady – w jaki sposób pisać zapytania SQL do dokumentów XML.

W artykule tym omawiam podstawowe zastosowania funkcji OPENXML() oraz wbudowanych metod typu danych XML. Związanych z nimi wyrażeń XPath i elementów języka XQuery w aspekcie przeszukiwania dokumentów XML i przekształcania ich na postać relacyjną.


Czym jest XML

XML (ang. Extensible Markup Language) określany jest często jako „język”, ale podobnie jak SQL, nie jest to język programowania. XML to najbardziej popularny standard (język) opisu danych. Jego komercyjne pojawienie się pod koniec lat 90-tych, spowodowało wręcz eksplozję zastosowań.

Stosuje się go wszędzie tam, gdzie chcemy przekazać jakieś informacje. W ogólności, na XML możemy spojrzeć jak na opakowanie. To nic innego jak metadane opisujące i nadające sens przesyłanym informacjom.

XML to zbiór zasad, które pozwalają definiować dowolne, ale ściśle ustandaryzowane, hierarchiczne dokumenty. XML (czyli standard) posiada wiele rozszerzeń powiązanych z celem do którego został stworzony.

Jedną z głównych zalet XML, to możliwość silnego typizowania dokumentów (schematy XSD). Możemy dokonywać weryfikacji ich poprawności pod względem określonego schematu. Definiować elementy czy atrybuty jako obowiązkowe lub opcjonalne. Narzucać ograniczenia dla wartości – podobnie jak w konstrukcjach CHECK CONSTRAINT (np. zakresy). Standard ten jest bardzo elastyczny i niezależny od platformy.

Wymianę danych pomiędzy systemami najwygodniej realizować za jego pomocą, choć czasem może być postrzegany jako mocno nadmiarowy. Obecnie większość urządzeń udostępnia na przykład swoje dane konfiguracje, czy zbierane informacje w postaci plików XML.


XML w SQL Server

Podstawowe funkcjonalności związane z XML, pojawiły się już w SQL Server 2000 (np. funkcja OPENXML czy FOR XML). W późniejszych edycjach (od wersji 9 – 2005), XML stał się pełnoprawnym, natywnie wspieranym typem danych. Jego implementacja w SQL Server to szereg dedykowanych funkcjonalności takich jak wbudowane metody tworzenia dokumentów XML, specjalne typy indeksów czy mechanizmy przeszukiwania FLWOR (XQuery).

Przetwarzanie obiektów XML, jest wykonywane w oparciu o jego hierarchiczną strukturę drzewa. Jest to znacznie bardziej efektywny sposób niż metody przeszukiwania tekstów.

Natywny typ danych XML, możemy stosować w różnych miejscach np.

  • przy określaniu typu kolumn w definicjach tabel – do składowania dokumentów XML w bazie relacyjnej.
  • w definicji zmiennych – do przetwarzania dokumentów.
  • jako wartości zwracanych przez funkcję lub parametrów procedur składowanych.

Mamy możliwość tworzenia dokumentów XML bezpośrednio z postaci relacyjnej. Służą do tego polecenia FOR XML, rozszerzające składnię języka SQL. Ale nie o nich jest ten artykuł 🙂

W drugą zaś stronę – z postaci XML do postaci relacyjnej, możemy dokonać przekształceń za pomocą funkcji OPENXML() lub wbudowanych metod typu XML. Wykorzystując wyrażenia XPath i język zapytań XQuery. W artykule tym ograniczam się właśnie do omówienia sposobów, pobierania interesujących nas danych z dokumentów XML.


Przykład dokumentu XML

Jednymi z publicznie dostępnych dokumentów XML, na których zaprezentuję kilka przykładów zapytań, są publikowane przez NBP, aktualne kursy walut. Jest to typowe zastosowanie tego standardu, służące do przekazywania danych pomiędzy różnymi systemami. Pobierać je może każdy i niezależnie od platformy, przetwarzać zgodnie z własnymi potrzebami.

Fragment takiego pliku poniżej :

Odpytywanie_XML_w_SQL_01

Kilka słów na temat samej konstrukcji. Generalnie XML, daje dużą swobodę użytkownikowi w definiowaniu własnych dokumentów. Możemy określać dowolną strukturę za pomocą elementów oraz związanych z nimi atrybutów

 wartość_elementu 

Każdy z nich może mieć przypisaną wartość, a ich typ, zakres czy charakter (obowiązkowy / opcjonalny) może być określony schematem.

Poprawny dokument XML musi posiadać korzeń. W naszym przykładzie jest to element nadrzędny o nazwie <tabela_kursow>. XML jest zawsze ściśle określony, stąd wrażliwy jest na małe / wielkie litery (case sensitive) oraz konieczność zamykania znaczników elementów :

  wartość 

Poza tymi podstawowymi elementami konstrukcji, w typowym dokumencie XML określane są standardy kodowania, przestrzenie nazw (namespaces), czy powiązania do schematów, opisujących jego strukturę. W naszym przykładzie, mamy bardzo prosty dokument i w jego definicji te elementy nie są określone.


Przeszukiwanie pliku XML w SQL

Pierwszym przykładem, będzie odpytywanie pliku XML z poziomu T-SQL za pomocą funkcji OPENXML i prostych wyrażeń XPath. Wykorzystam do tego dokument XML, który możemy pobrać ze strony zasobów NBP – średnie kursy walut opublikowane 14 marca 2014.

Załóżmy, że chcemy przekształcić taki dokument na postać relacyjną – czyli zasilić naszą bazę, aktualnymi kursami walut.

Schemat tabeli, przechowującej kursy walut w postaci relacyjnej, będzie wyglądał tak :

USE TEMPDB
GO

CREATE TABLE [dbo].[KursyWalut](
	[Kod_Waluty] [char](3) NOT NULL,
	[Data] [date] NOT NULL,
	[Kurs_Sredni] [smallmoney] NOT NULL,
	[Przelicznik] [smallint] NOT NULL DEFAULT 1,

	 CONSTRAINT [PK_KursyWalut] PRIMARY KEY CLUSTERED 
	(
		[Kod_Waluty] ASC,
		[Data] ASC
	)
)

Oczywiście budując aplikację, należałoby zasilać bazę codziennie. Można to zrealizować na kilka sposobów, korzystając z paczek SSIS, własnych funkcji CLR lub pisząc proste skrypty w Visual Basic’u. W naszym przykładzie, zakładam, że mamy dostępny dokument XML na lokalnym serwerze na dysku D.

Załadowanie pliku XML do zmiennej w T-SQL

Pierwszym krokiem będzie wrzucenie pliku XML na serwer i załadowanie go do zmiennej z poziomu T-SQL. Wykorzystamy do tego celu funkcję OPENROWSET().

DECLARE @xmldata XML

SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1

SELECT @xmldata as Dokument

Odpytywanie_XML_w_SQL_02

Cały dokument, został załadowany do zmiennej typu XML. Możemy go wrzucić bezpośrednio do odpowiedniej kolumny w tabeli lub wyciągnąć z niego tylko istotne dla nas informacje.

Obiekty typu XML, wyświetlane są w Management Studio w postaci linku. Możemy podejrzeć taki dokument, po prostu klikając w taki link.
Odpytywanie_XML_w_SQL_03


Czytanie pliku XML za pomocą OPENXML()

Funkcja OPENXML jest dość wiekowa i dostępna była już w SQL Server 2000. Są z nią bezpośrednio związane dwie specjalne procedury składowane : sp_xml_preparedocument oraz sp_xml_removedocument.

OPENXML pozwala na proste szatkowanie dokumentu XML do postaci relacyjnej. Wyciąganie interesujących danych z XML i np. ładowanie ich do tabeli.

Algorytm pracy z tą funkcją obejmuje 3 kroki :

  1. Przygotowanie dokumentu – czyli załadowanie całego (!) do pamięci CACHE serwera. Służy do tego procedura sp_xml_preparedocument, do której przekazujemy jako parametr, dokument XML. Zwraca ona „uchwyt” do dokumentu – wskaźnik do pamięci CACHE pod którym jest on dostępny.
  2. Właściwe przeszukiwanie dokumentu za pomocą OPENXML, wykorzystując proste wyrażenia XPath do nawigacji po węzłach.
  3. Sprzątanie pamięci CACHE – zwalnianie zasobów za pomocą sp_xml_removedocument. Pamiętajmy o tym, żeby nie pozostawiać śmieci.

Z kolei funkcja OPENXML() przyjmuje 3 (opcjonalnie 4) parametry :

  • Pierwszym jest „uchwyt” (wskaźnik) do dokumentu w pamięci Cache Servera (zwracany przez sp_xml_preparedocument).
  • Drugim, ścieżka (XPath), wskazująca na punkt odniesienia jaki będzie stosowany w pobieraniu danych. Do tego miejsca w strukturze, będziemy mogli odnosić się w bloku WITH() tej funkcji.
  • Trzecim, flaga wskazująca na typ obiektów (elementy, atrybuty czy ich mix), których wartości chcemy zwrócić.
  • Czwartym, opcjonalnym jet definicja przestrzeni nazw

Dodatkowo, z funkcją OPENXML, powiązany jest blok WITH(), w którym możemy nawigować po elementach i atrybutach (z wykorzystaniem XPath), wyciągając z XMLa interesujące nas wartości.

Napiszmy więc skrypt, pobierający dane z pliku XML. Zgodnie z opisanym wcześniej algorytmem, przygotujemy dokument do obróbki, wyciągniemy interesujące nas informacje i wrzucimy je do tabeli dbo.KursyWalut. Na koniec zwolnimy zajęte przez nasz dokument zasoby w pamięci CACHE.

DECLARE @xmldata XML

-- 0. Załadowanie pliku do zmiennej
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1

-- 1. Przygotowanie dokumentu
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldata

-- 2. OPENXML - przyjmujemy za punkt odniesienia /tabela_kursow/pozycja
-- interesują nas wartości elementów (flaga = 2) i ładujemy do tabeli dbo.KursyWalut

INSERT INTO dbo.KursyWalut
SELECT 
	kod_waluty, Data_publikacji,
	cast ( Replace(kurs_sredni , ',','.') as smallmoney ) as Kurs, przelicznik
FROM
OPENXML(@docHandle, '/tabela_kursow/pozycja', 2)
WITH
(	
	data_publikacji varchar(10) '../data_publikacji',   -- poziom w górę
	kod_waluty	char(3) ,
	kurs_sredni	varchar(100) ,
	przelicznik int
)

-- 3. Sprzątanie pamięci 
EXEC sp_xml_removedocument @docHandle
GO

SELECT * from dbo.KursyWalut

Fragment zawartości tabeli dbo.KursyWalut, po załadowaniu danych z pliku XML :

Odpytywanie_XML_w_SQL_04

Nawigacja po węzłach za pomocą wyrażeń XPath, jest dość intuicyjna i podobna do określania ścieżki np. w systemie plików czy obiektach HTMLowych. Zresztą standard HTML wywodzi się z tej samej rodziny (SGML).


Przeszukiwanie XML za pomocą natywnych metod

Zaprezentowana funkcja OPENXML, jest bardzo uboga w porównaniu z pełną implementacją typu danych XML.

Typ XML, posiada pięć wbudowanych metod, które dają szerokie możliwości jego przeszukiwania i modyfikacji. Wszystkie bazują na języku zapytań XQuery i związanych z nim wyrażeniach FLWOR. Działanie pierwszych trzech, które stosuje się typowo do wyciągania informacji z dokumentów XML zaprezentuję na przykładach.

Typ danych XML posiada następujące, natywne metody :

  • value() – zwraca pojedynczą wartość, wynik zapytania XQuery w postaci skalarnego typu danych.
  • query() – zwraca fragment (wycinek) dokumentu XML.
  • nodes() – szatkuje dokument XML na wiele mniejszych, podrzędnych.
  • exists() – przeszukuje dokument XML pod kątem istnienia w nim określonych elementów. Zazwyczaj stosowana w budowaniu warunków, wyrażeń logicznych w WHERE.
  • modify() – służy do aktualizacji dokumentu XML.

Zapytanie do dokumentu XML, pokazane w poprzednim przykładzie za pomocą funkcji OPENXML(), możemy zrealizować wykorzystując znacznie szybsze (!), natywne metody value() oraz nodes(). Ich przewagę widać na pierwszy rzut oka. Są czytelniejsze i bardziej wygodne. Prawdziwą swoją moc, pokażą gdy zastosujesz dodatkowo, specjalne typy indeksów XML.

Alternatywne zapytanie do dokumentu XML, pobierające dane za pomocą metod wbudowanych w typ XML, będzie wyglądało tak :

DECLARE @xmldata XML

SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
  
-- Zastosoowanie XQuery
-- meotda nodes() dzieli 

SELECT  
	Tabliczka.element.value('kod_waluty[1]','char(3)') as kod_waluty,
	Tabliczka.element.value('../data_publikacji[1]','varchar(10)') as data_publikacji,
	Cast( Replace(Tabliczka.element.value('kurs_sredni[1]','varchar(10)'),',','.') as smallmoney)  as kurs_sredni,
	Tabliczka.element.value('przelicznik[1]','int') as przelicznik
      -- , Tabliczka.element.value('.././@typ[1]','varchar(10)') as Typ

FROM 
	@xmldata.nodes ('/tabela_kursow/pozycja') as Tabliczka(element)

-- metoda query() zwraca fragment dokumentu XML
SELECT  @xmldata.query('/tabela_kursow/pozycja[kod_waluty="USD"]/kurs_sredni') as Query

-- metoda value() – wartość skalarną
 SELECT  @xmldata.value('(/tabela_kursow/pozycja[kod_waluty="USD"]/kurs_sredni)[1]',
                                                              'varchar(10)') as Value

Odpytywanie_XML_w_SQL_05

Działanie metody nodes() jest podobne do funkcji tabelarycznych, dlatego możemy ją stosować we FROM. Poszatkowała nam ona dokument XML, na zbiór mniejszych elementów. Żeby lepiej sobie to wyobrazić, każdy z nich mógłby wyglądać mniej więcej tak :

    dolar amerykański
    1
    USD
    3,0481

Upraszcza to sposób przeszukiwania i odwoływania do fragmentów dokumentu XML.

Co bardzo ważne – tablica elementów (Tabliczka) powstałych na wskutek działania nodes(), nie stanowi osobnych dokumentów. Każdy z nich jest przetwarzany w kontekście dokumentu głównego. Lepszą interpretacją działania nodes() jest utworzenie wektora węzłów (wskaźników) wewnątrz dokumentu głównego.

W ramach węzłów stworzonych przez tą funkcję, możemy sięgać do dowolnych elementów np. wyżej w hierarchii – wartości data_publikacji czy atrybutu typ (jest on zakomentowany w tym przykładzie).

Dwie kolejne kwerendy to proste przykłady zastosowania metod query() oraz value() do przeszukiwania elementów o określonej wartości.


Przeszukiwanie dokumentów XML z określoną przestrzenią nazw

Większość plików XML ma określoną przestrzeń nazw. Obydwie zaprezentowane w tym artykule metody pobierania danych z dokumentów XML, obsługują oczywiście przestrzenie nazw.

Korzystając z natywnych metod, możemy je definiować na różne sposoby. Każde z poniższych zapytań, zwróci poprawny rezultat :

USE AdventureWorks2008
GO

-- sposób 1
SELECT  
 hj.Resume.value(
 'declare namespace MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
 (/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]' ,'nvarchar(100)')  as FirstName,
 hj.Resume.value(
 'declare namespace MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
 (/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]' ,'nvarchar(100)')  as LastName
FROM [HumanResources].[JobCandidate] hj;
 
-- sposób 2
WITH XMLNAMESPACES (N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' as MyNS)
SELECT
 hj.Resume.value(
 '(/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]' ,'nvarchar(100)')  as FirstName,
 hj.Resume.value(
 '(/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]' ,'nvarchar(100)') as LastName
FROM [HumanResources].[JobCandidate] hj;

-- sposób 3
WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' )
SELECT hj.Resume.value(
 '(/Resume/Name/Name.First)[1]' ,'nvarchar(100)')  as FirstName,
  hj.Resume.value(
 '(/Resume/Name/Name.Last)[1]' ,'nvarchar(100)') as LastName
FROM [HumanResources].[JobCandidate] hj

Odpytywanie_XML_w_SQL_07

Z kolei definiowanie przestrzeni nazw w przykładzie wykorzystującym funkcję OPENXML, sprowadza się do przekazania tej informacji w momencie przygotowania dokumentu, czyli wywołania procedury sp_xml_preparedocument. Przyjmuje ona opcjonalnie dodatkowy parametr.

Ponieważ funkcja OPENXML(), jest ściśle skojarzona z procedurami składowanymi, zastosowanie jej w celu osiągnięcia podobnego rezultatu (czytania wielu obiektów XML), wymaga działania na kursorach.

Widać więc, że nie jest to wygodna ani efektywna metoda, przeszukiwania dokumentów XML składowanych w tabeli. Być może wydawać się prostsza lub dla niektórych, (historycznie) bardziej bliska. Polecam jednak metody natywne, bo są bardziej uniwersalne i z pewnością szybsze.

Przetworzenie pierwszego dokumentu XML z określoną przestrzenią nazw za pomocą OPENXML, może wyglądać tak :

DECLARE @idoc int, @rootxmlns varchar(100)
DECLARE @doc xml 
 
SELECT TOP 1 @doc=Resume
FROM [HumanResources].[JobCandidate] hj

SET @rootxmlns = ''
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, @rootxmlns
 
SELECT    *
FROM       OPENXML (@idoc, '/MyNS:Resume/MyNS:Name ',1)
WITH
(	
	FirstName nvarchar(100)  './MyNS:Name.First',
	LastName nvarchar(100) './MyNS:Name.Last'
)
		
EXEC sp_xml_removedocument @idoc
GO

Odpytywanie_XML_w_SQL_08


Podsumowanie

Na temat języka zapytań XQuery oraz samego XML’a można napisać wiele artykułów. Z założenia, rozdział ten miał być zwięzłym wprowadzeniem i prezentować gotowe rozwiązania kwerend do dokumentów XML. Dlatego ograniczyłem się tylko do kilku podstawowych przykładów z wykorzystaniem funkcji OPENXML oraz metod typu danych XML – nodes(), value() oraz query(). Jeśli szukasz szerszej wiedzy na ten temat XML – zapraszam na moje kursy.

One Response

  • Generalnie w XML przekazywane są właściwie całe tabele, do ich odczytu wystarczy używać poleceń typu:
    SELECT T2.Loc.query(’.’)
    FROM T
    CROSS APPLY Instructions.nodes(’/root/Location’) as T2(Loc)

    Nie trzeba listować nazw zmiennych.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.