Windows - Adatlekérdezés XML formában

SQL XML Support 2. rész

Az MS SQL 2000 segítségével könnyen, és minden igényeinket kielégítő módon tudjuk az adatbázis adatait XML dokumentumként lekérdezni a FOR XML feltétel segítségével. Cikkünkben ismertetjük mindazon lehetőségeket, amit ez a feltétel kínál.
FOR XML használata
Lehetőségünk van SQL lekérdezéseket definiálni a relációs adatbázisszervernek, és az eredmény XML dokumentumként érkezik. Ehhez használjuk a FOR XML feltételt a SELECT utasításban. Három XML módot különböztetünk meg:
  • RAW: a RAW mód minden egyes sort egy XML elembe helyez, amelynek az azonosítója: „row”. Minden nem NULL értékű oszlop az oszlop nevével jellemzett attribútumként jelenik meg az adott elemben.
  • AUTO: az AUTO mód egymásba ágyazott XML elemeket ad vissza. Minden tábla, ami szerepel a FROM feltételben és legalább egy mezője szerepel a SELECT listában egy XML elemként érkezik. A tábla neve, vagy az alias neve megfelel az XML elem nevének. Ha több táblát adunk meg a feltételben, akkor egymásba ágyazott eredményt kapunk, azaz az elsőként megadott tábla lesz az első elem, és a következő tábla ennek belső eleme lesz.
  • EXPLICIT: ebben a módban a lekérdezés írója kontrollálni tudja a visszaadott XML dokumentum formáját. A lekérdezést úgy kell megírni, hogy tartalmazza azokat a szükséges információkat, amelyek a várható egymásba ágyazásokhoz szükségesek. Mező szinten megadhatunk direktívákat. Ebben az esetben vállalni kell a felelősséget, hogy a létrehozott XML helyesen formázott, és érvényes lesz.
Ezek a beállítási módok csak azokban a lekérdezésekben érvényesek, ahol megadjuk őket, pontosabban nincsenek hatással a belső lekérdezésekre. Az XML mód meghatározása mellett elérhetünk XML-Data sémát is. Ezt akkor szükséges használni, ha speciális – főként többféle numerikus – adattípust akarunk definiálni az XML dokumentumban. A szervernek ez nagyobb terhelést jelent.
FOR XML Szintaxis
A FOR XML szintaxisa a következő:
FOR XML mode [, XMLDATA][, ELEMENTS][, BINARY BASE64]
  • Az XMLDATA beállítás jelzi, hogy XML-Data séma eredményt várunk.
  • Az ELEMENTS opció esetén az oszlopok belső elemként érkeznek, ellenkező esetben attribútumként szerepelnek. Ez az opció csak az AUTO módban használható.
  • Ha a BINARY Base64 opciót használjuk, akkor a bináris formátumú visszaadott értékek base64 formátumban jelennek meg. Ahhoz, hogy RAW, vagy EXPLICIT módban bináris adatokat kapjunk, használnunk kell ezt a kulcsszót. AUTO módban ez alapértelmezett.
Explicit lekérdezés
Ahhoz, hogy az EXPLICIT módot használjuk a lekérdezésben, az eredménysoroknak meghatározott formátumúaknak kell lenniük. A lekérdezést úgy kell megírnunk, hogy az eredménytábla alkalmas legyen a kért XML dokumentum előállítására. Ezt a speciális formátumot univerzális táblának nevezzük. Elsőként két meta adat oszlop szükséges:
  • Tag nevű oszlop, ami a tag sorszámát jelenti, integer adattípusúnak kell lennie.
  • Parent nevű oszlop, ami a szülő elem tag számát tárolja, szintén integer.
