BEGIN
 IF DBMS_XDB.existsResource('/public/emp.xml') 
   THEN DBMS_XDB.deleteResource('/public/emp.xml',DBMS_XDB.DELETE_FORCE); 
  END IF; 
END; 
/


select any_path 
  from resource_view r
 where under_path(res,'/home/OE') = 1
   and extractValue(r.res,'/Resource/DisplayName') like '%.xml'
/



SELECT xmlelement( "Emp",
    xmlattributes(empno, ename as "Name"),
    xmlforest(job, sal as "salary", mgr as "manager"))
FROM emp e WHERE empno = 7369;

INSERT INTO xml_prezidenti VALUES (SYS.XMLType.createxml('<?xml version="1.0"?>
  <prezidenti>
    <id>1</id>
    <meno>George Washington</meno>
    <od>1789</od>
    <do>1797</do>
  </prezidenti>')
);

INSERT INTO xml_prezidenti VALUES (SYS.XMLType.createxml('<?xml version="1.0"?>
  <prezidenti>
    <id>2</id>
    <meno>John Adams</meno>
    <od>1797</od>
    <do>1801</do>
  </prezidenti>')
);

CREATE TABLE xml_dokumenty 
( 
   nazev VARCHAR2(15), 
   dokument SYS.XMLType
); 


INSERT INTO xml_dokumenty VALUES ('prezidenti',SYS.XMLType.createxml('<?xml version="1.0"?>
<prezidenti>
  <prezident>
    <id>1</id>
    <meno>George Washington</meno>
    <od>1789</od>
    <do>1797</do>
  </prezident>
  <prezident>
    <id>2</id>
    <meno>John Adams</meno>
    <od>1797</od>
    <do>1801</do>
  </prezident>
</prezidenti>')
);




INSERT INTO xml_dokumenty VALUES ('hp_knihy', SYS.XMLType.createxml('<?xml version="1.0"?>
<knihy>
 <kniha>
    <autor>Rowlling J.K.</autor> 
    <titul>Harry Potter a tajomna komnata</titul> 
    <cena>249</cena> 
  </kniha>
  <kniha>
    <autor> Rowlling J.K.</autor> 
    <titul> Harry Potter a kamen mudrcov</titul> 
    <cena>299</cena> 
  </kniha>
  <kniha>
    <titul>Bible</titul> 
    <cena>399</cena> 
  </kniha>
</knihy>')
);



CREATE TABLE xml_knihy 
( 
   isbn VARCHAR2(13), 
   kniha SYS.XMLType
); 



INSERT INTO xml_knihy VALUES ('80-7226-555-5',
SYS.XMLType.createxml('<?xml version="1.0"?>
<kniha>
    <autor>Lacko Luboslav</autor> 
    <titul>Web a databze</titul> 
    <cena>297</cena> 
</kniha>')
);

INSERT INTO xml_knihy VALUES ('80-7226-360-9',
SYS.XMLType.createxml('<?xml version="1.0"?>
<kniha>
    <autor>Riordan Rebecca M.</autor> 
    <titul>Vytvme relan databzov aplikace</titul> 
    <cena>499</cena> 
</kniha>')
);

INSERT INTO xml_knihy VALUES ('80-7226-525-3',
SYS.XMLType.createxml('<?xml version="1.0"?>
<kniha>
    <autor>Gunnerson Eric</autor> 
    <titul>Zanme programovat v C#</titul> 
    <cena>390</cena> 
</kniha>')
);


SELECT k.kniha.extract('//autor/text()').getStringVal() AS autor,
       k.kniha.extract('//titul/text()').getStringVal() AS titul,
       k.kniha.extract('//cena/text()').getNumVal() AS cena
 FROM xml_knihy k
  WHERE k.kniha.extract('//titul/text()').getStringVal() LIKE '%databz%';


CREATE TABLE narodne_parky 
( 
   id NUMBER, 
   nazov VARCHAR2(30), 
   telefon VARCHAR2(20), 
   skratka VARCHAR2(10) 
);


INSERT INTO narodne_parky 
  VALUES (1, 'Nrodn park Nzke Tatry', '+421-48-4130888', 'NAPANT'); 
INSERT INTO narodne_parky 
 VALUES (2, 'Pieninsky narodny park', '+421-52-4181071', 'PIENAP'); 
INSERT INTO narodne_parky 
  VALUES (3, 'Tatransk nrodn park', '+421-52/4484 217 ', 'TANAP');



SELECT XMLQuery(
   '<parky>
    {for $c in ora:view("narodne_parky")
     let $np_skratka := $c/ROW/SKRATKA/text(),
         $np_nazov := $c/ROW/NAZOV,
         $np_telefon := $c/ROW/TELEFON/text()
     order by $np_skratka
     return
        <park>
           <skratka>{$np_skratka}</skratka>
           <kontakt>="{$np_telefon}">{$np_nazov/text()}</kontakt>
        </park>}
    </parky>' RETURNING CONTENT)
FROM dual;


create table PrezidentiUSA
(
   poradie number,
   prezident xmltype
);


insert into PrezidentiUSA values (1,
xmltype(
'<prezident>
    <meno>George Washington</meno>
    <od>1789</od>
    <do>1797</do>
  </prezident>')
)
/

insert into PrezidentiUSA values (2,
xmltype(
'<prezident>
    <meno>John Adams</meno>
    <od>1797</od>
    <do>1801</do>
  </prezident>')
)
/


create or replace view DPTX1 of xmltype
with object id
(extract(object_value, '/Department/@DepartmentId').getnumberVal())
as
select column_value from XMLTable
       ('for $d in ora:view("DEPARTMENTS"), 
		 $l in ora:view("LOCATIONS"),
		 $c in ora:view("COUNTRIES")
	   where $d/ROW/LOCATION_ID = $l/ROW/LOCATION_ID
		and $l/ROW/COUNTRY_ID = $c/ROW/COUNTRY_ID 
	   return
	   <Department DepartmentId= "{$d/ROW/DEPARTMENT_ID/text()}" >
		<Name>{$d/ROW/DEPARTMENT_NAME/text()}</Name>
		<Location>
		  <Address>{$l/ROW/STREET_ADDRESS/text()}</Address>
		  <City>{$l/ROW/CITY/text()}</City>
		  <State>{$l/ROW/STATE_PROVINCE/text()}</State>
		  <Zip>{$l/ROW/POSTAL_CODE/text()}</Zip>
		  <Country>{$c/ROW/COUNTRY_NAME/text()}</Country>
		</Location>
	    </Department>')
/


create or replace view DPTX2 of xmltype
with object id
(extract(object_value, '/Department/@DepartmentId').getnumberVal())
as
select column_value from XMLTable
       ('for $d in ora:view("DEPARTMENTS") 
	   return
	   <Department DepartmentId= "{$d/ROW/DEPARTMENT_ID/text()}" >
		<Name>{$d/ROW/DEPARTMENT_NAME/text()}</Name>
		<EmployeeList>
		{
		   for $e in ora:view("EMPLOYEES"),
		  	 $m in ora:view("EMPLOYEES"),
			 $j in ora:view("JOBS")
		   where $e/ROW/DEPARTMENT_ID = $d/ROW/DEPARTMENT_ID
		      and $j/ROW/JOB_ID = $e/ROW/JOB_ID
			and $m/ROW/EMPLOYEE_ID = $e/ROW/MANAGER_ID
		   return
		   <Employee employeeNumber="{$e/ROW/EMPLOYEE_ID/text()}" >
			<FirstName>{$e/ROW/FIRST_NAME/text()}</FirstName>
			<LastName>{$e/ROW/LAST_NAME/text()}</LastName>
			<JobTitle>{$j/ROW/JOB_TITLE/text()}</JobTitle>
		    </Employee>
		 }
		 </EmployeeList>
	    </Department>')
/


declare
  res boolean;
  xml_adresar varchar2(256) :=  '/home/OE/XML_Departments';
begin
  res := dbms_xdb.createFolder(xml_adresar);
end;
/


declare
  res boolean;
  xml_adresar varchar2(256) :=  '/home//OE/XML_Departments';
begin
  if dbms_xdb.existsResource(xml_adresar) then
     dbms_xdb.deleteResource(xml_adresar,dbms_xdb.DELETE_RECURSIVE);
  end if;
  res := dbms_xdb.createFolder(xml_adresar);
end;
/




declare
  res boolean;
  cursor curDepartments is
    select xtab.name NAME, ref(d) XMLREF
    from DPTX1 d, xmltable(
     'for $i in . return $i/Department/Name' PASSING d.object_value
      COLUMNS name VARCHAR2(30) PATH '/Name') xtab;
begin
  for dept in curDepartments loop
    res := DBMS_XDB.createResource('/home//OE/XML_Departments' || '/' || dept.NAME || '.xml', dept.XMLREF);
  end loop;
end;
/




