Ostatnimi czasy naobiecywałem paru osobom, że coś napiszę na temat XML-a w SQL Server. Ponieważ ostatnio jakoś ten temat powraca do mnie jak bumerang, postanowiłem sklecić coś korzystając z długiego weekendu (mniam!).
Impresja nr 1 – Zagnieżdżenia
Zacznę od czegoś w miarę prostego (acz nie do końca banalnego). Często zdarza się, że muszę w T-SQL wydobywać dane z dokumentów XML. Sprawa jest prosta, jeśli a) znam strukturę dokumentu, b) struktura dokumentu jest w miarę "płaska" (tzn. elementy zawierające dane znajdują się na jednym poziomie hierarchii dokumentu XML). Punktem a) zajmę się w impresji nr 2. A punkt b)?
Przyjrzyjmy się dokumentowi:
DECLARE @doc xml;
SET @doc = '<orders>
<order orderid="1">
<orderdate>20090610</orderdate>
<orderdetails>
<orderetail>
<product>Mac</product>
<unitprice>10.00</unitprice>
<quantity>1</quantity>
</orderdetail>
<orderetail>
<product>PC</product>
<unitprice>10.00</unitprice>
<quantity>1</quantity>
</orderdetail>
</orderdetails>
</order>
<order orderid="2">
<orderdate>20090610</orderdate>
<orderdetails>
<orderetail>
<product>Mac</product>
<unitprice>10.00</unitprice>
<quantity>3</quantity>
</orderdetail>
<orderetail>
<product>PC</product>
<unitprice>10.00</unitprice>
<quantity>5</quantity>
</orderdetail>
</orderdetails>
</order>
</orders>';
Ot, dane zamówień. Jak wydobyć z takiego dokumentu wszystkie dane i zrobić z tego na początek tabelę zdenormalizowaną? Wiadomo, że przejście od XML-a do tabeli od SQL Server 2005 można zrealizować za pomocą metod typu danych XML (w szczególności za pomocą metody nodes()). I w takim, zagnieżdżonym przypadku, też można poradzić sobie w ten sam sposób. Tyle, że metody nodes() trzeba użyć wielokrotnie. Na początek wydobywamy ogólne dane zamówienia, a potem dane szczegółów zamówień.
SELECT
orders_detailed.orderid,
orders_detailed.orderdate,
order_details.order_detail.value('product[1]', 'varchar(20)') AS product,
order_details.order_detail.value('unitprice[1]', 'varchar(20)') AS unitprice,
order_details.order_detail.value('quantity[1]', 'varchar(20)') AS quantity
FROM (
SELECT
orders.single_order.value('@orderid', 'int') AS orderid,
orders.single_order.value('orderdate[1]', 'smalldatetime') AS orderdate,
orders.single_order.query('orderdetails') AS order_details
FROM @doc.nodes('/orders/order') AS orders(single_order)
) AS orders_detailed
CROSS APPLY orders_detailed.order_details.nodes('/orderdetails/orderdetail') AS order_details(order_detail)
Wynik:
orderid orderdate product unitprice quantity
------- ------------------- ------- --------- --------
1 2009-06-10 00:00:00 Mac 10.00 1
1 2009-06-10 00:00:00 PC 10.00 1
2 2009-06-10 00:00:00 Mac 10.00 3
2 2009-06-10 00:00:00 PC 10.00 5
Impresja nr 2 – Parsowanie XML-a
Co jednak, jeśli postawimy przed sobą trudniejsze zadanie? Na przykład: nie znając struktury dokumentu XML zróbmy z niego tabelę. To już wymaga nie lada wysiłku. Zastanawiałem się wiele razy, jak do tego podejść. Niezłym rozwiązaniem wydało mi się przerobienie dokumentu XML na tabelę zawierającą informacje o węzłach dokumentu. Spróbuję zaprezentować to na przykładzie. Mamy dokument:
DECLARE @doc xml;
SET @doc = '<employees>
<employee empid="1">
<firstname>Pawel</firstname>
<lastname>Potasinski</lastname>
</employee>
<employee empid="2">
<firstname>Jan</firstname>
<lastname>Kowalski</lastname>
</employee>
</employees>'
Spróbuję za pomocą kodu T-SQL zbudować tabelę pokazującą hierarchię tego dokumentu. Ale jednocześnie zapytanie będzie na tyle uniwersalne, że poradzi sobie z innym dokumentem XML (nie gwarantuję, że z każdym, bo pewnie tak nie jest).
;WITH CTE AS (
SELECT
Q.a.value('local-name(.)', 'varchar(100)') AS node,
CASE WHEN Q.a.exist('child::*') = 0
THEN Q.a.value('text()[1]', 'varchar(8000)') ELSE Q.a.query('child::*') END AS children_or_text,
CASE WHEN Q.a.exist('child::*') = 1 THEN 1 ELSE 0 END AS has_children,
0 AS level,
CAST('/' AS varchar(8000)) AS Path,
1 AS is_element
FROM @doc.nodes('//*[count(parent::*) = 0]') AS Q(a)
UNION ALL
SELECT
Q.a.value('local-name(.)', 'varchar(100)'),
CASE WHEN Q.a.exist('child::*') = 0
THEN Q.a.value('text()[1]', 'varchar(8000)') ELSE Q.a.query('child::*') END,
CASE WHEN Q.a.exist('child::*') = 1 THEN 1 ELSE 0 END,
level + 1,
CAST(Path + CONVERT(varchar(20), ROW_NUMBER() OVER (ORDER BY Q.a)) + '/' AS varchar(8000)),
1
FROM CTE
CROSS APPLY CTE.children_or_text.nodes('//*[count(parent::*) = 0]') AS Q(a)
UNION ALL
SELECT
Q.a.value('local-name(.)', 'varchar(100)'),
Q.a.value('.', 'varchar(8000)'),
0,
level + 1,
CAST(Path + CONVERT(varchar(20), ROW_NUMBER() OVER (ORDER BY Q.a)) + '/' AS varchar(8000)),
0
FROM CTE
CROSS APPLY CTE.children_or_text.nodes('//*[count(parent::*) = 0]/@*') AS Q(a)
)
SELECT *
FROM CTE
ORDER BY Path
OPTION (MAXRECURSION 0)
Wynik zapytania:
Mała legenda:
- node - nazwa elementu lub atrybutu,
- children_or_text - zawartość elementu lub atrybutu (elementy "dzieci" lub tekst),
- has_children - czy węzeł ma "dzieci" (elementy zagnieżdżone),
- level – poziom zagnieżdżenia węzła w dokumencie XML,
- Path – ścieżka węzła (idealna do rzutowania na hierarchyid),
- is_element – czy element (jeśli 0, to węzeł jest atrybutem).
Samo zapytanie to CTE rekurencyjne. W pierwszej iteracji CTE wybiera elementy nie mające "rodzica" (tu użyłem wyrażenia XPath - //*[count(parent::*) = 0]). W następnych iteracjach wybierane są elementy z kolejnych poziomów zagnieżdżeń, a także atrybuty. CTE kończy iteracje po dojściu do najbardziej zagnieżdżonego elementu. Kluczem do "zwycięstwa" jest użycie metody local-name (funkcja ze specyfikacji XQuery). Metoda ta umożliwia wydobycie z dokumentu XML nazw elementów i atrybutów.
Ok, ale teraz pytanie, jak z tego zrobić tabelę np. takiej postaci:
empid firstname lastname
----- --------- --------
1 Pawel Potasinski
2 Jan Kowalski
Odpowiedź: PIVOT :-) A w zasadzie - dynamiczny PIVOT. Proponuję się pobawić samemu – można użyć np. procedury pivot_sp autorstwa Erlanda Sommarskoga (SQL Server MVP). Wystarczy odfiltrować zbędne wiersze (takie, gdzie has_children = 1) i skupić się na kolumnach node oraz children_or_text.
Impresja nr 3 – Operacje na napisach
Zaskakujące, w jakich momentach przydaje się typ danych XML w SQL Server. Jednym z takich zastosowań są operacje na napisach – konkatenacja oraz rozdzielanie wartości oddzielonych wybranym separatorem.
Konkatenację realizuje się używając klauzuli FOR XML PATH:
SELECT DISTINCT
collation_name,
STUFF(
(
SELECT ', ' + name
FROM sys.databases
WHERE collation_name = d.collation_name
ORDER BY name
FOR XML PATH('')
),1,2,'') AS databases
FROM sys.databases AS d
Powyższe zapytanie pokaże listę collation użytych w bazach danych oraz – w drugiej kolumnie – listę baz, w których dane collation jest używane, np.
collation_name databases
--------------------------- --------------------------------------
Latin1_General_CI_AS_KS_WS ReportServer, ReportServerTempDB
Polish_CI_AS master, model, msdb, Northwind, tempdb
Dodanie literału (tu – przecinek i spacja) eliminuje zbędny element XML w podzapytaniu, a pusty napis w klauzuli FOR XML PATH powoduje, że nie pojawia się element pochodzący od nazwy tabeli. Dzięki temu powstaje odpowiedni łańcuch tekstowy. Funkcja STUFF pozwala w prosty sposób pozbyć się pierwszego przecinka i spacji (są zbędne).
O ile użycie typu danych XML do wykonywania konkatenacji jest dość znane (można je znaleźć choćby w książce Itzika Ben-Gana "Inside SQL Server 2005 Programming" w kodzie procedury do tworzenia dynamicznego PIVOT-a), to już wykorzystanie tego typu danych do rozdzielania wielu wartości względem wybranego separatora (tzw. split) jest wg mnie naprawdę nieszablonowym rozwiązaniem. Zobaczmy taką funkcję:
IF OBJECT_ID('dbo.ufn_Split') IS NOT NULL
DROP FUNCTION dbo.ufn_Split;
GO
CREATE FUNCTION dbo.ufn_Split(@Input nvarchar(max), @Separator nchar(1))
RETURNS TABLE
AS
RETURN (
WITH CTE AS
(
SELECT
CAST(
N'<Value>' +
REPLACE(@Input, @Separator, N'</Value><Value>') +
N'</Value>' AS XML
) AS Items
)
SELECT
Split.a.value('.', 'nvarchar(max)') AS Item
FROM CTE
CROSS APPLY Items.nodes('/Value') Split(a)
);
GO
-- Przykład użycia
SELECT * FROM dbo.ufn_Split('1,2,3,4',',')
Ciekawe, prawda? To rozwiązanie podejrzałem kiedyś na Experts Exchange. Pewnie, można się przyczepić, że rozwiązanie bazuje na założeniu, że w podanym na wejściu łańcuchu nie wystąpi napis "<Value>" lub "</Value>", ale to chyba niewielki problem (nazwę elementu XML można zmienić / skomplikować). Od razu zaznaczam, że nie testowałem też, jak powyższe rozwiązanie wypada wydajnościowo przy odpowiedniku z użyciem pętli WHILE lub z wykorzystaniem CLR.
Suma sumarum
Jak widać, XML-em w systemie SQL Server można się fajnie "bawić". Znajomość typu danych XML oraz jego metod niejednokrotnie daje szansę na stworzenie ciekawego rozwiązania dla problemu. Następnym razem, gdy będę pisał o XML-u w SQL Server, zapewne poruszę temat indeksów XML. Ale to już zapewne po sezonie urlopowym ;-)