Ez a két oszlop determinálja a szülő-gyermek viszonyt az XML fában. Ha a Parent oszlop értéke 0, vagy NULL, akkor a sor a legfelsőbb szinten lesz a hierarchiában.
A többi adat az univerzális táblában, teljes mértékben leírja a kért XML dokumentumot. Egy példával szemléltetjük, hogy az egymásba ágyazott <Customer>, <Order>, <OrderDetail> elemek hogyan mutatnak.
Tag Parent Customer!1!cid Customer!1!name Order!2!id Order!2!date OrderDetail!3!id!id OrderDetail!3!pid!idref
1 NULL C1 „Janine” NULL NULL NULL NULL
2 1 C1 NULL O1 1/20/1996 NULL NULL
3 2 C1 NULL O1 NULL OD1 P1
3 2 C1 NULL O1 NULL OD2 P2
2 1 C1 NULL O2 3/29/1997 NULL NULL
Az univerzális tábla adatai vertikálisan csoportokra különülnek. Minden csoport XML elemmé válik az eredményben. Az így létrehozott XML dokumentum a következő (részlet):
<Customer cid="C1" name="Janine">
<Order id="O1" date="1/20/1996">
<OrderDetail id="OD1" pid="P1"/>
<OrderDetail id="OD2" pid="P2"/>
</Order>
<Order id="O2" date="3/29/1997">
...
</Customer>
A lekérdezésben meg kell adni az univerzális tábla oszlopainak neveit. Ezek a nevek az XML általános azonosítóiként kódoltak. Az elem nevek, attribútum nevek, és egyéb átalakító információk a következőképpen néznek ki:
ElementName!TagNumber!AttributeName!Directive
  • ElementName: a visszaadott értékben szereplő XML elemek neve
  • TagNumber: az elem tag sorszáma. Minden tag szám meghatároz egy konkrét elem nevet.
  • AttributeName: az XML attribútumot jelenti, ha a direktíva nincs megadva, vagy elem nevet tartalmaz, ha a direktíva xml, cdata vagy element értékű. Ha a direktíva megadott, akkor az attribútum lehet üres is. Ebben az esetben az ElementName nevű elem fogja közvetlenül tartalmazni az oszlopértéket.
  • Directive: opcionális direktíva. Ha nincs direktíva, akkor attribútumot kell megadni, ha nincs attribútum, és nincs direktíva (pl. Customer!1), akkor egy elem direktívát foglal magában (pl. Customer!1!!element), és adatot tartalmaz.
Direktíva használatának két célja van. Kódolhatjuk az ID, IDREF, IDREFS értékeket ugyanezen kulcsszavak használatával. Használhatjuk még arra is, hogy hogyan rendelje egymáshoz a string adatot az XML, használva a hide, element, xml, xmltext, cdata kulcsszavakat. Ezek a direktíva csoportok kombinációja a legtöbb szükséges esetet tartalmazza, viszont saját magukkal nem kombinálhatók.
  • ID: egy elem attribútuma lehet ID típusú. IDREF és IDREFS típusú attribútumok hivatkozhatnak rá, ezáltal dokumentumon belüli linkelést megvalósítva. Ha nincs XMLDATA kérés, akkor nincs hatása a kulcsszónak.
  • IDREF, IDREFS: hivatkozhat ID típusú attribútumra. Szintén nincs hatása XMLDATA kérés hiánya esetén.
  • hide: az attribútum nem jelenítődik meg. Hasznos lehet, ha attribútum segítségével akarunk eredmény sorrendet felállítani, és az attribútumot el akarjuk rejteni.
  • element: nem hoz létre attribútumot, hanem kreál egy belső elemet a megadott névvel, vagy közvetlenül, ha nincs attribútum név megadva. A belső adat egyedként kódolt (pl. a < karakter <-vé válik). ez a kulcsszó kombinálható ID, IDREF, IDREFS kulcsszavakkal.
  • xml: hasonló a használata az element direktívához, azzal a kivétellel, hogy egyed kódolása nem történik meg (pl. < karakter marad <). Ez a direktíva csak a hide kulcsszóval kombinálható.
  • xmltext: az oszloptartalomnak egy tag-be kell kerülnie, ami illeszkedik a többi elem közé. Ez a direktíva arra szolgál, hogy az OPENXML által oszlopban tárolt túlcsorduló XML adatot lekérdezze. (Az XML adatok írására szolgáló OPENXML feltétellel a következő részben foglalkozunk.) Ha az attribútum név megadott, akkor a tag név kicserélődik az attribútum névre, máskülönben az attribútum hozzáadódik a mellékelt elem attribútumaihoz, és így adódik hozzá a tartalomhoz, az egyed kódolása nélkül. Az oszlopnak a következő szöveges típusok valamelyikének kell lennie: (varchar, nvarchar, char, nchar, text, ntext). Csak a hide kulcsszóval kombinálható. Az oszlopban lévő túlcsorduló adatot kéri le.
  • cdata: adatot tartalmaz CDATA részlegbe csomagolva. Nincs egyedkódolás. Az eredeti adattípusnak text-nek kell lennie (varchar, nvarchar, text, ntext). Csak a hide kulcsszóval kombinálható. Ha megadjuk a direktívát, az AttributeName használata nem kötelező.
