XML kolonna un Cross Apply

XML datu tipā tiek glabāti XML dokumenti vai XML dokumentu fragmenti. Tātad- tur var būt (un bieži ir) vairāki elementi, kas atkārtojas, veidojot interesantu konstrukciju- tabulas šūnā glabājas vairāki elementi, piemēram, personu saraksts. Šeit par to, kā atlasīt datus no XML kolonnas, veidojot līdzīgu konstrukciju kā galvas-detaļu tabula.

Konstrukcija varētu būt mazliet mulsinoša, ja iepriekš nav izmantots Cross Apply vai Outer Apply. Zemāk skriptā ir neliels "špikērīts", kā izgūt datus:

Declare @myXML XML
Declare @tab Table
(
    tabId int primary key identity,
    iestade nvarchar(50),
    darbinieki xml
)

Insert Into @tab(iestade, darbinieki)
    Values
        (N'kaut kāda iestāde', N'
        <PersonInfo>
            <Person ID="21">
                <Name>Jānis</Name>
                <LastName>Bērziņš</LastName>
            </Person>
            <Person ID="22">
                <Name>Liene</Name>
                <Profession>Kociņa</Profession>
            </Person>
        </PersonInfo>
        ');
Insert Into @tab(iestade, darbinieki)
    Values (N'Cita iestāde', Null)

-- Parasts selekts:
Select * From @tab;


-- Iegūt darbiniekus no 1 iestādes var arī šādi:
declare @darbinieki xml;

Select @darbinieki =  darbinieki
From @tab
Where iestade = N'kaut kāda iestāde';

Select
    t.x.value('(Name)[1]', 'nvarchar(50)') vards,
    t.x.value('(LastName)[1]', 'nvarchar(50)') uzvards,
    t.x.query('.')
From @darbinieki.nodes('/PersonInfo/Person') t(x)


--izmantojot Cross Apply (līdzīgi kā Inner Join- rezultātā 2 rindas):
Select
    tabid,
    iestade,
    t.x.value('(Name)[1]', 'nvarchar(50)') vards,
    t.x.value('(LastName)[1]', 'nvarchar(50)') uzvards
From @tab
    Cross Apply darbinieki.nodes('/PersonInfo/Person') t(x)

--izmantojot Outer Apply (līdzīgi kā Left Join- rezultātā 3 rindas):
Select
    tabid,
    iestade,
    t.x.value('(Name)[1]', 'nvarchar(50)') vards,
    t.x.value('(LastName)[1]', 'nvarchar(50)') uzvards
From @tab
    Outer Apply darbinieki.nodes('/PersonInfo/Person') t(x)

Emuārā par XML: XML SQL Server.