Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] Impresje na temat XML w SQL Server

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:

image

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 ;-)

Opublikowane 14 czerwca 2009 01:11 przez brejk
Filed under: ,

Komentarze:

# re: [PL] Impresje na temat XML w SQL Server

14 czerwca 2009 11:12 by marekpow

Impresja nr 2. Wymiatasz!

# re: [PL] Impresje na temat XML w SQL Server

14 czerwca 2009 17:06 by marekpow

Jednak w nr 2 jest problem z atrybutami. Atrybuty powielają sie tyle razy na ile poziomów jest zagłębieny dany atrybut.

# SQLGEEK : [PL] Impresje na temat XML w SQL Server

14 czerwca 2009 17:36 by dotnetomaniak.pl

Dziękujemy za publikację - Trackback z dotnetomaniak.pl

# re: [PL] Impresje na temat XML w SQL Server

14 czerwca 2009 17:39 by Wojciech Gebczyk

Pawel, Nooo gratulacje ze chcialo ci sie stukac tle koda :P

Tak przy okazji zaraz kojarzy mi sie z potforkami w stylu SP generujace kawal HTMLa :P

# re: [PL] Impresje na temat XML w SQL Server

14 czerwca 2009 20:30 by brejk

@Wojtek: I co ciekawe, może się okazać, że ten kod nie jest bez sensu, bo moi ziomale w firmie sporo XML-a produkują i transformują :-)

@Marekpow: Wiem, że ten kod jest niedoskonały, ale to dobry początek. Bawiłem się tym ledwie dzień po tym, jak odkryłem piękno metody local-name ;-)

# re: [PL] Impresje na temat XML w SQL Server

14 czerwca 2009 22:28 by marekpow

Masz na myśli local-name(.)? Mnie się bardzo spodobało local-name(..)

# re: [PL] Impresje na temat XML w SQL Server

19 sierpnia 2009 15:40 by OpenRowset

Nie chcę odgrzewać kotleta tylko ostrzec. Split w sposób napisany powyżej jest oczywiście ciekawy ale silnie niewydajny. O wiele wydajniejszy jest sposób z tabelką z liczbami (wliczając w to jej powstanie) i zwykłym joinem (abstrahując od niedogodności tej metody).

# re: [PL] Impresje na temat XML w SQL Server

20 sierpnia 2009 14:47 by brejk

@OpenRowset: Dzięki za informację. Spodziewałem się tego (ciężki typ XML + metody XQuery to nie są demony prędkości), ale byłem zbyt leniwy, by przetestować to rozwiązanie. W rozwiązaniach własnych tego nie używam.

# [PL] Indeksy XML w SQL Server 2008 od środka

15 grudnia 2009 09:36 by SQLGEEK

Jakiś czas temu pisałem o moich &quot;zabawach&quot; z typem danych XML w SQL Server. Od tamtego czasu

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek