Dzisiejszy wpis poświęcę kontynuacji zagadnień związanych z XML w T‑SQL. W jednym z poprzednich wpisów pokazałem w jaki sposób można pobrać dane z bazy w postaci kodu XML. Dzisiaj chciałbym zaprezentować w jaki sposób poradzić sobie z zadaniem odwrotnym, czyli odczytywaniem takich danych i zapisywaniem ich w bazie.
Zanim przejdziemy dalej utwórzmy sobie do zabawy kilka tabel tymczasowych i dodajmy do nich trochę danych.
create table #klasy (
idKlasy int identity(1, 1) primary key clustered,
nazwa varchar(2)
);
create table #uczniowie (
idUcznia int identity (1, 1) primary key clustered,
idKlasy int not null foreign key references #klasy(idKlasy),
imie nvarchar(50) not null,
nazwisko nvarchar(100) not null
);
go
insert into #klasy (nazwa)
values ('1A'), ('1B');
insert into #uczniowie(idKlasy, imie, nazwisko)
values (1, N'Anna', N'Nowak'), (1, N'Adam', N'Kowalski'), (1, N'Monika', N'Mickiewicz'),
(2, N'Marcel', N'Mieszkowski'), (2, N'Ewa', N'Tarczyńska'), (2, N'Filip', N'Konopski');
go
Aby zaprezentować te dane w formacie XML wykonajmy poniższe zapytanie:
declare @xml xml;
select @xml = ( select klasa.idKlasy,
klasa.nazwa,
( select uczen.idUcznia,
uczen.imie,
uczen.nazwisko
from #uczniowie as uczen
where uczen.idKlasy = klasa.idKlasy
order by nazwisko, imie
for xml auto, elements, type
) as uczniowie
from #klasy as klasa
for xml auto, root('klasy'), elements, type);
select @xml;
Jak nietrudno się domyślić wartość zmiennej @xml powinna wyglądać mniej więcej tak
<klasy> <klasa> <idklasy>1</idklasy> <nazwa>1A</nazwa> <uczniowie> <uczen> <iducznia>2</iducznia> <imie>Adam</imie> <nazwisko>Kowalski</nazwisko> </uczen> <uczen> <iducznia>3</iducznia> <imie>Monika</imie> <nazwisko>Mickiewicz</nazwisko> </uczen> <uczen> <iducznia>1</iducznia> <imie>Anna</imie> <nazwisko>Nowak</nazwisko> </uczen> </uczniowie> </klasa> <klasa> <idklasy>2</idklasy> <nazwa>1B</nazwa> <uczniowie> <uczen> <iducznia>6</iducznia> <imie>Filip</imie> <nazwisko>Konopski</nazwisko> </uczen> <uczen> <iducznia>4</iducznia> <imie>Marcel</imie> <nazwisko>Mieszkowski</nazwisko> </uczen> <uczen> <iducznia>5</iducznia> <imie>Ewa</imie> <nazwisko>Tarczyńska</nazwisko> </uczen> </uczniowie> </klasa> </klasy>
Załóżmy teraz, iż pracownik naszej małej szkoły pobrał sobie w jakiejś aplikacji spis powyższy klas wraz z uczniami i będzie nanosił na nim zmiany. Przyjmijmy, że ów pracownik ma za zadanie wprowadzić kilka modyfikacji do naszej bazy:
- poprawić nazwisko Marcela Mieszkowskiego na Mieszakowskiego,
- dodać nową uczennicę Iwonę Piekarską do klasy 1B,
- przygotować nową klasę 1C i zapisać do niej nowego ucznia Michała Młynarskiego.
Pomyślmy teraz ile operacji na bazie danych musiałaby wykonać owa aplikacja aby wprowadzić powyższe zmiany:
- wykonanie operacji UPDATE na tabeli uczniów w rekordzie ucznia Marcela,
- wykonanie operacji INSERT na tabeli uczniów aby dodać nową uczennicę Iwonę do klasy 1B,
- wykonanie operacji INSERT na tabeli klas aby dodać klasę 1C (i pobrać sobie od razu indeks nowego rekordu, który będzie potrzebny do kolejnej operacji),
- wykonać operację INSERT na tabeli uczniów aby dodać nowego ucznia Michała do nowo utworzonej klasy 1C.
Uogólniając można powiedzieć, że musimy wykonać cztery operacje. Jeśli zbierzemy sobie wszystkie inserty razem to damy radę ograniczyć ich liczbę do trzech. Czy można jednak wykonać wszystkie te zmiany przy pomocy tylko jednego zapytania (w sensie batcha)? Ktoś mógłby powiedzieć – no pewnie, że można. Wystarczy przecież napisać sobie np. coś takiego
update #uczniowie
set nazwisko = N'Mieszakowski'
where idUcznia = 4;
declare @noweIdTab table (idKlasy int);
declare @noweId int;
insert into #klasy (nazwa)
output inserted.idKlasy
into @noweIdTab
values ('1C');
select top 1 @noweId = idKlasy
from @noweIdTab;
insert into #uczniowie(idKlasy, imie, nazwisko)
values (1, N'Iwona', N'Piekarska'),
(@noweId, N'Michał', N'Młynarski');
Działa? No pewnie, że działa. Jest jedno zapytanie? No jest. Jak się jednak zapewne domyślasz drogi czytelniku nie chodziło mi o aż tak „sprytne” rozwiązanie 🙂 Przede wszystkim tworząc takie zapytanie musimy wiedzieć wcześniej jakie dane chcemy dodawać, usuwać bądź aktualizować. Czy można jednak wykonać te wszystkie operacje w sposób uniwersalny, czyli bez wcześniejszej wiedzy o tym co i jak trzeba zmienić w bazie? Czy można napisać sobie w tym celu np. jedną procedurę zawierającą wyłącznie jeden argument – czyli rozpalić wspomniane w tytule ognisko jedną zapałką? Otóż chcę pokazać, że można i jeśli zaciekawiłem Cię tym drogi czytelniku to zapraszam do kontynuowania czytania tego wpisu 🙂
Przyjmijmy, że jeśli aplikacja obsługująca klasy zwraca dane w formacie XML to i w takim formacie je przyjmuje. W efekcie wprowadzonych modyfikacji otrzymalibyśmy dokument XML o poniższej strukturze
<klasy> <klasa> <idklasy>1</idklasy> <nazwa>1A</nazwa> <uczniowie> <uczen> <iducznia>2</iducznia> <imie>Adam</imie> <nazwisko>Kowalski</nazwisko> </uczen> <uczen> <iducznia>3</iducznia> <imie>Monika</imie> <nazwisko>Mickiewicz</nazwisko> </uczen> <uczen> <iducznia>1</iducznia> <imie>Anna</imie> <nazwisko>Nowak</nazwisko> </uczen> </uczniowie> </klasa> <klasa> <idklasy>2</idklasy> <nazwa>1B</nazwa> <uczniowie> <uczen> <iducznia>6</iducznia> <imie>Filip</imie> <nazwisko>Konopski</nazwisko> </uczen> <uczen> <iducznia>4</iducznia> <imie>Marcel</imie> <nazwisko>Mieszakowski</nazwisko> </uczen> <uczen> <iducznia>5</iducznia> <imie>Ewa</imie> <nazwisko>Tarczyńska</nazwisko> </uczen> <uczen> <iducznia></iducznia> <imie>Iwona</imie> <imie>Weronka</imie> <nazwisko>Piekarska</nazwisko> </uczen> </uczniowie> </klasa> <klasa> <idklasy></idklasy> <nazwa>1C</nazwa> <uczniowie> <uczen> <iducznia>99</iducznia> <imie>Michał</imie> <nezwisko>Młynarski</nezwisko> <opis>Uczeń przeniesiony</opis> </uczen> </uczniowie> </klasa> </klasy>
Zanim przejdziemy dalej chciałbym zwrócić uwagę na pewien interesujący fakt. Przyjrzyj się drogi czytelniku proszę powyższemu kodowi XML – szczególnie nowym elementom, które dodajemy. Struktura zawiera co prawda pola identyfikatorów, ale przecież nie znamy tych wartości w przypadku elementów, które dopiero chcemy dodać. Skoro nie znamy tych wartości to spróbujmy zostawić je puste lub dla „zabawy” użyjmy jakiejś nieistniejącej wartości. Skoro już tak kombinujemy to spróbujmy jeszcze przemycić dodatkowe pola do XML. Np. przy Michale Młynarskim dodajmy sobie pole opisu a przy Iwonie Piekarskiej spróbujmy przekazać jeszcze drugie imię używając tego samego elementu imie co dla pierwszego imienia (czyli będziemy mieli dwa elementy o takiej samej nazwie w danych ucznia). Dodatkowo w rekordzie Michała Młynarskiego zrobmy jeszcze literówkę w nazwie pola przechowującego nazwisko. Zobaczymy później do czego nas to wszystko doprowadzi 🙂 Możemy to potraktować jako sprawdzenie zachowania się naszego rozwiązania w przypadku błędnie uzupełnionej struktury (o co w prawdziwym życiu nie jest przecież wcale tak trudno 😉 ).
Czy odebranie takiego XML oznacza, że trzeba będzie parsować ten kod np. przy pomocy metody opisanej przeze mnie w tym wpisie? Jeśli ktoś chce to oczywiście można, ale ja chciałbym zaproponować przekazanie takiego XML wprost do zapytania T‑SQL i niech nasz serwer bazodanowy sam sobie z tym poradzi. W końcu jeśli baza MSSQL potrafi zwrócić dane w postaci kodu XML to można od niej oczekiwać, że również potrafi z takim kodem pracować 🙂
Warto również moim zdaniem dodać, iż jeśli aplikacja odbiera dane w postaci dokumentu XML to być może nie zna struktury tabel w bazie danych. Czy powinniśmy zatem zaimplementować jej tę wiedzę aby potrafiła wykonać interesujące nas operacje wprowadzania danych? Otóż przekazując dane jako XML nie musimy tego wcale robić. Posługiwanie się wyłącznie dokumentami XML mogłoby więc służyć do ukrycia przed aplikacjami klienckimi struktury w jakiej przechowywane są tak na prawdę dane.
Jeśli kogoś zaciekawiłem powyższym opisem to zapraszam teraz do dalszej części gdzie pokażę jak można poradzić sobie (a właściwie jak ja to robię 🙂 ) z problemem zapisu danych wykorzystując wyłącznie przekazany XML.
W pierwszej kolejności staram się przygotować sobie zmienne tabelaryczne (oczywiście można też użyć tabel tymczasowych), do których będę zczytywał dane z dokumentu XML. W naszym przypadku wykorzystam dwie takie zmienne jak w poniższym kodzie.
declare @klasy table (idKlasy int, nazwa varchar(2)); declare @uczniowie table (idUcznia int, idKlasy int, imie nvarchar(50), nazwisko nvarchar(100));
Definicje pól wyglądają znajomo, prawda? Zmienne te mają za zadanie „odebrać” dane z przekazanego XML więc siłą rzeczy muszą zawierać wszystkie pola jakie spodziewamy się tam znaleźć. Oczywiście może się zdarzyć, że będziemy potrzebować więcej pól pozwalających zapisać dodatkowe informacje o np. relacjach danych w strukturze XML, ale o takich przypadkach napiszę może w innym miejscu. Na razie skupmy się tylko na naszym problemie.
Czy można obyć się bez owych zmiennych? Oczywiście, że można. Czy jest to w jakiś sposób złe, że sam tego tutaj tak nie robię? Prawdę mówiąc nie robiłem nigdy analizy, które z podejść jest bardziej efektywne. Zależy mi jednak w tym miejscu na pokazaniu pewnych relacji między danymi i nie chcę niepotrzebnie mieszać w głowie komuś, dla kogo może być to coś nowego (staram się z tego powodu zaprezentować operacje przy pomocy w miarę prostych zapytań).
Nie zapomnijmy jeszcze o utworzeniu zmiennej, w której przekażemy nasz „argument”. Nazwijmy ją podobnie jak wcześniej po prostu @xml.
declare @xml xml = N' <klasy> <klasa> <idklasy>1</idklasy> <nazwa>1A</nazwa> <uczniowie> <uczen> <iducznia>2</iducznia> <imie>Adam</imie> <nazwisko>Kowalski</nazwisko> </uczen> <uczen> <iducznia>3</iducznia> <imie>Monika</imie> <nazwisko>Mickiewicz</nazwisko> </uczen> <uczen> <iducznia>1</iducznia> <imie>Anna</imie> <nazwisko>Nowak</nazwisko> </uczen> </uczniowie> </klasa> <klasa> <idklasy>2</idklasy> <nazwa>1B</nazwa> <uczniowie> <uczen> <iducznia>6</iducznia> <imie>Filip</imie> <nazwisko>Konopski</nazwisko> </uczen> <uczen> <iducznia>4</iducznia> <imie>Marcel</imie> <nazwisko>Mieszakowski</nazwisko> </uczen> <uczen> <iducznia>5</iducznia> <imie>Ewa</imie> <nazwisko>Tarczyńska</nazwisko> </uczen> <uczen> <iducznia></iducznia> <imie>Iwona</imie> <imie>Weronka</imie> <nazwisko>Piekarska</nazwisko> </uczen> </uczniowie> </klasa> <klasa> <idklasy></idklasy> <nazwa>1C</nazwa> <uczniowie> <uczen> <iducznia>99</iducznia> <imie>Michał</imie> <nezwisko>Młynarski</nezwisko> <opis>Uczeń przeniesiony</opis> </uczen> </uczniowie> </klasa> </klasy>';
Kiedy mamy już przygotowane zmienne tabelaryczne warto będzie zająć się wypełnieniem ich danymi. W pierwszej kolejności zajmijmy się przepisaniem danych dotyczących samych klas. Aby odczytać dane XML musimy posłużyć się funkcjami operującymi na tego typu danych. Samo zapytanie do pobrania informacji o klasach może wyglądać tak:
select t.c.value('number(idKlasy[1])', 'int') as idKlasy,
t.c.value('nazwa[1]', 'varchar(2)') as nazwa
from @xml.nodes('klasy/klasa') as t(c);
Mówiąc najprościej w zapytaniu tym w formule from potraktowaliśmy elementy klasy/klasa naszego dokumentu XML jako kolekcję, którą nazwaliśmy tajemniczo t(c). Owo t(c) to nic innego jak zdefiniowanie owych elementów (nodes) dokumentu XML jako tabeli (symbolizowaną przez literkę „t”) oraz kolumny (symbolizowaną przez literkę „c”). Mówiąc inaczej kazaliśmy serwerowi SQL zapisać kolekcję elementów XML jako kolumnę w tabeli, do której możemy odwoływać się później w zapytaniu poprzez wyrażenie t.c. Oczywiście zamiast owych dwóch literek możemy użyć innych, bardziej dostosowanych do naszych potrzeb nazw. Szczególnie przydatne będzie to podczas tworzenia złączeń danych pochodzących z tego samego bądź wielu dokumentów XML.
Od razu wyjaśnię jeszcze jeden, być może równie interesujący element. Zapewne zaciekawiło Cię czytelniku co to za „[1]” pojawiające się przy nazwie pola XML. Otóż chodzi tutaj o to, iż w dokumencie XML może znajdować się obok siebie wiele elementów o takiej samej nazwie. Nawet w naszym przypadku istnieje wiele równoległych elementów o nazwie klasa. Ów nawias kwadratowy mówi nam, które wystąpienie danego elementu w danym kontekście nas interesuje. Ponieważ zakładamy, że klasa ma jeden identyfikator oraz jedną nazwę to naturalne jest, że interesuje nas tylko pierwsze wystąpienie. Gdyby ktoś dodał np. więcej elementów nazwa to pozostałe zostaną przez nasze zapytanie najzwyczajnej zignorowane.
W efekcie wykonania powyższego kodu otrzymamy oczywiście taki rezultat (dla wygody dane prezentuję w postaci tekstowej a nie „grida”):
idKlasy nazwa ----------- ----- 1 1A 2 1B 3 1C
Czy dane te zgadzają się z tym co mieliśmy wcześniej w bazie? My wiemy co się tam święci – w końcu sami te dane tam wprowadziliśmy. Chcąc jednak pracować z nimi dalej musielibyśmy mieć jakiś wskaźnik, czy nie pojawiła się definicja żadnej nowej klasy. Jak to można osiągnąć? Można np. wykonać złączenie odebranych danych wraz z istniejącymi w naszej tabeli klasami i zobaczyć czy do siebie pasują. Aby to ułatwić zmieńmy nieco definicje naszych zmiennych tabelarycznych. Dodajmy im na końcu pomocnicze pole id>:
declare @klasy table (idKlasy int, nazwa varchar(2), id int); declare @uczniowie table (idUcznia int, idKlasy int, imie nvarchar(50), nazwisko nvarchar(100), id int);
Zapiszmy teraz informacje o klasach w następujący sposób:
insert into @klasy
select t.c.value('number(idKlasy[1])', 'int') as idKlasy,
t.c.value('nazwa[1]', 'varchar(2)') as nazwa,
k.idKlasy as id
from @xml.nodes('klasy/klasa') as t(c)
left join #klasy as k on k.nazwa = t.c.value('nazwa[1]', 'varchar(2)')
Proszę zwrócić uwagę, że w złączeniu nie sprawdzam identyfikatorów wybierając w zamian nazwę klasy, która identyfikuje ją w bardziej naturalny sposób. Jeśli pobierzemy sobie teraz dane ze zmiennej tabelarycznej @klasy naszym oczom ukaże się następujący rezultat:
idKlasy nazwa id ----------- ----- ----------- 1 1A 1 2 1B 2 3 1C NULL
Jak widać przy klasie „1C” pole id jest puste. Wynika to z faktu, iż elementu o nazwie „1C” nie ma w naszej tabeli z klasami. Co możemy z tak uzyskaną wiedzą zrobić? Możemy np. automatycznie uzupełnić tabelę z klasami o nowe wpisy.
insert into #klasy (nazwa) select nazwa from @klasy where id is null
Jak widać uzupełnienie kolekcji klas jest bardzo proste. Gdybyśmy również chcieli usunąć z tabeli informacje o klasach, które nie pojawiły się w dokumencie XML to moglibyśmy użyć zapytania:
delete from #klasy where nazwa not in (select nazwa from @klasy)
Oczywiście w naszym przypadku dobrze byłoby jeszcze posprzątać w tabeli uczniów – skoro jakaś klasa przestaje istnieć to trzeba usunąć z niej uczniów 🙂 Możemy to zrobić na kilka sposobów. Możemy np. wykonać zapytanie usuwające uczniów wcześniej, możemy przygotować inne usuwające ich po skasowaniu klasy, możemy również zdefiniować usuwanie kaskadowe podczas kasowania rekordu klasy. My zadowolimy się prostym zapytaniem wykonanym po zapytaniu usuwającym klasę (to zapytanie też można oczywiście zapisać na kilka sposobów):
delete from #uczniowie where idKlasy not in (select idKlasy from #klasy)
Czy w przypadku klas jest potrzeba wykonywania aktualizacji rekordów? W naszym przypadku nie. Klasa posiada tak na prawdę tylko informację o swojej nazwie, więc jeśli ta się zmienia to tak jakby jedna klasa była usuwana a druga była tworzona. Oczywiście gdybyśmy chcieli to moglibyśmy rozpoznawać aktualizację po identyfikatorze. W przypadku klas nie będziemy tego jednak robić.
Podsumowując nasze działania na kolekcji klas możemy zamieścić jeden spójny skrypt SQL (pomijam już definicję zmiennej z zawartością XML):
declare @klasy table (idKlasy int, nazwa varchar(2), id int);
insert into @klasy
select t.c.value('number(idKlasy[1])', 'int') as idKlasy,
t.c.value('nazwa[1]', 'varchar(2)') as nazwa,
k.idKlasy as id
from @xml.nodes('klasy/klasa') as t(c)
left join #klasy as k on k.nazwa = t.c.value('nazwa[1]', 'varchar(2)')
insert into #klasy (nazwa)
select nazwa from @klasy
where id is null
delete from #klasy
where nazwa not in (select nazwa from @klasy);
delete from #uczniowie
where idKlasy not in (select idKlasy from #klasy);
Jeśli wykonamy powyższy kod i pobierzemy sobie zawartość naszej tabeli #klasy naszym oczom ukaże się taki widok:
idKlasy nazwa ----------- ----- 1 1A 2 1B 3 1C
Jak widać dodana przez nas w dokumencie XML klasa „1C” została pomyślnie dodana.
Napiszmy teraz podobne zapytania dla informacji o uczniach. Zacznijmy od przetworzenia danych z postaci XML:
insert into @uczniowie
select t.c.value('number(idUcznia[1])', 'int') as idUcznia,
t.c.value('number(../../idKlasy[1])', 'int') as idKlasy,
t.c.value('imie[1]', 'nvarchar(50)') as imie,
t.c.value('nazwisko[1]', 'nvarchar(100)') as nazwisko,
u.idUcznia as id
from @xml.nodes('klasy/klasa/uczniowie/uczen') as t(c)
left join #uczniowie as u on u.idUcznia = t.c.value('idUcznia[1]', 'int')
W odróżnieniu od klas tutaj musimy pobierać dane nieco inaczej. Przede wszystkim interesujemy się identyfikatorem ucznia (w końcu może być dwóch różnych uczniów o takim samym imieniu i nazwisku). Drugim i zarazem o wiele ciekawszym aspektem jest pobranie identyfikatora klasy, do której dany uczeń jest przypisany. Zwróć proszę czytelniku uwagę na dane XML. Tam przy uczniu nie ma zapisanej jawnie informacji o identyfikatorze klasy – ten wynika bowiem ze struktury danych XML a konkretnie z tego, gdzie wpis ucznia został „podpięty”. Znając strukturę pliku XML wiemy, iż dane te są zapisane „dwa piętra” wyżej (dane ucznia są w ścieżce klasy/klasa/uczniowie/uczen a informacje o klasie w klasy/klasa). Dlatego w nazwie pola idKlasy pojawia się dodatkowy element ścieżki w postaci „../../”.
Ponieważ wcześniej uznaliśmy, że posługiwanie się identyfikatorem klasy nie jest najlepszym pomysłem, dlatego zmodyfikujmy nasze zapytanie aby pobrało nam ten identyfikator z aktualnej tabeli klas na podstawie nazwy klasy:
insert into @uczniowie
select t.c.value('number(idUcznia[1])', 'int') as idUcznia,
k.idKlasy as idKlasy,
t.c.value('imie[1]', 'nvarchar(50)') as imie,
t.c.value('nazwisko[1]', 'nvarchar(100)') as nazwisko,
u.idUcznia as id
from @xml.nodes('klasy/klasa/uczniowie/uczen') as t(c)
left join #uczniowie as u on u.idUcznia = t.c.value('idUcznia[1]', 'int')
inner join #klasy as k on k.nazwa = t.c.value('../../nazwa[1]', 'varchar(2)')
Możemy wykonać złączenie z tabelą klas w ten sposób ponieważ nasze wcześniejsze zapytania zadbały o to, aby wszystkie nieistniejące w tabeli klasy zostały tam dodane 🙂
Dalej z dodawaniem i usuwaniem uczniów mamy z górki – zapytania są bowiem podobne jak w przypadku klas:
delete from #uczniowie where idUcznia not in (select idUcznia from @uczniowie where idUcznia is not null); insert into #uczniowie (idKlasy, imie, nazwisko) select idKlasy, imie, nazwisko from @uczniowie where id is null;
Czy przy usuwaniu rekordów nie będzie jednak problemu? Przecież w XML mogli pojawić się nowi uczniowie, którzy nie mają swoich identyfikatorów. Otóż nie, problemu nie będzie gdyż przy usuwaniu interesują nas tylko i wyłącznie uczniowie, którzy istnieli wcześniej w bazie a usuwanie wykonujemy przecież przed ich dodaniem do tabeli.
Spróbujmy wykonać teraz powyższe polecenia i zobaczmy czy wszystko zadziałało poprawnie. Niestety naszym oczom powinien ukazać się błąd Cannot insert the value NULL into column ‘nazwisko’, table ‘tempdb.dbo.#uczniowie. O co chodzi? Przypomnij sobie proszę czytelniku, iż u jednego ucznia zrobiliśmy literówkę w nazwie pola przechowującego nazwisko. Skrypt odczytujący strukturę XML uczniów nie natrafił przy jednym uczniu na pole nazwiska więc przyjął, iż jego wartość to NULL a takiej wartości nasza docelowa tabela uczniów nie akceptuje. Co możemy z tym zrobić? To zależy od naszej fantazji i potrzeb. Możemy zostawić to tak jak jest ale wtedy sugerowałbym wycofanie całej transakcji po wykryciu błędu. Możemy też zmodyfikować nasze tabele aby akceptowały wartości NULL. Możemy też, podczas odczytywania danych z XML wstawiać dowolny inny łańcuch znaków – ten wariant zastosujemy w naszym przypadku. Nasze zapytanie odczytujące dane z XML powinno więc przybrać np. taką formę:
insert into @uczniowie
select t.c.value('number(idUcznia[1])', 'int') as idUcznia,
k.idKlasy as idKlasy,
coalesce(t.c.value('imie[1]', 'nvarchar(50)'), N'NIE PODANO') as imie,
coalesce(t.c.value('nazwisko[1]', 'nvarchar(100)'), N'NIE PODANO') as nazwisko,
u.idUcznia as id
from @xml.nodes('klasy/klasa/uczniowie/uczen') as t(c)
left join #uczniowie as u on u.idUcznia = t.c.value('idUcznia[1]', 'int')
inner join #klasy as k on k.nazwa = t.c.value('../../nazwa[1]', 'varchar(2)')
Teraz zajmijmy się aktualizowaniem danych uczniów. Ktoś mógł np. zmienić nazwisko, lub zaszła potrzeba skorygowania literówki w imieniu bądź nazwisku. Ponieważ ucznia identyfikujemy na podstawie identyfikatora to możemy wyszukać sobie takie wpisy uczniów, które są inne w XML oraz tabeli, ale które mają identyczny identyfikator. Uczniów, którym trzeba zaktualizować dane możemy odnaleźć przy pomocy następującego zapytania:
select idUcznia, idKlasy, imie, nazwisko from #uczniowie as u except select idUcznia, idKlasy, imie, nazwisko from @uczniowie
Zapytanie to wyszukuje wszystkie zapisane w tabeli uczniów rekordy za wyjątkiem takich samych rekordów w tabeli zbudowanej na podstawie danych XML. Efektem powyższego zapytania będzie w naszym przypadku:
idUcznia idKlasy imie nazwisko ----------- ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- 4 2 Marcel Mieszkowski
Ponieważ znamy identyfikatory wszystkich osób, ktorym trzeba zaktualizować dane, oraz wiemy, że w XML znajdują się nowe dane dlatego możemy wprowadzić nowe wartości przy pomocy np. takiego zapytania:
with doAktualizacji as ( select idUcznia, idKlasy, imie, nazwisko from #uczniowie as u except select idUcznia, idKlasy, imie, nazwisko from @uczniowie ) update u set u.imie = n.imie, u.nazwisko = n.nazwisko from #uczniowie as u inner join @uczniowie as n on n.idUcznia = u.idUcznia inner join doAktualizacji as a on a.idUcznia = n.idUcznia;
Zbierzmy sobie teraz cały skrypt dla przetworzenia danych o uczniach w jednym miejscu i spróbujmy go wykonać:
declare @uczniowie table (idUcznia int, idKlasy int, imie nvarchar(50), nazwisko nvarchar(100), id int);
insert into @uczniowie
select t.c.value('number(idUcznia[1])', 'int') as idUcznia,
k.idKlasy as idKlasy,
coalesce(t.c.value('imie[1]', 'nvarchar(50)'), N'NIE PODANO') as imie,
coalesce(t.c.value('nazwisko[1]', 'nvarchar(100)'), N'NIE PODANO') as nazwisko,
u.idUcznia as id
from @xml.nodes('klasy/klasa/uczniowie/uczen') as t(c)
left join #uczniowie as u on u.idUcznia = t.c.value('idUcznia[1]', 'int')
inner join #klasy as k on k.nazwa = t.c.value('../../nazwa[1]', 'varchar(2)');
delete from #uczniowie
where idUcznia not in (select idUcznia from @uczniowie where idUcznia is not null);
insert into #uczniowie (idKlasy, imie, nazwisko)
select idKlasy, imie, nazwisko from @uczniowie
where id is null;
with doAktualizacji as (
select idUcznia, idKlasy, imie, nazwisko
from #uczniowie as u
except select idUcznia, idKlasy, imie, nazwisko
from @uczniowie
)
update u
set u.imie = n.imie,
u.nazwisko = n.nazwisko
from #uczniowie as u
inner join @uczniowie as n on n.idUcznia = u.idUcznia
inner join doAktualizacji as a on a.idUcznia = n.idUcznia;
Pobranie zawartości tabeli #uczniowie da nam teraz następujący rezultat:
idUcznia idKlasy imie nazwisko ----------- ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- 1 1 Anna Nowak 2 1 Adam Kowalski 3 1 Monika Mickiewicz 4 2 Marcel Mieszakowski 5 2 Ewa Tarczyńska 6 2 Filip Konopski 7 1 Iwona Piekarska 8 3 Michał NIE PODANO
Jak widać wszystkie nowe osoby pojawiły się w swoich klasach (nawet jeśli nie podano wszyskich danych w poprawny sposób), rekord ucznia wymagający aktualizacji został poprawnie zapisany a dodatkowe dane podane przy uczeniach zostały zignorowane.
Mam nadzieję, że udało mi się pokazać, iż operowanie na danych XML nie jest wcale takie trudne. Aby uzmysłowić Tobie czytelniku jak łatwa może być to implementacja wyobraź sobie proszę, iż cały poniższy kod, którym posłużyliśmy się powyżej, możemy zamknąc w jednej procedurze, której argumentem będzie wyłącznie zmienna @xml.
declare @klasy table (idKlasy int, nazwa varchar(2), id int);
insert into @klasy
select t.c.value('number(idKlasy[1])', 'int') as idKlasy,
t.c.value('nazwa[1]', 'varchar(2)') as nazwa,
k.idKlasy as id
from @xml.nodes('klasy/klasa') as t(c)
left join #klasy as k on k.nazwa = t.c.value('nazwa[1]', 'varchar(2)')
delete from #klasy
where nazwa not in (select nazwa from @klasy);
insert into #klasy (nazwa)
select nazwa from @klasy
where id is null
delete from #uczniowie
where idKlasy not in (select idKlasy from #klasy);
declare @uczniowie table (idUcznia int, idKlasy int, imie nvarchar(50), nazwisko nvarchar(100), id int);
insert into @uczniowie
select t.c.value('number(idUcznia[1])', 'int') as idUcznia,
k.idKlasy as idKlasy,
coalesce(t.c.value('imie[1]', 'nvarchar(50)'), N'NIE PODANO') as imie,
coalesce(t.c.value('nazwisko[1]', 'nvarchar(100)'), N'NIE PODANO') as nazwisko,
u.idUcznia as id
from @xml.nodes('klasy/klasa/uczniowie/uczen') as t(c)
left join #uczniowie as u on u.idUcznia = t.c.value('idUcznia[1]', 'int')
inner join #klasy as k on k.nazwa = t.c.value('../../nazwa[1]', 'varchar(2)');
delete from #uczniowie
where idUcznia not in (select idUcznia from @uczniowie where idUcznia is not null);
insert into #uczniowie (idKlasy, imie, nazwisko)
select idKlasy, imie, nazwisko from @uczniowie
where id is null;
with doAktualizacji as (
select idUcznia, idKlasy, imie, nazwisko
from #uczniowie as u
except select idUcznia, idKlasy, imie, nazwisko
from @uczniowie
)
update u
set u.imie = n.imie,
u.nazwisko = n.nazwisko
from #uczniowie as u
inner join @uczniowie as n on n.idUcznia = u.idUcznia
inner join doAktualizacji as a on a.idUcznia = n.idUcznia;
Życzę miłej zabawy z XML. Ten format zapisu danych na prawdę nie jest taki zły w połączeniu z TSQL 🙂
