drop table NACHRICHTEN_X; 

CREATE TABLE NACHRICHTEN_X (
  "NR_X"          NUMBER(10), 
  "NR_NACHRICH_X" CLOB
);
 
insert into NACHRICHTEN_X 
  VALUES (10,'<Adresse><Name>Karin</Name><Ort>Hamburg</Ort></Adresse>');

commit; 

set long5000;
SELECT * FROM NACHRICHTEN_X;

--- -----------------------------------------------------------------------

drop table NACHRICHTEN_U;

CREATE TABLE NACHRICHTEN_U (
  "NR_I"        NUMBER(10), 
  "NR_NACHRICH" XMLTYPE
); 

insert into NACHRICHTEN_U
  VALUES(10, xmltype('<Adresse><Name>Peter</Name><Ort>Bremen</Ort></Adresse>'));

insert into NACHRICHTEN_U
  VALUES(20, xmltype('<Adresse><Name>Theo</Name><Ort>Hamburg</Ort></Adresse>'));

insert into NACHRICHTEN_U
  VALUES(30, xmltype('<Adresse><Name>Peter</Name><Ort>Hamburg</Ort></Adresse>'));
 

insert into NACHRICHTEN_U
  VALUES(50, xmltype('<Adresse><Name>Petra</Name><Ort>Bremen</Ort></Adresse>'));

insert into NACHRICHTEN_U
  VALUES(60, xmltype('<Adresse><Name>Theo</Name><Ort>Bremen</Ort></Adresse>'));

insert into NACHRICHTEN_U
  VALUES(70, xmltype('<Adresse><Name>Karin</Name><Ort>Hannover</Ort></Adresse>'));

commit;

set long 5000; 
select * from NACHRICHTEN_U; 

select n.NR_NACHRICH.getClobVal() as Adresse
  from NACHRICHTEN_U n; 

select n.NR_NACHRICH.getClobVal() as Adresse 
  from NACHRICHTEN_U n 
  where NR_I=30;

select NR_I as Nummer, n.NR_NACHRICH.getClobVal() as Adresse
  from NACHRICHTEN_U n 
  where NR_I=30;

--- -----------------------------------------------------------------------

update NACHRICHTEN_U
  set NR_NACHRICH=XMLTYPE (
  '<Adresse><Name>Gert</Name><Ort>Frankfurt</Ort></Adresse>')
  where NR_I=30;

select * from NACHRICHTEN_U where NR_I=30;

--- -----------------------------------------------------------------------

select n.NR_NACHRICH.getClobVal() as Adresse 
  from NACHRICHTEN_U n 
  where NR_I=20; 


update NACHRICHTEN_U 
  set NR_NACHRICH=UPDATEXML(NR_NACHRICH,'/Adresse/Name/text()','Hugo')
  where NR_I=20;

commit;

select n.NR_NACHRICH.getClobVal() as Adresse 
  from NACHRICHTEN_U n 
  where NR_I=20; 

--- -----------------------------------------------------------------------

ALTER TABLE NACHRICHTEN_U add (SONSTIGES XMLType); 

insert into NACHRICHTEN_U
  values(10, XMLTYPE('<Adresse><Name>Karin</Name><Ort>Hamburg</Ort></Adresse>'),
             XMLTYPE('<Hobby>Lesen,Theater,Tennis</Hobby>'));

select * from NACHRICHTEN_U;
 
select * from NACHRICHTEN_U where NR_I=10;

ALTER TABLE NACHRICHTEN_U drop(SONSTIGES);

select * from NACHRICHTEN_U;

drop table NACHRICHTEN_U;


--- -----------------------------------------------------------------------

select n.NR_I, 
  extractValue(n.NR_NACHRICH,'/Adresse/Name')
  from NACHRICHTEN_U n;

select n.NR_I, extractValue(n.NR_NACHRICH,'/Adresse/Name') as Name
  from NACHRICHTEN_U n
  where existsNode(n.NR_NACHRICH,'/Adresse[Ort="Bremen"]')=1;

--- -----------------------------------------------------------------------

create or replace view v_NR_NACHRICH 
  as select n.NR_I, extractValue(n.NR_NACHRICH,'/Adresse/Name') as name 
  from NACHRICHTEN_U n;

select * from v_NR_NACHRICH;

--- -----------------------------------------------------------------------
 
drop table Nachrich_rel_tab;
 
CREATE TABLE Nachrich_rel_tab ( 
  Name varchar2(20), 
  Ort varchar2(20)
);

INSERT INTO Nachrich_rel_tab 
  SELECT n.NR_NACHRICH.extract('/Adresse/Name/text()').getstringval() as Name, 
         n.NR_NACHRICH.extract('/Adresse/Ort/text()').getstringval() as Ort
  FROM NACHRICHTEN_U n;

commit;

select * from Nachrich_rel_tab;

drop table NACHRICHTEN_U; 

--- -----------------------------------------------------------------------

select * from Nachrich_rel_tab;

select XMLELEMENT(Personen, NT.Name||' '||NT.Ort) as RESULT
  from Nachrich_rel_tab NT;

select XMLELEMENT(Personen, XMLAGG(XMLELEMENT(PAngabe, NT.Name||' '||NT.Ort))) as RESULT
  from Nachrich_rel_tab NT;

select XMLELEMENT(Personen, XMLATTRIBUTES(Ort as "Wohnort"), XMLELEMENT(PAngabe, NT.Name)) as RESULT
  from Nachrich_rel_tab NT;