Te dhenat XML

rezultati do te ishte krejt ndryshe nga hera e pare:

</Employees>

</Employees>

3. PATH MODE QUERIES - FOR XML PATH

Nje tjeter menyre per te konvertuar te dhenat tabelare ne XML eshte FOR XML PATH. Kjo menyre i eksporton te dhenat ne XML ne nje strukture te ngjashme me direktorite ne sistemin operativ.

/ (slash) perdoret per te ndare nyjet nga njera tjetra

. Simbolizon nyjen korrente

.. Simbolizon nyjen prind te nyjes korrente

Nepermjet FOR XML PATH mund te krijojme edhe nyje qe nuk kane korrespondence direkte me kollonat e tabelave. Kjo eshte nganjehere e nevojshme per te ndertuar XML sipas nje skeme te percaktuar paraprakisht nga partneri me te cilin duam te shkembejme te dhena ne formatin XML.

Le ta ilustrojme me nje shembull perdorimin e FOR XML PATH :

Select EmployeeId '@KodiPunonjesit',

Firstname 'Gjeneralitet/Emri',

Lastname 'Gjeneralitet/Mbiemri'

From Employees

where EmployeeID<3

FOR XML PATH('Punonjes'),ROOT('Punonjesit')

Komanda e mesiperme afishon nje XML si me poshte:

<Emri>Nancy</Emri>

<Mbiemri>Davolio</Mbiemri>

</Gjeneralitet>

</Punonjes>

<Emri>Andrew</Emri>

<Mbiemri>Fuller</Mbiemri>

</Gjeneralitet>

</Punonjes>

</Punonjesit>

Ne rezultatin XML veme re disa gjera:

a) Elementet ne XML kane emra te ndryshem nga emrat e kollonave ne bazen e te dhenave, EmplyeeId eshte konvertuar ne KodiPunonjesit, Firstname ne Emri dhe LastName ne Mbiemri. Kjo eshte realizuar nepermjet riemerimit ose krijimit te ALIAS-eve per kollonat.

me konkretisht EmployeeId '@KodiPunonjesit' i vendos nje emer te ri kollones EmployeeId

b) Gjithashtu veme re nje element XML Gjeneralitetet, per te cilin nuk ka ndonje korrespondence ne tabelen Employees. Si eshte shtuar atehere ky element? Ky element eshte shtuar thjesh nepermjet Alias qe i eshte vene kollones Firstname.

Ne komanden SQL kemi

Firstname 'Gjeneralitet/Emri',

Lastname 'Gjeneralitet/Mbiemri'

Duke qene se XML PATH perdor / per te ndare nga nje nyje ne nje tjeter, SQL Serveri kupton qe kollona me emrin 'Gjeneralitet/Emri' perbehet nga dy elemente XML elementi Gjeneralitet dhe elementi Emri , si edhe qe elementi Emri eshte bir i elementit Gjeneralitet.

Ne menyre analoge rezulton qe Mbiemri eshte bir i elementit Gjeneralitet, ndaj emri dhe mbiemri jane paraqitur si elemente bij te elementit gjeneralitet

c) Pse per kodin e punonjesit kemi vendosur @ perpara emrit ('@KodiPunonjesit') ndersa per Emrin dhe Mbiemrin jo? Shenja @ i tregon SQL qe kollona KodiPunonjesit do te projektohet ne rezultatin XML si nje atribut i elementit ku ndodhet. E ne fakt po te shohim ne rezultatin XML KodiPunonjesit eshte paraqitur si nje atribut i nje punonjesi ndersa Emri dhe Mbiemri jane paraqitur si elemente me vehte.

Po nese kemi te dhena nga disa tabela?

Ne kete rast do te ishte e lehte te kombinonim nje PATH XML me nje RAW ose me nje tjeter PATH XML duke nderfutur komandat(query) SQL nepermjet opsionit TYPE. Shembujt e meposhtem ilustrojne kete gje.

Shembull 1- Kombimin i FOR XML PATH me FOR XML RAW nepermjet opsionit TYPE

Per punonjesit me kodin me te vogel se 3, afishoni ne nje XML kodin dhe gjeneralitetet e tyre se bashku me porosite orders. Te dhenat duhen sipas formatit qe eshte afishuar me poshte si shembull:

<Emri>Nancy</Emri>

<Mbiemri>Davolio</Mbiemri>

</Gjeneralitet>

</Porosite>

</Punonjes>

<Emri>Andrew</Emri>

<Mbiemri>Fuller</Mbiemri>

</Gjeneralitet>

</Porosite>

</Punonjes>

</Punonjesit>

