Az Oracle sokféle függvényt biztosít arra, hogy a különböző
formátumú adatokból (relációs, obj-relációs) XML dokumentumot
tudjunk létrehozni, illetve fordítva.
SYS_XMLGEN(kif [,fmt]) függvény
-------------------------------
XML dokumentumot hoz létre a paraméterül kapott értékből. Ha egy SELECT utasításban
használjuk a lekérdezés valamelyik oszlopára, akkor minden sorra külön dokumentumot
hoz létre.
Ha a paraméter egy skalár érték, akkor egy ezt tartalmazó XML elemet ad vissza.
Ha a paraméter egy objektum példány, az attribútumokat külön XML elemekre képezi le.
A '@'-al kezdődő attribútumokból XML attribútumot (és nem XML elemeket!) csinál.
Ha a paraméter egy XML dokumentum töredék, akkor azt egy XML elembe ágyazza.
Példák:
SELECT SYS_XMLGEN(dnev) FROM dolgozo WHERE ROWNUM < 3;
---------------------
SMITH
ALLEN
Az alábbi lekérdezés egy XML töredéket ad vissza, amiből a függvény XML-t csinál.
(Nem egyetlen gyökérelem van, ezért nem jól formált XML dokumentum az eredmény)
SELECT EXTRACT(kolcs_spec, '//DVD').getStringVal() FROM kolcsonzes WHERE azon=1;
----------------------------------------------------------------------------------------
JegkorszakShrekUvegtigris
SELECT SYS_XMLGEN(EXTRACT(kolcs_spec, '//DVD')).getStringVal() FROM kolcsonzes WHERE azon=1;
---------------------------------
Jegkorszak
Shrek
Uvegtigris
Az EXTRACT által visszaadott szöveges dokumentumtöredékek egybeolvadnak egyetlen töredékké.
SELECT EXTRACT(kolcs_spec, '//DVD/text()').getStringVal() FROM kolcsonzes WHERE azon=1;
-------------------------
JegkorszakShrekUvegtigris
SELECT SYS_XMLGEN(EXTRACT(kolcs_spec, '//DVD/text()')).getStringVal() FROM kolcsonzes WHERE azon=1;
---------------------------
JegkorszakShrekUvegtigris
Az elem nevét meg is lehet változtatni egy formázó objektum megadásával.
Ennek leírását lásd -> XmlGenFormatType
select * from dba_objects where object_name = 'XMLGENFORMATTYPE'
select text from dba_type_versions where type_name='XMLGENFORMATTYPE' order by line
SELECT SYS_XMLGEN(EXTRACT(kolcs_spec, '//DVD/text()'),
SYS.XMLGenFormatType.CreateFormat('ELEM')).getStringVal() FROM kolcsonzes WHERE azon=1;
-----------------------------
JegkorszakShrekUvegtigris
Objektumok lekérdezése
SELECT SYS_XMLGEN(tagok) FROM csapat WHERE nev='Fradi';
----------------------------
Albert Flori
Kicsi
Bp
Magyar
1234
Novak Dezso
Nagy
Vac
Magyar
4321
SYS_XMLAGG(kif [,fmt]) függvény
-------------------------------
A kifejezés által meghatározott dokumentumokból vagy töredékekből (az összes sorból) egy XML dokumentumot
hoz létre. Ennek is lehet formázó objektum paramétert megadni.
SELECT SYS_XMLAGG(SYS_XMLGEN(dnev)).getStringVal() FROM dolgozo WHERE ROWNUM < 3;
---------------------
SMITH
ALLEN
Vagy ugyanez formázás megadásával:
SELECT SYS_XMLAGG(SYS_XMLGEN(dnev),
SYS.XMLGenFormatType.CreateFormat('SOROK')).getStringVal() FROM dolgozo WHERE ROWNUM < 3;
---------------------
SMITH
ALLEN
DBMS_XMLGEN csomag
------------------
-- CREATE TABLE tempXML (azon NUMBER(4), o sys.xmltype);
A relációs lekérdezés eredményéből úgy csinál dokumentumot, hogy minden sorból
lesz egy elem, a soron belüli oszlopokból pedig a sor alatt egy elem.
A sor-elem nevét is megadhatjuk, ha NULL-ra állítjuk, akkor nem lesz külön elem a sorokból.
Azt is megtehetjük, hogy a lekérdezésnek minden 3 (vagy tetszőleges n) sorából egy külön
dokumentum készüljön. Részleteket lásd -> a package definícióban.
DECLARE
kornyezet DBMS_XMLGEN.ctxHandle;
eredmeny CLOB;
BEGIN
kornyezet := DBMS_XMLGEN.newContext(
'SELECT region_name, country_name, city, street_address
FROM hr.regions r, hr.countries c, hr.locations l
WHERE l.country_id=c.country_id AND c.region_id=r.region_id AND region_name=''Europe'' ');
DBMS_XMLGEN.setRowSetTag(kornyezet, 'XMLDOK');
DBMS_XMLGEN.setRowTag(kornyezet, 'SOR');
eredmeny := DBMS_XMLGEN.getXML(kornyezet);
INSERT INTO tempXML VALUES(1, SYS.XMLType.CreateXML(eredmeny));
COMMIT;
DBMS_XMLGEN.closeContext(kornyezet);
END;
------------------------------------------------------------
Europe
Italy
Roma
1297 Via Cola di Rie
Europe
Italy
Venice
93091 Calle della Testa
...
Objektum-relációs tábla esetén viszont a dokumentum tükrözi az objektumok szerkezetét.
Minden objektum típusból egy elem lesz, az attribútumokból pedig egy leszármazott elem.
A '@'-al kezdődő oszlopnevekből és attribútumnevekből (objektumok attribútumaiból)
az adott XML elem attribútuma lesz.
Az alábbi példa rögtön XMLType typusú eredményt ad vissza és nem CLOB-ot, mint az előző.
DECLARE
kornyezet DBMS_XMLGEN.ctxHandle;
eredmeny SYS.XMLType;
BEGIN
kornyezet := DBMS_XMLGEN.newContext('SELECT nev "@nev",tagok FROM csapat');
DBMS_XMLGEN.setRowSetTag(kornyezet, 'XMLDOK');
DBMS_XMLGEN.setRowTag(kornyezet, 'CSAPAT');
eredmeny := DBMS_XMLGEN.getXMLType(kornyezet);
INSERT INTO tempXML VALUES(2, eredmeny);
COMMIT;
DBMS_XMLGEN.closeContext(kornyezet);
END;
SELECT o AS csapatok FROM tempxml WHERE azon=2;
CSAPATOK
------------------------------
Albert Flori
Kicsi
Bp
Magyar
1234
Novak Dezso
Nagy
Vac
Magyar
4321
...
EXTRACT(XMLType, XPath)
-----------------------
Az XPath kifejezés által megjelölt elemeket (csomópontokat, részfákat) adja vissza
dokumentumtöredékként.
SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Docks') o60
FROM oe.warehouses WHERE warehouse_spec IS NOT NULL;
WAREHOUSE_NAME O60
----------------------------------- ----------------
Southlake, Texas 2
San Francisco 1
New Jersey
Seattle, Washington 3
EXTRACTVALUE(XMLType, XPath)
----------------------------
Hasonló az EXTRACT-hez, de ez az eredményt skalár értékként adja vissza. Épp ezért
az XPath által megadott kifejezésnek egyetlen szöveges csomópontot vagy attribútumot
kell eredményeznie, ellenkező esetben hibaüzenetet kapunk.
SELECT warehouse_name, EXTRACTVALUE(warehouse_spec, '/Warehouse/Docks') o60
FROM oe.warehouses WHERE warehouse_spec IS NOT NULL;
WAREHOUSE_NAME O60
----------------------------------- ---
Southlake, Texas 2
San Francisco 1
New Jersey
Seattle, Washington 3
Lekérdezhetünk vele levélcsomópontot vagy attribútumot.
SELECT EXTRACTVALUE(kolcs_spec, '//Konyv[@cim="Tuskevar"]/Ar') o60 FROM kolcsonzes WHERE azon=1;
----
2500
SELECT EXTRACTVALUE(kolcs_spec, '//Konyv[@cim="Tuskevar"]/@szerzo') o60 FROM kolcsonzes WHERE azon=1;
-------------
Fekete Istvan
EXISTSNODE(XMLType, XPath)
--------------------------
0 vagy 1 értékkel tér vissza attól függően, hogy a megadott XPath kifejezés eredményezett-e
valamilyen csomópontot vagy attribútumot.
SELECT warehouse_id, warehouse_name FROM oe.warehouses
WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1;
WAREHOUSE_ID WAREHOUSE_NAME
------------ -------------------
1 Southlake, Texas
2 San Francisco
4 Seattle, Washington
XMLELEMENT(Elemnév [,érték] [,attribútumok])
--------------------------------------------
Hasonló a SYS_XMLGen-hez, egy XML elemet hoz létre. Itt megadhatunk attribútumokat is,
és egymásba ágyazva is meghívhatjuk a függvényt.
Az első paraméter az elem nevét adja meg az alábbi példában.
SELECT XMLELEMENT("last_name", e.last_name).getStringval() FROM hr.employees e
WHERE e.employee_id IN (205, 206);
------------------------------
Gietz
Higgins
Attribútumot és beágyazott elemet hozunk létre. A második attribútum nevét az oszlopnévből
származtatja, ha nem adtuk meg.
SELECT XMLELEMENT("Emp",
XMLATTRIBUTES(e.employee_id AS "ID", e.last_name),
XMLELEMENT("Dept", e.department_id),
XMLELEMENT("Salary", e.salary)) o60
FROM hr.employees e
WHERE e.employee_id = 206;
O60
--------------------------------
110
8300
Az alábbi lekérdezés kicsit még összetettebb. Fontos, hogy a beágyazott SELECT csak egyetlen
sorral térjen vissza, különben hibás lenne. Az alias ("Dept_name") megadása is kötelező,
különben nem tudná elnevezni az attribútumot. A beágyazott SELECT mellett további SELECT-ek is
szerepelhetnének még, pl. a location_id-t is lekérdezhetnénk még.
SELECT XMLELEMENT("Emp", XMLATTRIBUTES(e.employee_id, e.last_name),
XMLELEMENT("Dept", XMLATTRIBUTES(e.department_id,
(SELECT d.department_name FROM hr.departments d
WHERE d.department_id = e.department_id) as "Dept_name")),
XMLELEMENT("salary", e.salary),
XMLELEMENT("Hiredate", e.hire_date)) AS "Emp Element"
FROM hr.employees e
WHERE employee_id = 205;
----------------------------------------------------------
12000
1994-06-07
XMLCONCAT(XMLType, XMLType, ...)
---------
Összefűzi a paraméterül kapott elemeket egy sorozattá. Épp az ellenkezőjét csinálja,
mint az XMLSequence.
SELECT employee_id, XMLCONCAT(XMLELEMENT("First", e.first_name), XMLELEMENT("Last", e.last_name)) o60
FROM hr.employees e
WHERE e.employee_id > 202;
EMPLOYEE_ID O60
----------- ------------------------------------------
203 SusanMavris
204 HermannBaer
205 ShelleyHiggins
206 WilliamGietz
XMLSEQUENCE(XMLType)
--------------------
A dokumentumtöredékekből dinamikus tömböt (VARRAY) hoz létre, amelynek elemei XML dokumentumok.
SELECT EXTRACT(kolcs_spec, '//Konyv/@cim').getStringVal() FROM kolcsonzes WHERE azon=1;
------------
MomoTuskevar
Az alábbi VARRAY-t ad vissza, amit a kliens programok nem tudnak megjeleníteni.
A lekérdezés(ek) eredménye egyébként 1 sor, amiben egy XML-eket tartalmazó VARRAY van.
Valójában ez egy üres VARRAY lesz, ahogy majd lejjebb látni fogjuk, mivel nem dokumentumtöredék
amivel fel kellene tölteni.
SELECT XMLSequence(EXTRACT(k.kolcs_spec, '//Konyv/@cim')) FROM kolcsonzes k WHERE azon=1;
SELECT XMLSequence(EXTRACT(k.kolcs_spec, '//Konyv/AR/text()')) FROM kolcsonzes k WHERE azon=1;
A tömböt táblává alakíthatjuk és lekérdezhetjük. Mivel a TABLE által létrejött táblának
nincs oszlopa, ezért azt a VALUE(t) hivatkozással vagy COLUMN_VALUE hivatkozással kérdezhetjük le.
A lekérdezés 2 sort ad vissza.
SELECT value(t)
FROM TABLE(SELECT XMLSequence(EXTRACT(k.kolcs_spec, '//Konyv')) FROM kolcsonzes k WHERE azon=1) t;
---------------------------------------------
2000
2500
Az alábbi lekérdezés is 2 sort ad vissza. A VARRAY 2 XML elemből áll.
SELECT value(t)
FROM TABLE(SELECT XMLSequence(EXTRACT(k.kolcs_spec, '//Konyv/Ar')) FROM kolcsonzes k WHERE azon=1) t;
-------------
2000
2500
Az attribútumokból vagy szöveges tartalmakból azonban 0 elemű tömb lesz, vagyis ezekkel nem tölti
fel a VARRAY-t az XMLSequence.
SELECT count(*)
FROM TABLE(SELECT XMLSequence(EXTRACT(k.kolcs_spec, '//Konyv/Ar/text()')) FROM kolcsonzes k WHERE azon=1) t;
-------
0
SELECT count(*)
FROM TABLE(SELECT XMLSequence(EXTRACT(k.kolcs_spec, '//Konyv/@cim')) FROM kolcsonzes k WHERE azon=1) t;
-------
0
Külső XML adatok beolvasása
---------------------------
Az alábbi utasítás egy külső fájlból beolvasott adatokat XML-lé konvertál.
A BFILENAME függvény egy lokátort ad vissza, amit az XMLType konstruktor függvény
használ fel.
SELECT XMLType(bfilename('INFOKEZ3', 'kolcsonzesek.xml'), nls_charset_id('EE8ISO8859P2')).getStringVal()
FROM dual;
Ez pedig be is teszi egy táblába a külső fájlból olvasott XML-t.
INSERT INTO tempXML
VALUES (2, XMLType(bfilename('INFOKEZ3', 'kolcsonzesek.xml'), nls_charset_id('EE8ISO8859P2')));
XML adatok módosítása
---------------------
Hozzunk létre még egy sort a táblában, amelyben most csak Gipsz Jakab kölcsönzései
lesznek benne. A továbbiakban ezt a 2-es azonosítójú sort fogjuk módosítgatni.
INSERT INTO kolcsonzes
SELECT 2, EXTRACT(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]') FROM kolcsonzes;
DELETEXML(XMLType, XPath) -- kitörli a megadott csomópontokat vagy attribútumokat.
-------------------------
-- Töröljük ki a Momo-ra vonatkozó kölcsönzést
SELECT DELETEXML(kolcs_spec, '//Konyv[@cim="Momo"]') FROM kolcsonzes WHERE azon=2;
-- Töröljük ki a könyvek 'cim' attribútumait
SELECT DELETEXML(kolcs_spec, '//Konyv/@cim') FROM kolcsonzes WHERE azon=2
APPENDCHILDXML(XMLType, XPath, Value)
-------------------------------------
a megadott helyekre csomópontokat szúr be azok gyermek csomópontjaként
-- Szúrjunk be egy újabb könyvet Gipsz Jakab Konyvek csomópontja alá
-- a könyvek között a most beszúrt lesz az utolsó
SELECT APPENDCHILDXML(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]/Konyvek', XMLType(
'
2400
'))
FROM kolcsonzes WHERE azon=2;
egy gyermek nélküli csomópontot megadhatunk az alábbi rövidített módon is
SELECT APPENDCHILDXML(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]/Konyvek', XMLType(
''))
FROM kolcsonzes WHERE azon=2;
INSERTCHILDXML(XMLType, XPath, Value, XML)
------------------------------------------
A 3. paraméterben megadott típusú csomópontok közé szúr be utolsónak még egyet
az XPath kifejezés által kijelölt csomópont gyermekeként.
Új DVD-k csomópont beszúrása
SELECT INSERTCHILDXML(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]', 'DVD-k',
XMLType('
Kill Bill
'))
FROM kolcsonzes WHERE azon=2;
Új DVD-k csomópont más szerkezettel, új attribútummal
SELECT INSERTCHILDXML(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]', 'DVD-k',
XMLType(''))
FROM kolcsonzes WHERE azon=2;
Új CD-k csomópont beszúrása
SELECT INSERTCHILDXML(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]', 'CD-k',
XMLType('
Kapuk
3000
'))
FROM kolcsonzes WHERE azon=2;
INSERTXMLBEFORE(XMLType, XPath, XML)
------------------------------------
Az XPath kifejezés által megadott csomópontok elé szúr be egy elemet
Szúrjunk be a két könyv közé még egyet
SELECT INSERTXMLBEFORE(kolcs_spec, '//Kolcsonzo[@nev="Gipsz Jakab"]//Konyv[2]', XMLType(
'
2400
'))
FROM kolcsonzes WHERE azon=2;
Minden Ar csomopont elé szúrjunk be
SELECT INSERTXMLBEFORE(kolcs_spec, '//Ar', XMLType('Blabla'))
FROM kolcsonzes WHERE azon=2;
UPDATEXML(XMLType, XPath, Value)
--------------------------------
A megadott részt módosítja a dokumentumon belül. Módosíthatunk attribútum értéket,
elem tartalmat is.
Cseréljük le a Tuskevar könyvet egy másikra
SELECT UPDATEXML(kolcs_spec, '//Konyv[@cim="Tuskevar"]', XMLType(
'
2400
'))
FROM kolcsonzes WHERE azon=2;
Módosítsuk a Tuskevar konyv címét
SELECT UPDATEXML(kolcs_spec, '//Konyv[@cim="Tuskevar"]/@cim', 'Uj_tuskevar')
FROM kolcsonzes WHERE azon=2;
Állítsuk üres-re a cim attribútumot
SELECT UPDATEXML(kolcs_spec, '//Konyv[@cim="Tuskevar"]/@cim', NULL)
FROM kolcsonzes WHERE azon=2;
Az alábbi üres elemet csinál a Tuskevar könyvből
SELECT UPDATEXML(kolcs_spec, '//Konyv[@cim="Tuskevar"]', NULL)
FROM kolcsonzes WHERE azon=2;