Chapter 12 - Te dhenat XML



Table 2 XML

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" ?> 
 <Authors AuthorId="1000" FirstName="Ndre" LastName="Mjeda"> 
  <Books> 
    <Book BookId="Z1234567" /> 
     <Book BookId="X1234567" /> 
      <Book BookId="Y1234567" /> 
       <Book BookId="W1234567" /> 
  </Books> 
</Authors>' 

SELECT @doc

afishon 

<?xml version="1.0" ?> 
 <Authors AuthorId="1000" FirstName="Ndre" LastName="Mjeda"> 
  <Books> 
    <Book BookId="Z1234567" /> 
     <Book BookId="X1234567" /> 
      <Book BookId="Y1234567" /> 
       <Book BookId="W1234567" /> 
  </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.

FOR XML RAW 


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 

<row EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio" Title="Sales Representative" />
<row EmployeeID="2" TitleOfCourtesy="Mrs." FirstName="Andrew" LastName="Fuller" Title="Vice President, Sales" />
<row EmployeeID="3" TitleOfCourtesy="Mrs." FirstName="Janet" LastName="Leverling" Title="Sales Representative" />
<row EmployeeID="4" TitleOfCourtesy="Mrs." FirstName="Margaret" LastName="Peacock" Title="Sales Representative" />
<row EmployeeID="5" TitleOfCourtesy="Mrs." FirstName="Steven" LastName="Buchanan" Title="Sales Manager" />


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>
  <EmployeeID>1</EmployeeID>
  <TitleOfCourtesy>Mrs.</TitleOfCourtesy>
  <FirstName>Nancy</FirstName>
  <LastName>Davolio</LastName>
  <Title>Sales Representative</Title>
</row>
<row>
  <EmployeeID>2</EmployeeID>
  <TitleOfCourtesy>Mrs.</TitleOfCourtesy>
  <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:

<Employee>
  <EmployeeID>1</EmployeeID>
  <TitleOfCourtesy>Mrs.</TitleOfCourtesy>
  <FirstName>Nancy</FirstName>
  <LastName>Davolio</LastName>
  <Title>Sales Representative</Title>
</Employee>
<Employee>
  <EmployeeID>2</EmployeeID>
  <TitleOfCourtesy>Mrs.</TitleOfCourtesy>
  <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:

<Employees>
  <Employee>
    <EmployeeID>1</EmployeeID>
    <TitleOfCourtesy>Mrs.</TitleOfCourtesy>
    <FirstName>Nancy</FirstName>
    <LastName>Davolio</LastName>
    <Title>Sales Representative</Title>
  </Employee>
  <Employee>
    <EmployeeID>2</EmployeeID>
    <TitleOfCourtesy>Mrs.</TitleOfCourtesy>
    <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:

<Employee EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio" OrderID="10258" CustomerID="ERNSH" OrderDate="1996-07-17T00:00:00" />
<Employee EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio" OrderID="10270" CustomerID="WARTH" OrderDate="1996-08-01T00:00:00" />
<Employee EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio" OrderID="10275" CustomerID="MAGAA" OrderDate="1996-08-07T00:00:00" />
<Employee EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio" OrderID="10285" CustomerID="QUICK" OrderDate="1996-08-20T00:00:00" />
<Employee EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio" OrderID="10292" CustomerID="TRADH" OrderDate="1996-08-28T00:00:00" />

 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
  2. 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 EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio">
  <Order OrderID="10258" CustomerID="ERNSH" OrderDate="1996-07-17T00:00:00" />
  <Order OrderID="10270" CustomerID="WARTH" OrderDate="1996-08-01T00:00:00" />
</Employee>
<Employee EmployeeID="2" TitleOfCourtesy="Mrs." FirstName="Andrew" LastName="Fuller">
  <Order OrderID="10265" CustomerID="BLONP" OrderDate="1996-07-25T00:00:00" />
  <Order OrderID="10277" CustomerID="MORGK" OrderDate="1996-08-09T00:00:00" />
</Employee>
<Employee EmployeeID="3" TitleOfCourtesy="Mrs." FirstName="Janet" LastName="Leverling">
  <Order OrderID="10251" CustomerID="VICTE" OrderDate="1996-07-08T00:00:00" />
  <Order OrderID="10253" CustomerID="HANAR" OrderDate="1996-07-10T00:00:00" />