Pas nje vezhgimi te pare te rezultatit te kerkuar veme re qe te dhenat mund te prodhohen ne kete format duke eksportuar te dhenat e tabeles punonjesit nepermjet FOR XML PATH , per arsye se duhet te percaktojme qe disa kollona do jene atribute(KodiPunonjesit) dhe disa elemente (Emri) per me teper duhet te shtojme nyje qe nuk kane korrespondence direkte ne kollonat e tabeles (Gjeneralitet). Per te dhenat e porosive me sa duket mund te perdoret nje query qe i konverton ne XML nepermjet FOR XML RAW brenda query per punonjesit.

Me poshte po shkruajme query-n qe gjeneron te dhenat XML sipas formatit te kerkuar:

Select EmployeeId '@KodiPunonjesit',Firstname 'Gjeneralitet/Emri',Lastname 'Gjeneralitet/Mbiemri',

(

SELECT TOP 2 Orders.OrderID 'kodi',

Orders.OrderDate 'Data',

Orders.CustomerID 'Klienti'

FROM Orders

where Orders.EmployeeID=Employees.EmployeeID

FOR XML RAW('Porosi'),TYPE,ROOT('Porosite')

)

From Employees

where EmployeeID <3

FOR XML PATH('Punonjes'),ROOT('Punonjesit')

Shembull 2- Kombimin i dy komandave FOR XML PATH nepermjet opsionit TYPE

Per punonjesit me kodin me te vogel se 3, afishoni ne nje XML kodin dhe gjeneralitetet e tyre se bashku me porosite orders. Te dhenat duhen sipas formatit qe eshte afishuar me poshte si shembull:

<Emri>Nancy</Emri>

<Mbiemri>Davolio</Mbiemri>

</Gjeneralitet>

</Porosi>

</Porosi>

</Porosite>

</Punonjes>

<Emri>Andrew</Emri>

<Mbiemri>Fuller</Mbiemri>

</Gjeneralitet>

</Porosi>

</Porosi>

</Porosite>

</Punonjes>

</Punonjesit>

Pas nje vezhgimi te pare te rezultatit te kerkuar veme re qe te dhenat mund te prodhohen ne kete format duke eksportuar te dhenat e tabeles punonjesit nepermjet FOR XML PATH , per arsye se duhet te percaktojme qe disa kollona do jene atribute(KodiPunonjesit) dhe disa elemente (Emri) per me teper duhet te shtojme nyje qe nuk kane korrespondence direkte ne kollonat e tabeles (Gjeneralitet). Per te dhenat e porosive me sa duket nuk mund te perdoret nje query qe i konverton ne XML nepermjet FOR XML RAW, pasi edhe ne kete rast duhet te percaktojme qe disa kollona do jene atribute(kodi) dhe duhet te shtojme nyje qe nuk kane korrespondence direkte ne kollonat e tabeles (Klienti)

Me poshte po shkruajme query-n qe gjeneron te dhenat XML sipas formatit te kerkuar:

Select EmployeeId '@KodiPunonjesit',Firstname 'Gjeneralitet/Emri',Lastname 'Gjeneralitet/Mbiemri',

(

SELECT TOP 2 Orders.OrderID '@kodi',

Orders.OrderDate '@Data',

Orders.CustomerID 'Klienti/@Kodi'

FROM Orders

where Orders.EmployeeID=Employees.EmployeeID

FOR XML PATH('Porosi'),TYPE,ROOT('Porosite')

)

From Employees

where EmployeeID <3

FOR XML PATH('Punonjes'),ROOT('Punonjesit')

4. Konvertimi i te dhenave tabelare ne XML nepermjet FOR XML EXPLICIT

Konvertimi i te dhenave ne XML nepermjet FOR XML EXPLICIT, eshte nje proces paksa me i komplikuar, por na jep kontroll te plote mbi menyren se si do te konvertohen te dhenat XML. Ne shumicen e rasteve na mjaftojne tre menyrat e ilustruara deri me tani RWA,PATH dhe AUTO.

Konvertimi nepermjet menyres EXPLICIT, bazohet ne tabelat universale. Tabelat universale kane se dy kollona:

TAG dhe PARENT. Kollona TAG ka vleren e nivelit qe kane rreshtat e tabeles dhe kollona PARENT ka vleren NULL per nivelin e pare te pemes XML dhe vleren e kollones TAG te rreshtave qe sherbejne si Prind.

Per ta bere me te thjeshte procesin e ndertimit te tabelave universale po marrim si shembull konvertimin e te dhenave per produktet dhe kategorite e tyre.

Do te na duhet pra te prodhojme nje rezultat XML si meposhte:

</Kategorite>

