Datu izgūšana XML formā (FOR XML klauza)

SQL Server atbalsta datu izgūšanu XML formātā. Šajā rakstā pamatlietas par FOR XML klauzu (Raw, Auto, Path un Explicit), daži veidi/piemēri, kā dabūt XML dokumentu sev vēlamā formā. Raksta nobeigumā dažas kopīgās lietas (atslēgvārdi Root, Type un Binary Base64, kā arī vārdtelpu izmantošana kopā ar For XML).

Rakstā visi piemēri tiks veidoti uz šīm tabulām un datiem:

Create Table dbo.Owners
(
    OwnerID int primary key identity,
    FirstName nvarchar(50) not null,
    LastName nvarchar(50) not null,
    Age int
)
Go
Create Table dbo.Cars
(
    CarID int primary key identity,
    OwnerID int not null Foreign Key References dbo.Owners(OwnerID),
    Make nvarchar(30),
    Color nvarchar(30)
)
Go
Insert Into dbo.Owners(FirstName,LastName,Age)
    Values('Jānis', 'Liepiņš', 20);
Insert Into dbo.Owners(FirstName,LastName,Age)
    Values('Liene', 'Bērziņa', 39);
Insert Into dbo.Owners(FirstName,LastName,Age)
    Values('Vilma', 'Kociņa', 60);
  
Insert Into dbo.Cars(OwnerID,Make,Color)
    Values(1,'BMW','Black')
Insert Into dbo.Cars(OwnerID,Make,Color)
    Values(2,'Opel','Yello')
Insert Into dbo.Cars(OwnerID,Make,Color)
    Values(3,'BMW','Red')
Insert Into dbo.Cars(OwnerID,Make,Color)
    Values(3,'Aston Martin','Red')

For XML Raw (msdn)
Vaicājums, kuram beigās tiek norādīts "For XML Raw" atgriezīs XML fragmentu, kurā katra rinda tiek pārveidota par XML elemntu, bet katra kolona- par šī rindas elementa atribūtu (atribūta nosaukums = kolonnas nosaukums, rindas elements pēc noklusējuma saucas "row").
1. piemērs:

Select o.OwnerID, o.FirstName, o.LastName, c.Make, c.Color
From Owners o Inner Join Cars c on o.OwnerID = c.OwnerID
FOR XML Raw

rezultāts:


Elementa nosaukums pēc noklusējuma ir "Row", bet iegūtais XML fragments ir bez saknes elementa. Šīs abas lietas var mainīt, nedaudz papildinot vaicājumu (2. Piemērs).

2. piemērs:

Select o.OwnerID, o.FirstName, o.LastName, c.Make, c.Color
From Owners o Inner Join Cars c on o.OwnerID = c.OwnerID
FOR XML Raw('ReturnedRow'), Root('Owners')

rezultāts:


Ja ir vajadzība katru kolonu atgriezt kā elementu, nevis atribūtu, tad For XML klauzai beigās vēl var pievienot atslēgvārdu "Elements". Ja norāda "Elements XSINIL", tad tiek ģenerēti elementi arī tad, ja attiecīgās rindas kolonnā ir Null vērtība. Ja to nenorāda, Null vērtībām elementi netiek ģenerēti.
3. piemērs:

Select o.OwnerID, o.FirstName, o.LastName, c.Make, c.Color
From Owners o Inner Join Cars c on o.OwnerID = c.OwnerID
FOR XML Raw('ReturnedRow'), Root('Owners'), Elements

rezultātu fragments:



For XML Auto (msdn)
Ja dati tiek izgūti tikai no vienas tabulas, tad gandrīz nav svarīgi, vai tiek izmantots "For XML Raw", vai "For XML Auto". Atšķirība tādā gadījumā ir tikai tāda, ka katras rindas elementa nosaukums sakritīs ar tabulas vai tabulas saīsinājuma (alias) nosaukumu. Savukārt ja dati tiek izgūti no vairāk kā vienas tabulas, tad XML tiek atgriezts kā koks (galvas tabula kā virsējais elements, detaļu tabula- kā apakšelements). Līdzīgi, kā For XML Raw gadījumā, atribūtus var pārveidot kā elementus izmantojot "Elements" atslēgvārdu (gluži kā 3.piemērā)
4. piemērs:

Select o.OwnerID, o.FirstName, o.LastName, c.Make, c.Color
From Owners o Inner Join Cars c on o.OwnerID = c.OwnerID
FOR XML Auto, Root('Owners')

rezultāts:


For XML Path (msdn)
Iepriekš minētie "For XML Raw" un "For XML Auto" atgriež datus vai nu kā elementus, vai rindas elementa atribūtus. Tomēr kontrole ir tikai tik daudz, kā viens vai otrs variants. Ja ir nepieciešams detalizētāk noteikt elementus un atribūtus, tad var izmantot For XML Path klauzu. Šī klauza palīdz arī norādīt sarežģītākus parametrus iegūtajam XML fragmentam.
Piemērā redzams, kā var pielāgot, kas ir parametri un kas- atribūti. Šajā gadījumā Vilma Kociņa būs divas reizes "Person" elements (nevis kā viens "Person" elements ar diviem "Car" elementiem, kā tas bija For XML Auto piemērā).
5. piemērs:

Select o.OwnerID "@ownerID", o.FirstName "Name/First", o.LastName "Name/Last",
    c.Make "CarColor/@make", c.Color "CarColor"
From Owners o Inner Join Cars c on o.OwnerID = c.OwnerID
FOR XML PATH('Person'), Root('List')

rezultāta fragments:


Ar šīs klauzas palīdzību var norādīt arī, piemēram, komentārus, instrukcijas XML apstrādes aplikācijai (līdzīgi kā iepriekšējos variantos, kas gan netika norādīts).
6. piemērs:

Select
    'Šeit mazs komentārs' "comment()",    
    o.OwnerID "processing-instruction(ApstradesInfo)",
    o.FirstName, o.LastName   
From Owners o
Where o.OwnerID = 2
FOR XML PATH('Person')

rezultāts:


Un vēl viens piemērs ar XML vārdtelpām. Šajā gadījumā lai ilustrētu situāciju, ka viena un tā paša nosaukuma elementi ir no dažādām vārdtelpām.
7. piemērs:

WITH XMLNAMESPACES(default 'www.sqlblog.lv/Owners', 'www.sqlblog.lv/Cars' as c)
Select o.OwnerID "@ownerID", o.FirstName "Name/First", o.LastName "Name/Last",
    c.OwnerID "c:CarColor/@c:OwnerID", c.Make "c:CarColor/@c:make", c.Color "c:CarColor"
From Owners o Inner Join Cars c on o.OwnerID = c.OwnerID
Where o.OwnerID = 2
FOR XML PATH('Person'), Root('List')

rezultāts:


For XML Explicit (msdn)
Šis ir pats sarežģītākais no visiem For XML variantiem, bet arī ar visplašākajām iespējām (šeit tikai viens piemērs).
Principā For XML Explicit ir jāapskata tad, kad ar iepriekšējiem variantiem nevar iegūt to pašu rezultātu vienkāršākā veidā.
8. piemērs:

SELECT  1 as Tag,
        0 as Parent,
        o.OwnerID [Owners!1!OwnerID],
        o.FirstName [Owners!1!Vards],
        o.LastName [Owners!1!Uzvards],
        NULL [Cars!2!CarID],
        NULL [Cars!2!Make!Element],
        NULL [Cars!2!Color!Element]
FROM  Owners o
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       c.OwnerID [Owners!1!OwnerID],
       Null,
       Null,
       c.CarID [Cars!2!CarID!Element],
       c.Make [Cars!2!Make!Element],
       c.Color [Cars!2!Color!Element]
FROM Cars c
order by [Owners!1!OwnerID], [Cars!2!CarID]
FOR XML EXPLICIT;

rezultāta fragments:


Kopīgie atslēgvārdi (Root, Type un Binary Base64)
Visus šos atslēgvārdus var pievienot ar komatu pēc For XML ... klauzas.

• Root- For XML atgriež XML fragmentu bez saknes elementa. Ja ir nepieciešamība, ka XML fragmentam tiek pievienots arī saknes elements, tad jāizmanto atslēgvārdu Root('SaknesElementaNosaukums'). Root elements tika izmantots vairākos no iepriekšējiem piemēriem.
• Type (msdn)- For XML atgriež datus teksta formātā. Bet SQL Server ir iebūvēts XML datu tips. Ja ir vajadzība atgriezt datus XML datu tipa formātā, tad ir jāizmanto atslēgvārds "Type". Tas noder gadījumos, ja ir nepieciešams apstrādāt XML uz SQL Server  vai arī gadījumos, ja iegūto XML vēlaties iekļaut citā XML (ilustrācijai 9.piemērs zemāk)
• Binary Base64 (msdn)- ārpus apskatītā. 

9. piemērs (divi vaicājumi, viens ar Type, otrs bez Type):

Select o.OwnerID, o.FirstName, o.LastName,
    (Select c.Make, c.Color
    From Cars c
    Where c.OwnerID = o.OwnerID
    For XML Raw('Car'), type)
From Owners o
FOR XML Path('Owner'), Root('Owners')

Select o.OwnerID, o.FirstName, o.LastName,
    (Select c.Make, c.Color
    From Cars c
    Where c.OwnerID = o.OwnerID
    For XML Raw('Car'))
From Owners o
FOR XML Path('Owner'), Root('Owners')

rezultātu fragmenti
izmantojot Type tiek iekļauti XML dati:


bez Type tiek iekļauts teksts:


Vārdtelpas
Vēl viena lieta, ko vēlējos pieminēt ir tā, ka visiem For XML variantiem var izmantot arī vārdtelpas (līdzīgi kā 7.piemērā).
Vaicājuma sākumā pievienojot, piemēram:

WITH XMLNAMESPACES(default 'www.sqlblog.lv/Owners',
'www.sqlblog.lv/Cars' as c)

mēs norādām, ka noklusētā vārdtelpa atgrieztajam XML dokumentam ir "www.sqlblog.lv/Owners" un ka var izmantot arī vārdtelpu "www.sqlblog.lv/Cars", kurai saīsinātais nosaukums ir "c".
Vaicājumā priekšā iespējams pievienot vārdtelpas saīsinājumu norādot no kuras vārdtelpas nāk elements vai atribūts. Izskatās apmēram šādi:"c:Make". Jāievēro parastie XML vārdtelpu likumi..