</Employee>
<Employee EmployeeID="4" TitleOfCourtesy="Mrs." FirstName="Margaret" LastName="Peacock">
  <Order OrderID="10250" CustomerID="HANAR" OrderDate="1996-07-08T00:00:00" />
  <Order OrderID="10252" CustomerID="SUPRD" OrderDate="1996-07-09T00:00:00" />
</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.

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
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'
rezultati do te ishte krejt ndryshe nga hera e pare:

<Employees EmployeeID="1" TitleOfCourtesy="Mrs." FirstName="Nancy" LastName="Davolio">
  <Orders OrderID="11067" CustomerID="DRACD" OrderDate="1998-05-04T00:00:00" />
  <Orders OrderID="11069" CustomerID="TORTU" OrderDate="1998-05-04T00:00:00" />
  <Orders OrderID="11071" CustomerID="LILAS" OrderDate="1998-05-05T00:00:00" />
  <Orders OrderID="11077" CustomerID="RATTC" OrderDate="1998-05-06T00:00:00" />
</Employees>
<Employees EmployeeID="2" TitleOfCourtesy="Mrs." FirstName="Andrew" LastName="Fuller">
  <Orders OrderID="11078" CustomerID="ALFKI" OrderDate="2010-11-15T16:47:43.367" />
  <Orders OrderID="11073" CustomerID="PERIC" OrderDate="1998-05-05T00:00:00" />
  <Orders OrderID="11070" CustomerID="LEHMS" OrderDate="1998-05-05T00:00:00" />
  <Orders OrderID="11059" CustomerID="RICAR" OrderDate="1998-04-29T00:00:00" />
  <Orders OrderID="11060" CustomerID="FRANS" OrderDate="1998-04-30T00:00:00" />
</Employees>

FOR XML PATH


3PATH 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:

<Punonjesit>
  <Punonjes KodiPunonjesit="1">
    <Gjeneralitet>
      <Emri>Nancy</Emri>
      <Mbiemri>Davolio</Mbiemri>
    </Gjeneralitet>
  </Punonjes>
  <Punonjes KodiPunonjesit="2">
    <Gjeneralitet>
      <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:

<Punonjesit>
  <Punonjes KodiPunonjesit="1">
    <Gjeneralitet>
      <Emri>Nancy</Emri>
      <Mbiemri>Davolio</Mbiemri>
    </Gjeneralitet>
    <Porosite>
      <Porosi kodi="10258" Data="1996-07-17T00:00:00" Klienti="ERNSH" />
      <Porosi kodi="10270" Data="1996-08-01T00:00:00" Klienti="WARTH" />
    </Porosite>
  </Punonjes>
  <Punonjes KodiPunonjesit="2">
    <Gjeneralitet>
      <Emri>Andrew</Emri>
      <Mbiemri>Fuller</Mbiemri>
    </Gjeneralitet>
    <Porosite>
      <Porosi kodi="10265" Data="1996-07-25T00:00:00" Klienti="BLONP" />
      <Porosi kodi="10277" Data="1996-08-09T00:00:00" Klienti="MORGK" />
    </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:

<Punonjesit>
  <Punonjes KodiPunonjesit="1">
    <Gjeneralitet>
      <Emri>Nancy</Emri>
      <Mbiemri>Davolio</Mbiemri>
    </Gjeneralitet>
    <Porosite>
      <Porosi kodi="10258" Data="1996-07-17T00:00:00">
        <Klienti Kodi="ERNSH" />
      </Porosi>
      <Porosi kodi="10270" Data="1996-08-01T00:00:00">
        <Klienti Kodi="WARTH" />
      </Porosi>
    </Porosite>
  </Punonjes>
  <Punonjes KodiPunonjesit="2">
    <Gjeneralitet>
      <Emri>Andrew</Emri>
      <Mbiemri>Fuller</Mbiemri>
    </Gjeneralitet>
    <Porosite>
      <Porosi kodi="10265" Data="1996-07-25T00:00:00">
        <Klienti Kodi="BLONP" />
      </Porosi>
      <Porosi kodi="10277" Data="1996-08-09T00:00:00">
        <Klienti Kodi="MORGK" />
      </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')


FOR XML EXPLICIT


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 Kodi="1" Emri="Beverages">
   <Produktet Kodi="3" Emri="Aniseed Syrup" />
  <Produktet Kodi="4" Emri="Chef Anton's Cajun Seasoning" />
  <Produktet Kodi="5" Emri="Chef Anton's Gumbo Mix" />
  <Produktet Kodi="6" Emri="Grandma's Boysenberry Spread" />
  <Produktet Kodi="8" Emri="Northwoods Cranberry Sauce" />