</Kategorite>

Per kete do te na duhet se pari te ndertojme tabelat universale dhe ti bejme ato union me njera tjetren.

Se pari, ndertojme tabelen universale per tabelen Categories:

SELECT

1 as TAG,

NULL As Parent,

Categories.CategoryID 'Kategorite!1!Kodi' ,

Categories.CategoryName 'Kategorite!1!Emri',

NULL as 'Produktet!2!Kodi',

NULL as 'Produktet!2!Emri'

from Categories

Kjo do te prodhonte nje rezultat si ne figuren e meposhtme:

select Employees.EmployeeID,Employees.TitleOfCourtesy,Employees.FirstName,

Employees.LastName, Orders.OrderID,Orders.CustomerID,Orders.OrderDate

FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID

order by Employees.EmployeeID

FOR XML RAW('Employee'

Te dhenat XML ne SQL Server 2008

Ne kete leksion

Si te konvertojme te dhenat tabelare ne format XML

Si te lexojme te dhenat XML dhe ti paraqesim ato ne forme tabelare

Si te konvertojme te dhenat tabelare ne format XML

XML eshte formati standart per shkembimin e te dhenave. SQL Server, qe me versionin SQL 2005 ka shtuar nje tip te ri te dhenash per XML.

Kjo ben te mundur qe te dhenat XML te ruhen ne vlerat e nje kollone te tabeles, ku mund te aplikohen gjithashtu rregulla per validimin e te dhenave qe mund te futen ne nje dokument XML. Kjo realizohet nepermjet skemave XML.

Gjithashtu mund te deklarojme variabla te tipit XML dhe ti perdorim ato ne skriptet XML.

Per shembull kodi:

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

<Books>

</Books>

</Authors>'

SELECT @doc

afishon

<?xml version="1.0" ?>

<Books>

</Books>

</Authors>

Ne permbajtjen e mesiperme XML, Authors edhe nje element XML, (nganjehere referohen edhe si nyje "node" XML). Ndersa AuthorId, Firstname dhe Lastname jane atributet e elementit Authors.

Elementi Authors permban elementin Books.

Elementi Books permban elemente Book. Elementet Book jane atributin BookID.

Ky dallim midis elementeve dhe atributeve edhe i rendesishem, por kete do ta shohim kur te perdorim FOR XML PATH, ne seksionim me poshte.

Do te shikojme se pari, si ti konvertojme te dhenat tabelare ne format XML. Per shembull nese duam te konvertojme listen e punonjesve nga tabela Employees ne nje format XML apo nese duam te eskportojme porosite (Orders) ne XML per ti shkembyer me nje tjeter aplikacion, per shembull me aplikacioni e finances per ti ndjekur me tej porosite per efekte pagese,kontabiliteti etj.

Per te konvertuar te dhenat tabelare ne XML, ekziston nje kaluzole e komandes SELECT, qe eshte FOR XML.

Klauzola FOR XML ka disa menyra perdorimi dhe sejcila prej ketyre ka disa opsione si per shembull opsioni per ti shtuar nje element rrenje (root) dokumentit XML.

Menyrat e perdorimit te FOR XML jane kater:

- FOR XML RAW
- FOR XML AUTO
- FOR XML PATH
- FOR XML EXPLICIT

Per me teper eshte e mundur qe komanda qe kthejne XML te integrohen brenda komandave(query) qe kthejne XML ose te dhena tabelare, por keto do ti shohim pasi te shpjegojme menyrat e perdorimit te FOR XML.

Perdorimi i FOR XML RAW

Sintaksa e perdorimit te FOR XML eshte shume e thjeshte dhe memorizohet lehte.

Ne fund te query-t vendoset fjala FOR XML dhe metoda e perdorur.

Vini re shembullin e meposhtem per te kuptuar se si perdoret klauzola FOR XML.

Kodi:

select EmployeeID,TitleOfCourtesy,FirstName,LastName,Title

from Employees

FOR XML RAW

afishon

Sic duket edhe nga shembulli i mesiperm, nje query-t e zakonshme si per shembull ajo qe afishon te dhenat e punonjesve, nese ka ne fund klauzolen FOR XML , e prodhon rezultatin ne format XML.

FOR XML RAW eshte menyra me e thjeshte e prodhimit te rezultateve ne format XML.

Per ta permiresuar rezultatin mund te perdorim opsionet e FOR XML RAW

FOR XML RAW, Elements

Opsioni ELEMENTS i afishon kollonat e tabeles si elemente ne vend te atributeve.

Per shembull, komanda

select EmployeeID,TitleOfCourtesy,FirstName,LastName,Title

from Employees where EmployeeID<3

FOR XML RAW,Elements

do te afishonte rezultatin e meposhtem:

<row>

<FirstName>Nancy</FirstName>

<LastName>Davolio</LastName>

<Title>Sales Representative</Title>

</row>

<row>

<FirstName>Andrew</FirstName>

<LastName>Fuller</LastName>

<Title>Vice President, Sales</Title>

</row>

Per ti vendosur nje emer rreshtave, ne vend te row, sic eshte ne rezultatin e mesiperm mund te vendosim tekstin e deshiruar si ne shembullin e meposhtem:

select EmployeeID,TitleOfCourtesy,FirstName,LastName,Title

from Employees where EmployeeID<3

FOR XML RAW('Employee'),Elements

do te afishonte:

<FirstName>Nancy</FirstName>

<LastName>Davolio</LastName>

<Title>Sales Representative</Title>

</Employee>

<FirstName>Andrew</FirstName>

<LastName>Fuller</LastName>

<Title>Vice President, Sales</Title>

</Employee>

Nese duam qe te vendosim nje element rrenje per te gjithe XML , mund te perdorim opsionin ROOT

Shembulli i meposhtem ilustron kete perdorim:

select EmployeeID,TitleOfCourtesy,FirstName,LastName,Title

from Employees where EmployeeID<3

FOR XML RAW('Employee'),Elements,ROOT('Employees')

kodi i mesiperm do te afishonte:

<FirstName>Nancy</FirstName>

<LastName>Davolio</LastName>

<Title>Sales Representative</Title>

</Employee>

<FirstName>Andrew</FirstName>

<LastName>Fuller</LastName>

<Title>Vice President, Sales</Title>

</Employee>

</Employees>

Sic vihet re, ne xml me siper eshte shtuar nje element rrenje, Employees.

Po nese duam te konvertojme ne XML rezultatin e nje query qe merr te dhena nga disa tabela?

Edhe ne keto raste FOR XML RAW na mundeson te prodhojme rezultatin e deshiruar, por ka disa kufizime.

Po i ilustrojme keto me shembullin e meposhtem.

Komanda e meposhtme afishon per nje liste me punonjesit dhe me porosite qe ka trajtuar ai:

select Employees.EmployeeID,Employees.TitleOfCourtesy,Employees.FirstName,

Employees.LastName, Orders.OrderID,Orders.CustomerID,Orders.OrderDate

FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID

order by Employees.EmployeeID

FOR XML RAW('Employee')

Po paraqesim nje pjese te rezultatit te komandes:

Ky rezultat, pervec qe eshte veshtiresisht i lezueshem, ka edhe nje tjeter problem: Te dhenat e punonjesit kodi, emri, mbiemri afishohen per cdo porosi, A nuk do te ishte me mire sikur te dhenat te organizoheshin ne formen e pemese ku te paraqiteshin te dhenat e punonjesit dhe brenda elementit XML te punonjesite te paraqiteshin porosite qe ai ka trajtuar.

Ky eshte nje rast kur FOR XML RAW, paraqitet kufizues. ne kete situate kemi dy zgjidhje:

1. te integrojme komandat SQL qe prodhojne XML brenda njera tjetres duke perdorur opsionin TYPE

te perdorim FOR XML AUTO - qe eshte me i pershtashem pikerisht per ato raste kur kemi nje rezultat te nje query me JOIN

Po ilustrojme se pari :

1. Si te integrojme XML brenda te dhenave te tjera XML duke perdorur opsionin TYPE

Opsioni TYPE i FOR XML , na mundeson te futim brenda njera tjetres komandat qe gjenerojne XML. Po e ilustojme perdorimin e FOR XML TYPE, duke ndjekur llogjiken e shembullit me lart.

Komanda per te prodhuar nje rezultat me te lexueshem te punonjesve dhe porosive qe ata kane trajtuar si edhe qe nuk perserit te dhenat e punonjesve do te ishte komanda e meposhtme:

select Employees.EmployeeID,Employees.TitleOfCourtesy,Employees.FirstName,Employees.LastName,

(

select Orders.OrderID,Orders.CustomerID,Orders.OrderDate from Orders

where Orders.EmployeeID=Employees.EmployeeID

FOR XML RAW('Order'),TYPE

)

from Employees

FOR XML RAW('Employee')

e cila do te gjeneronte nje output si me poshte:

</Employee>

</Employee>

</Employee>

</Employee>

Sic, mund te vihet re edhe nga rezultati brenda elementit XML me te dhenat e punonjesit vendosen porosite qe ai ka trajtuar.

Vini re perdorimin e opsionit TYPE tek query i brendshem.

2. FOR XML AUTO

Ne menyre te vecante nese rezultati i query tone vjen nga nje JOIN mbi dy ose me shume tabela, FOR XML AUTO eshte me efektiv, pasi ai nuk perserit te dhenat e tabeles prind. Nese komanden e meparshme:

select Employees.EmployeeID,Employees.TitleOfCourtesy,Employees.FirstName,

Employees.LastName, Orders.OrderID,Orders.CustomerID,Orders.OrderDate

FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID

order by Employees.EmployeeID

FOR XML RAW('Employee')

Do ta shkruanim ndryshe , duke zevendesuar ne rreshtin e fundit FOR XML RAW('Employee') me FOR XML AUTO

Krijojme edhe tabelen universale per Produktet:

SELECT

2 as TAG,

1 As Parent,

Products.CategoryID 'Kategorite!1!Kodi',

NULL 'Kategorite!1!Emri',

Products.ProductID 'Produktet!2!Kodi',

Products.ProductName 'Produktet!2!Emri'

From Products

I bashkojme te du tabelat me UNION ALL

SELECT

1 as TAG,

NULL As Parent,

Categories.CategoryID 'Kategorite!1!Kodi' ,

Categories.CategoryName 'Kategorite!1!Emri',

NULL as 'Produktet!2!Kodi',

NULL as 'Produktet!2!Emri'

from Categories

UNION ALL

SELECT

2 as TAG,

1 As Parent,

Products.CategoryID 'Kategorite!1!Kodi',

NULL 'Kategorite!1!Emri',

Products.ProductID 'Produktet!2!Kodi',

Products.ProductName 'Produktet!2!Emri'

From Products

dhe perpara se sa ti eksportojme si XML i renditim sipas kollones qe te dy tabelat e kane te perbashket, Kodin e kategorise qe e kemi emertuar 'Kategorite!1!Kodi'.

SELECT

1 as TAG,

NULL As Parent,

Categories.CategoryID 'Kategorite!1!Kodi' ,

Categories.CategoryName 'Kategorite!1!Emri',

NULL as 'Produktet!2!Kodi',

NULL as 'Produktet!2!Emri'

from Categories

UNION ALL

SELECT

2 as TAG,

1 As Parent,

Products.CategoryID 'Kategorite!1!Kodi',

NULL 'Kategorite!1!Emri',

Products.ProductID 'Produktet!2!Kodi',

Products.ProductName 'Produktet!2!Emri'

From Products

Order by 'Kategorite!1!Kodi'

dhe se fundi i konvertojme ne XML:

SELECT

1 as TAG,

NULL As Parent,

Categories.CategoryID 'Kategorite!1!Kodi' ,

Categories.CategoryName 'Kategorite!1!Emri',

NULL as 'Produktet!2!Kodi',

NULL as 'Produktet!2!Emri'

from Categories

UNION ALL

SELECT

2 as TAG,

1 As Parent,

Products.CategoryID 'Kategorite!1!Kodi',

NULL 'Kategorite!1!Emri',

Products.ProductID 'Produktet!2!Kodi',

Products.ProductName 'Produktet!2!Emri'

From Products

Order by 'Kategorite!1!Kodi'

FOR XML EXPLICIT

Si te lexojme te dhenat XML dhe ti paraqesim ato ne forme tabelare - OPEN XML

Ne kete pjese do te shikojme konvertimin mbrapsht, nga te dhenat XML ne te dhena tabelare. Per kete ekziston nje komande e SQL OPENXML, e cila merr si argument nje dokument XML dhe pozicionohet ne te duke lexuar te dhenat sipas PATH XML.

Perpara se te mund te lexojme brenda nje variabli XML me OPENXML te ndertojme strukturen e pemes se dokumentit XML ne menorje.

Per kete SQL ka nje procedure te sistemit sp_xml_preparedocument.

sp_xml_preparedocument lexon tekstin XML qe merr si input, e lexon ate nepermjet lexuesit MSXML (Msxmlsql.dll), dhe kthen ne nje variabel te tipit INT, nje shenjues mbi strukturen e dokumentit XML. Dokumenti i kthyer eshte nje paraqitje ne forme peme e nyjeve te dokumentit XML: elementet, atributet, etj.

Shenjuesi qe kthen sp_xml_preparedocument returns eshte i vlefshem pergjate sesionit ose derisa te eliminohet nepermjet procedures sp_xml_removedocument. Per efekte te performances rekomandohet qe pasi kemi perfunduar pune me dokumentin XML ta eliminojme strukturen e tij nga memorja nepermjet sp_xml_removedocument.

Page updated

Report abuse