Wyciągnięcie danych z kolumny XML (query,value)

Witam, zwracam się z prośbą w rozwiązaniu problemu z XML a dokładniej z wyciągnięciem danych z tabeli, która zawiera w sobie kolumnę XML. Mam tabelę o nazwie TerminyXML, w której są trzy kolumny: doc (XML), od(date), do(date). Chcę wyciągnąć imię, nazwisko lekarza, oraz ile dni przyjmuje w zadanym przez nas okresie czasu. Wszystko to zapisane jest właśnie w kolumnie w której przechowywane są XML'e.

 <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" />
      </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>

tak wygląda przykładowy XML w kolumnie doc w tabeli TerminyXML. Daty nie są przypadkowe, bo poprzez inserty dodawałem dane tygodniowe jakby. Chodzi o to aby uzyskać zestawienie dla zadanego przez nas tygodnia, w którym będziemy mieli: ImięLekarza, NazwiskoLekarza, ileDniPrzyjmuje. Próbuję coś w ten sposób:

 SELECT
	doc.value('count(/Przychodnia/Lekarze/Lekarz/Terminy/Termin)', 'int') as ile,
	doc.value('(/Przychodnia/Lekarze/Lekarz/@imie)[1]', 'nvarchar(80)') as imie,
	doc.value('(/Przychodnia/Lekarze/Lekarz/@nazwisko)[1]', 'nvarchar(80)') as nazwisko
		FROM
    TerminyXML

lecz w ten sposób wyświetla mi jedynie pierwsze imie i nazwisko z pierwszej krotki z tabeli TerminyXML i w dodatku zlicza wszystkie wizyty w danej krotce a nie do konkretnego lekarza

No to jedziemy od podstaw:

Materiały, kótre warto przyswoić:

• XML Data Type and Columns (SQL Server)
• nodes() Method (xml Data Type)
• value() Method (xml Data Type)
• query() Method (xml Data Type)
• Using APPLY

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:

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:

Musisz użyć takiego zapytania:

select
	--dane lekarza
	L.Lekarz.value('@imie','nvarchar(100)') Imie
	,L.Lekarz.value('@nazwisko','nvarchar(100)') Nazwisko
	--dane z wezlatermin
	,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:

select
	--dane lekarza
	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

@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:

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:

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:

select
	--dane lekarza
	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:

select
	--dane lekarza
	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.

@Panczo: bardzo Ci dziękuję, za tak obszerne rozjaśnienie tematu

Chciałem wrzucić to w procedurę, tylko coś mi nie wychodzi, obawiam się że właśnie przez przekazywanie argumentów do procedury, niby jak odpalę to nie wyświetla błędu, ale w rezultacie nie liczy nic, pokazuje po prostu 0 w kolumnie z liczbą terminów/wizyt

 CREATE PROCEDURE test1
@po DATE,
@ko DATE
AS
BEGIN
SELECT ImięLekarza,NazwiskoLekarza,SUM(ile) ileDniPrzyjmuje
FROM(
SELECT
    --dane lekarza
    L.Lekarz.value('@imie','nvarchar(100)') ImięLekarza
    ,L.Lekarz.value('@nazwisko','nvarchar(100)') NazwiskoLekarza
    ,L.Lekarz.value('count(Terminy/Termin[@data>="@po" and @data<="@ko"])', 'int') AS ile 
FROM
	TerminyXML
    CROSS APPLY
    doc.nodes('/Przychodnia/Lekarze/Lekarz') L(lekarz)
	) x
GROUP BY ImięLekarza,NazwiskoLekarza
ORDER BY ImięLekarza,NazwiskoLekarza
END

Nie mam teraz pod ręką SQL-a, ale błędna jest ta linijka:

    ,L.Lekarz.value('count(Terminy/Termin[@data>="@po" and @data<="@ko"])', 'int') AS ile 

Powinno być coś takiego:

    ,L.Lekarz.value('count(Terminy/Termin[@data>=sql:variable("@po") and @data<=sql:variable("@ko")])', 'int') AS ile 

wywaliło mi takie coś jak zmieniłem

 XQuery [TerminyXML.doc.value()]: The operator ">=" cannot be applied to "xs:string ?" and "xs:date ?" operands.

a przecież tam nie ma nigdzie zmiennej tekstowej

To wynika z twojego XSD podpiętego pod kolumne doc
masz tam linijkę:

<xs:attribute name="data" type="xs:string" use="optional" />

która wskazuje na to, e data jest tekstem a nie datą, jak zmienisz w xsd na:

<xs:attribute name="data" type="xs:date" use="optional" />

To zadziała prawidłowo