</Kategorite>
<Kategorite Kodi="3" Emri="Confections">
   <Produktet Kodi="31" Emri="Gorgonzola Telino" />
  <Produktet Kodi="32" Emri="Mascarpone Fabioli" />
  <Produktet Kodi="33" Emri="Geitost" />
</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:



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

XML 2 TABLE

Open XML


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.




Declare @dochandle int
Declare @doc as xml
SET @doc=dbo.shitjexml(2)

EXECUTE sp_xml_preparedocument @dochandle OUTPUT,@doc

SELECT * FROM OPENXML(@dochandle,'punonjesit/punonjes/Porosite/Porosi',1)
with
(
EmployeeId INT '../../@kodi',
Firstname varchar(50) '../../gjeneralitete/emri',
OrderId INT '@Kodi',
OrderDate datetime '@Data'
)


Funksioni shitjexml

CREATE FUNCTION [dbo].[shitjexml](@eid int)
returns xml
as
begin
declare @s as xml
set @s=
(
SELECT EmployeeID as '@kodi',
FirstName as 'gjeneralitete/emri',
LastName  as 'gjeneralitete/mbiemri',
(
select OrderID 'Kodi',
   OrderDate 'Data',
   (
   select ProductName 'Produkti',
[Order Details].UnitPrice 'Cmimi',
[Order Details].Quantity 'Sasia',
([Order Details].UnitPrice*[Order Details].Quantity) 'Vlera'
   from [Order Details] 
inner join Products
on [Order Details].ProductID=Products.ProductID
where Orders.OrderID=[Order Details].OrderID
FOR XML Raw('RreshtPorosi'),ROOT('Detaje'),TYPE
   )
from Orders
where Orders.EmployeeID=Employees.EmployeeID
for XML raw('Porosi'),ROOT('Porosite'),TYPE
)
FROM Employees
where EmployeeID=@eid
for xml path('punonjes'),root('punonjesit')
)
return @s
end

Procedurat dhe te dhenat XML


DECLARE @doc xml 
SET @doc = '<?xml version="1.0" ?> 
 <Order EmployeeId="2" CustomerId="ALFKI" > 
  <OrderDetails> 
    <Product ProdId="1" Quantity="6" Price="100"/> 
    <Product ProdId="2" Quantity="4" Price="245"/> 
    <Product ProdId="3" Quantity="4" Price="100"/> 
  </OrderDetails> 
</Order>' 
exec dbo.shtoporosi @doc





CREATE PROCEDURE shtoporosi
(@docxml as xml)
AS 
BEGIN
BEGIN TRANSACTION neworder
Declare @oid as int
Declare @a as int
exec sp_xml_preparedocument @a OUTPUT,@docxml

INSERT INTO Orders(EmployeeId,CustomerId)
select EmployeeId,CustomerID from 
OPENXML(@a,'Order',1)
WITH
(
EmployeeId  INT '@EmployeeId',
CustomerId  VARCHAR(10) '@CustomerId'
  )
select @oid=@@IDENTITY

insert into [Order Details](OrderId,ProductId,UnitPrice,Quantity )
select @oid,ProductId,Price,Quantity from 
OPENXML(@a,'Order/OrderDetails/Product',1)
WITH
(
ProductId  INT '@ProdId',
Quantity  float '@Quantity',
Price money '@Price'
)

If @@ERROR<>0 ROLLBACK TRANSACTION neworder
ELSE COMMIT TRANSACTION neworder

END


Nje version i permiresuar i procedures

ALTER PROCEDURE shtoporosi
(@docxml as xml)
AS 
BEGIN
BEGIN TRY
BEGIN TRANSACTION neworder
Declare @oid as int
Declare @a as int
exec sp_xml_preparedocument @a OUTPUT,@docxml

INSERT INTO Orders(EmployeeId,CustomerId)
select EmployeeId,CustomerID from 
OPENXML(@a,'Order',1)
WITH
(
EmployeeId  INT '@EmployeeId',
CustomerId  VARCHAR(10) '@CustomerId'
  )
select @oid=@@IDENTITY

insert into [Order Details](OrderId,ProductId,UnitPrice,Quantity )
select @oid,ProductId,Price,Quantity from 
OPENXML(@a,'Order/OrderDetails/Product',1)
WITH
(
ProductId  INT '@ProdId',
Quantity  float '@Quantity',
Price money '@Price'
)

COMMIT TRANSACTION neworder
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION neworder
print 'gabim'
END CATCH

END