No to jedziemy od podstaw:
Materiały, kótre warto przyswoić:
Teraz zaczniemy od początku, zanim bedziesz operować na danych z tabeli, naucz się odpytywać samego XMLa.
To co zaprezentowałeś w pierwszym przykładzie nie sprawdzi się ponieważ chcesz pobrać wszystkie węzły Lekarz, a metoda value zwraca tylko jedną wartość,
dlatego musisz użyć metody nodes().
Zapytania, które pokaże zawsze będą dla takiego XML-a:
Kopiuj
declare @doc xml
set @doc = N'<Przychodnia od="2016-11-22" do="2016-11-28">
<Lekarze>
<Lekarz imie="Jacek" nazwisko="Paździoch">
<Terminy>
<Termin data="2016-11-28" godzina_od="11:00:00" godzina_do="13:00:00" nazwaGabinetu="Socjal" />
<Termin data="2016-12-28" godzina_od="11:00:00" godzina_do="13:00:00" nazwaGabinetu="Socjal" />
</Terminy>
</Lekarz>
<Lekarz imie="Alona" nazwisko="Nowak">
<Terminy>
<Termin data="2016-11-22" godzina_od="12:00:00" godzina_do="14:00:00" nazwaGabinetu="Socjal" />
</Terminy>
</Lekarz>
<Lekarz imie="Helga" nazwisko="Kowalska">
<Terminy>
<Termin data="2016-11-23" godzina_od="15:00:00" godzina_do="16:00:00" nazwaGabinetu="Socjal" />
</Terminy>
</Lekarz>
<Lekarz imie="Marcin" nazwisko="Krzynówek">
<Terminy>
<Termin data="2016-11-24" godzina_od="10:00:00" godzina_do="13:00:00" nazwaGabinetu="Socjal" />
</Terminy>
</Lekarz>
<Lekarz imie="Jadwiga" nazwisko="Kiepska">
<Terminy>
<Termin data="2016-11-25" godzina_od="09:00:00" godzina_do="11:00:00" nazwaGabinetu="Socjal" />
</Terminy>
</Lekarz>
</Lekarze>
</Przychodnia>'
To żeby pobrać dane w postaci:
| Imie |
Nazwisko |
TerminData |
TrminGodzinaOd |
TerminGodzinaDo |
TerminData |
| Jacek |
Paździoch |
2016-11-28 |
11:00:00 |
13:00:00 |
Socjal |
| Jacek |
Paździoch |
2016-11-28 |
11:00:00 |
13:00:00 |
Socjal |
| Alona |
Nowak |
2016-11-22 |
12:00:00 |
14:00:00 |
Socjal |
| Helga |
Kowalska |
2016-11-23 |
15:00:00 |
16:00:00 |
Socjal |
| Marcin |
Krzynówek |
2016-11-24 |
10:00:00 |
13:00:00 |
Socjal |
| Jadwiga |
Kiepska |
2016-11-25 |
09:00:00 |
11:00:00 |
Socjal |
Musisz użyć takiego zapytania:
Kopiuj
select
L.Lekarz.value('@imie','nvarchar(100)') Imie
,L.Lekarz.value('@nazwisko','nvarchar(100)') Nazwisko
,T.termin.value('@data','date') TerminData
,T.termin.value('@godzina_od','nvarchar(20)') TrminGodzinaOd
,T.termin.value('@godzina_do','nvarchar(20)') TerminGodzinaDo
,T.termin.value('@nazwaGabinetu','nvarchar(20)') TerminData
from
@doc.nodes('/Przychodnia/Lekarze/Lekarz') L(lekarz)
CROSS APPLY
L.Lekarz.nodes('Terminy/Termin') T(termin)
Jednak nie ma sensu, trawersować, całego XML-a, skoro nas interesuje tylko ilość, dlatego wystarczy pobrać tylko węzły lekarz:
Kopiuj
select
L.Lekarz.value('@imie','nvarchar(100)') Imie
,L.Lekarz.value('@nazwisko','nvarchar(100)') Nazwisko
,L.Lekarz.value('count(Terminy/Termin)', 'int') as ile
from
@doc.nodes('/Przychodnia/Lekarze/Lekarz') L(lekarz)
Wynik:
Imie|Nazwisko|ile
---------------- | -------------------
Jacek|Paździoch|2
Alona|Nowak|1
Helga|Kowalska|1
Marcin|Krzynówek|1
Jadwiga|Kiepska|1
Tutaj warto się zatrzymać i zastanowić co się tak naprawdę zadziało
Tu ważna jest "funkcja tabelaryczna" nodes() która zwraca węzły zgodnie z przekazanym argumentem xQuery i daje do nich dostęp poprzez nazwę tabeli i kolumne,
W przykładzie
Kopiuj
@doc.nodes('/Przychodnia/Lekarze/Lekarz') L(lekarz)
Ważny jest zapis L(lekarz) w uproszczeniu: zwróć mi węzły Przychodnia/Lekarze/Lekarz i załaduj je do tabeli L w kolumnie Lekarz
Czyli zwraca nam tabelę L:
| Lekarz |
| <Terminy><Termin data="2016-11-28" godzina_od="11:00:00" godzina_do="13:00:00" nazwaGabinetu="Socjal" /><Termin data="2016-11-28" godzina_od="11:00:00" godzina_do="13:00:00" nazwaGabinetu="Socjal" /></Terminy> |
| <Terminy><Termin data="2016-11-22" godzina_od="12:00:00" godzina_do="14:00:00" nazwaGabinetu="Socjal" /></Terminy> |
| <Terminy><Termin data="2016-11-23" godzina_od="15:00:00" godzina_do="16:00:00" nazwaGabinetu="Socjal" /></Terminy> |
| <Terminy><Termin data="2016-11-24" godzina_od="10:00:00" godzina_do="13:00:00" nazwaGabinetu="Socjal" /></Terminy> |
| <Terminy><Termin data="2016-11-25" godzina_od="09:00:00" godzina_do="11:00:00" nazwaGabinetu="Socjal" /></Terminy> |
Oczywiście kolumna lekarz jest typu xml i możemy na niej wykorzystywać metody vale(), query() itd.
To teraz wróćmy do przykładu z postu:
Kopiuj
create table terminyXML (
od date
,do date
,doc XML);
go
insert into terminyXML values ('2016-11-01','2016-11-30',@doc)
I wykorzystajmy wiedzę którą podałem wcześniej:
Kopiuj
select
L.Lekarz.value('@imie','nvarchar(100)') Imie
,L.Lekarz.value('@nazwisko','nvarchar(100)') Nazwisko
,L.Lekarz.value('count(Terminy/Termin)', 'int') as ile
from
terminyXML
CROSS APPLY
doc.nodes('/Przychodnia/Lekarze/Lekarz') L(lekarz)
Oczywiście to nie jest prawidłowy wynik, powiedzmy, ze chce dane tylko z listopada, ale Jacek Paździoch ma 2 terminy jeden w grudniu drugi w listopadzie, dlatego musimy rozbudować trochę to zapytanie i dodać warunek w
funkcji count:
Kopiuj
select
L.Lekarz.value('@imie','nvarchar(100)') Imie
,L.Lekarz.value('@nazwisko','nvarchar(100)') Nazwisko
,L.Lekarz.value('count(Terminy/Termin[@data>="2016-11-01" and @data<="2016-11-30"])', 'int') as ile
from
terminyXML
CROSS APPLY
doc.nodes('/Przychodnia/Lekarze/Lekarz') L(lekarz)
Na to trzeba zwrócić uwagę, ponieważ tabela moze przechowywać zakres większy niż nas interesuje.
XML