Példák (Northwind adatbázis használatával)
RAW módban egy példa lekérdezésre:
SELECT Customers.CustomerID, Orders.OrderID, Orders.OrderDate 
FROM Customers, Orders 
WHERE Customers.CustomerID = Orders.CustomerID 
ORDER BY Customers.CustomerID 
FOR XML RAW 
Eredmény:
<row CustomerID="ALFKI" OrderID="10643" OrderDate="1997-08-25T00:00:00"/>
<row CustomerID="ANATR" OrderID="10308" OrderDate="1996-09-18T00:00:00"/>
<row CustomerID="ANATR" OrderID="10625" OrderDate="1997-08-08T00:00:00"/>
<row CustomerID="AROUT" OrderID="10355" OrderDate="1996-11-15T00:00:00"/>
Láthatjuk, hogy „row” nevű tag-ekbe ágyazva kapjuk a sorokat. Az oszlopértékek attribútumként jelennek meg.
AUTO módra példa:
SELECT Employees.EmployeeID, LastName, FirstName,
OrderID, OrderDate, Orders.EmployeeID
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
ORDER BY Employees.EmployeeID
FOR XML AUTO
Eredmény:
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy">
<Orders OrderID="10258" OrderDate="1996-07-17T00:00:00" EmployeeID="1"/>
<Orders OrderID="10270" OrderDate="1996-08-01T00:00:00" EmployeeID="1"/>
</Employees>
<Employees EmployeeID="2" LastName="Fuller" FirstName="Andrew">
<Orders OrderID="10248" OrderDate="1996-07-04T00:00:00" EmployeeID="5"/>
<Orders OrderID="10249" OrderDate="1996-07-05T00:00:00" EmployeeID="6"/>
</Employees>
Több táblát is használunk a FROM feltételben, így beágyazott eredményelemeket kapunk. A select lista első elemének megfelelően az Employees elem lesz a külső elem. Az oszlopinformációk attribútumként jelennek meg, és a tábla nevei megfelelnek az XML elemeknek.
EXPLICIT mód:
SELECT 1 as Tag, 
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2, 1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT
A lekérdezésben elsőként a külső elemeket kérjük le. 1 lesz a tag sorszám. NULL a Parent, mert ez a legfelső szint. Még két oszlop szükséges. Az első a Customer!1!CustomerID, ami Customer element, 1-es tag CustomerID attribútumot jelent. A másik pedig az Order element 2-es tag OrderID attribútumot jelent. Az unió második lekérdezésében kapjuk meg egyaránt az Order és Customer táblák ID mezőinek tartalmát.
Univerzális tábla:
Tag Parent Customer!1!CustomerID Order!2!OrderID
1 NULL ALFKI NULL
2 1 ALFKI 10643
2 1 ALFKI 10692
2 1 ALFKI 10702
2 1 ALFKI 11011
2 1 ALFKI ...
1 NULL ANATR NULL
2 1 ANATR 10308
2 1 ANATR 10625
2 1 ANATR ...
Eredmény:
<Customer CustomerID="ALFKI">
<Order OrderID="10643" />
<Order OrderID="10692" />
<Order OrderID="10702" />
<Order OrderID="11011" />
</Customer>
<Customer CustomerID="ANATR">
<Order OrderID="10308" />
<Order OrderID="10625" />
</Customer>

SQL XML Support cikksorozat