/*
Vytvořte tabulku XML_OBCE(ID, DATA XMLTYPE).
Pro naplnění tabulky použijte data získaná v předchozích cvičeních.
Hodnoty atributu data budou ve tvaru:

<?xml version="1.0" encoding="UTF-8"?>
<OBEC ID="954" NAZEV="Mladkov">
  <CASTI_OBCE>
    <COBCE ID="2102" NAZEV="Dolany"></COBCE>
    <COBCE ID="2848" NAZEV="Mladkov (část)"></COBCE>
    <COBCE ID="4953" NAZEV="Petrovičky"></COBCE>
    <COBCE ID="9384" NAZEV="Vlčkovice"></COBCE>
</CASTI_OBCE>
<OKRES OKRES_KOD="3611">Ústí nad Orlicí</OKRES>
</OBEC>
*/
select sys_xmlagg(XMLELEMENT("COBCE", XMLATTRIBUTES(co.id_cobce ID, co.naz_cobce NAZEV)), xmlformat('CASTI_OBCE')) from cv04_cobce co where co.id_obce = 439;
--
SELECT XMLELEMENT("OBEC", XMLATTRIBUTES(o.id_obce ID, o.naz_obce NAZEV), 
(SELECT sys_xmlagg(XMLELEMENT("COBCE", XMLATTRIBUTES(co.id_cobce ID, co.naz_cobce NAZEV)), xmlformat('CASTI_OBCE')) FROM cv04_cobce co WHERE co.id_obce = o.id_obce)
)
FROM cv04_obce o
WHERE o.naz_obce = 'Pardubice'; 
--
SELECT o.id_obce, xmlroot(XMLELEMENT("OBEC", XMLATTRIBUTES(o.id_obce ID, o.naz_obce NAZEV), 
(SELECT sys_xmlagg(XMLELEMENT("COBCE", XMLATTRIBUTES(co.id_cobce ID, co.naz_cobce NAZEV)), xmlformat('CASTI_OBCE')) FROM cv04_cobce co WHERE co.id_obce = o.id_obce),
(SELECT XMLELEMENT("OKRES", XMLATTRIBUTES(ok.kod_okresu OKRES_KOD),ok.naz_okresu) FROM cv04_okres ok WHERE ok.kod_okresu = o.kod_okresu)
), VERSION '1.0" encoding="UTF8') DATA
FROM cv04_obce o
WHERE o.naz_obce = 'Pardubice'; 

-- final vytvoření tabulky
CREATE TABLE XML_OBCE AS 
SELECT o.id_obce ID, xmlroot(XMLELEMENT("OBEC", XMLATTRIBUTES(o.id_obce ID, o.naz_obce NAZEV), 
(SELECT sys_xmlagg(XMLELEMENT("COBCE", XMLATTRIBUTES(co.id_cobce ID, co.naz_cobce NAZEV)), xmlformat('CASTI_OBCE')) FROM cv04_cobce co WHERE co.id_obce = o.id_obce),
(SELECT XMLELEMENT("OKRES", XMLATTRIBUTES(ok.kod_okresu OKRES_KOD),ok.naz_okresu) FROM cv04_okres ok WHERE ok.kod_okresu = o.kod_okresu)
), VERSION '1.0" encoding="UTF8') DATA
FROM cv04_obce o;

select count(*) from xml_obce; -- 6250 rows selected

/*
1) Nad touto tabulkou vytvořte pohled OBCE_OKRESY(ID,NAZEV), který bude obsahovat obce v okrese Kolín a Havlíčkův Brod
Př:

ID                     NAZEV
*/
SELECT id, xo.DATA.EXTRACT('/OBEC/@NAZEV').getStringVal() 
FROM xml_obce xo
where xo.DATA.EXTRACT('/OBEC/OKRES/text()').getStringVal()  in ('Kolín', 'Havlíčkův Brod');

-- final pohled
CREATE VIEW OBCE_OKRESY AS 
SELECT id, (xo.DATA.EXTRACT('/OBEC/@NAZEV').getStringVal()) nazev 
FROM xml_obce xo
where xo.DATA.EXTRACT('/OBEC/OKRES/text()').getStringVal()  in ('Kolín', 'Havlíčkův Brod');

/*
Pomocí technologie XQuery:

Vytvořte nad tabulkou XML_OBCE pohled POCTY_CASTI, který bude obsahovat atributy ID, NAZEV, POCET_CASTI, DATA, kde atribut DATA představuje obsah elementu CASTI_OBCE:

<COBCE ID="2226" NAZEV="Hlinky"/>
<COBCE ID="6773" NAZEV="Nové Stanovice"/>
<COBCE ID="14775" NAZEV="Stanovice"/> 

a atribut POCET_CASTI počet elementů.
*/
create view POCTY_CASTI as
SELECT ID, xo.DATA.EXTRACT('/OBEC/@NAZEV').getStringVal(), (SELECT XMLQuery('for $i in $p/OBEC/CASTI_OBCE let $content := $i return $content' passing xml.DATA AS "p" RETURNING CONTENT) FROM xml_obce xml where xml.id = xo.ID) DATA
from xml_obce xo

select XMLQuery('for $i in $p/OBEC/CASTI_OBCE let $content := $i return $content' passing xml.data as "p" returning content) from xml_obce xml;
