--- students table 

SELECT  S.Id,  XMLELEMENT (NAME  "Name",S.Name) AS  Info FROM  Students S;

SELECT  XMLELEMENT ( NAME "Student",   XMLATTRIBUTES(S.Id AS "Id"),  XMLAGG(XMLELEMENT(Name "CrsTaken", XMLATTRIBUTES(T.CrsCode AS "CrsCode", T.Semester AS "Semester") )  ORDER BY  T.CrsCode)  )  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId  GROUP BY S.Id


SELECT  XMLELEMENT ( NAME "Student",   XMLATTRIBUTES(S.Id AS "Id"),  XMLELEMENT(Name "CrsTaken", XMLATTRIBUTES(T.CrsCode AS "CrsCode", T.Semester AS "Semester") )) FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId  GROUP BY S.Id



SELECT  XMLELEMENT ( NAME "Student",   XMLATTRIBUTES(S.Id AS "Id"),  XMLAGG(XMLELEMENT(Name "CrsTaken",T.CrsCode AS "Code")  ORDER BY  T.CrsCode))  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId  GROUP BY S.Id



SELECT  S.Id,  XMLELEMENT (NAME  "Name",XMLATTRIBUTES (S.Name AS "Name"),S.Address) AS  Info FROM  Students S;

select XMLELEMENT(NAME "Student", XMLATTRIBUTES(T.StudID AS "ID"), 
       XMLELEMENT (select 
       XMLELEMENT(NAME "Class", XMLATTRIBUTES(S1.CrsCode AS "Code", S1.Semester AS "Semester", S1.Grade AS "Grade"))
       from transcripts S1 
       where S1.StudID = T.StudId)
from Transcripts T;   

select xmlquery('<students> <ID>{$I1}</ID> </students>' PASSING by Value  S.ID as I1 RETURNING sequence) AS selem from students S;

==========================


select XMLELEMENT(NAME "Student", XMLATTRIBUTES(T.StudID AS "ID")) from transcripts t;


XMLELEMENT(NAME"STUDENT",XMLATTRIBUTES(T.STUDIDAS"ID"))
--------------------------------------------------------------------------------
<Student ID="111111111"></Student>
<Student ID="222222222"></Student>
<Student ID="222222222"></Student>


select XMLELEMENT(NAME "Student", XMLATTRIBUTES(S.ID AS "ID")) from Students S, Transcripts T where T.StudId = S.Id;

SQL> select XMLELEMENT(NAME "Student", XMLATTRIBUTES(S.ID AS "ID")) from Students S, Transcripts T where T.StudId = S.Id;

XMLELEMENT(NAME"STUDENT",XMLATTRIBUTES(S.IDAS"ID"))
--------------------------------------------------------------------------------
<Student ID="111111111"></Student>
<Student ID="222222222"></Student>
<Student ID="222222222"></Student>


select  XMLELEMENT(NAME "Class", XMLATTRIBUTES(S1.CrsCode AS "Code", S1.Semester AS "Semester", S1.Grade AS "Grade")) from transcripts S1; 

SQL> select  XMLELEMENT(NAME "Class", XMLATTRIBUTES(S1.CrsCode AS "Code", S1.Semester AS "Semester", S1.Grade AS "Grade")) from transcripts S1;

XMLELEMENT(NAME"CLASS",XMLATTRIBUTES(S1.CRSCODEAS"CODE",S1.SEMESTERAS"SEMESTER",
--------------------------------------------------------------------------------
<Class Code="CS110     " Semester="Fall2005    " Grade="B"></Class>
<Class Code="CS482     " Semester="Fall2004    " Grade="A"></Class>
<Class Code="CS272     " Semester="Fall2002    " Grade="B"></Class>


select  XMLELEMENT(NAME "Semester", S1.Semester) from transcripts S1;

SQL> select  XMLELEMENT(NAME "Semester", S1.Semester) from transcripts S1;

XMLELEMENT(NAME"SEMESTER",S1.SEMESTER)
--------------------------------------------------------------------------------
<Semester>Fall2005    </Semester>
<Semester>Fall2002    </Semester>
<Semester>Fall2004    </Semester>


select XMLELEMENT(NAME "Student", XMLATTRIBUTES(S.ID AS "ID")) from Students S where xmlexists(select  XMLELEMENT(NAME "Semester", S1.Semester) from transcripts S1);  --- xmlexists does not work  


SQL> select XMLELEMENT(NAME "Student", XMLATTRIBUTES(S.ID AS "ID")) from Students S where xmlexists(select  XMLELEMENT(NAME "Semester", S1.Semester) from transcripts S1);
select XMLELEMENT(NAME "Student", XMLATTRIBUTES(S.ID AS "ID")) from Students S where xmlexists(select  XMLELEMENT(NAME "Semester", S1.Semester) from transcripts S1)
                                                                                               *
ERROR at line 1:
ORA-00936: missing expression


SQL> SELECT  XMLELEMENT ( NAME "Student", XMLATTRIBUTES(S.Id AS "Id"), XMLAGG(XMLELEMENT(Name "CrsTaken", XMLATTRIBUTES(T.CrsCode AS "CrsCode", T.Semester AS "Semester") )  ORDER BY  T.CrsCode)  )  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId  GROUP BY S.Id

XMLELEMENT(NAME"STUDENT",XMLATTRIBUTES(S.IDAS"ID"),XMLAGG(XMLELEMENT(NAME"CRSTAK
--------------------------------------------------------------------------------
<Student Id="111111111"><CrsTaken CrsCode="CS110     " Semester="Fall2005    "><
<Student Id="222222222"><CrsTaken CrsCode="CS272     " Semester="Fall2002    "><



SELECT  XMLELEMENT ( NAME "Student", XMLATTRIBUTES(S.Id AS "Id"), XMLAGG(XMLELEMENT(Name "CrsTaken", XMLATTRIBUTES(T.CrsCode AS "CrsCode") )  ORDER BY  T.CrsCode)  )  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId GROUP BY S.Id


SQL> SELECT  XMLELEMENT ( NAME "Student", XMLATTRIBUTES(S.Id AS "Id"), XMLAGG(XMLELEMENT(Name "CrsTaken", XMLATTRIBUTES(T.CrsCode AS "CrsCode") )  ORDER BY  T.CrsCode)  )  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId GROUP BY S.Id;

XMLELEMENT(NAME"STUDENT",XMLATTRIBUTES(S.IDAS"ID"),XMLAGG(XMLELEMENT(NAME"CRSTAK
--------------------------------------------------------------------------------
<Student Id="111111111"><CrsTaken CrsCode="CS110     "></CrsTaken></Student>
<Student Id="222222222"><CrsTaken CrsCode="CS272     "></CrsTaken><CrsTaken CrsC


SELECT  XMLELEMENT ( NAME "Student", XMLATTRIBUTES(S.Id AS "Id"), XMLAGG(XMLELEMENT(Name "CT", XMLATTRIBUTES(T.CrsCode AS "CCode") )  ORDER BY  T.CrsCode)  )  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId GROUP BY S.Id


SQL> SELECT  XMLELEMENT ( NAME "Student", XMLATTRIBUTES(S.Id AS "Id"), XMLAGG(XMLELEMENT(Name "CT", XMLATTRIBUTES(T.CrsCode AS "CCode") )  ORDER BY  T.CrsCode)  )  FROM  Students S, Transcripts T  WHERE  S.Id = T.StudId GROUP BY S.Id;

XMLELEMENT(NAME"STUDENT",XMLATTRIBUTES(S.IDAS"ID"),XMLAGG(XMLELEMENT(NAME"CT",XM
--------------------------------------------------------------------------------
<Student Id="111111111"><CT CCode="CS110     "></CT></Student>
<Student Id="222222222"><CT CCode="CS272     "></CT><CT CCode="CS482     "></CT>

Need: Group by in the outside "loop"

XMLAGG applied on the groups - produces for each row in the group one element 
In effect, this query is equivalent to the imaginative query: 


SELECT  XMLELEMENT ( NAME "Student", XMLATTRIBUTES(S.Id AS "Id"), 
	SELECT XMLELEMENT(Name "CT", XMLATTRIBUTES(T.CrsCode AS "CCode"))
	FROM Transcripts T 
	WHERE S.Id = T.StudID)
FROM 	Student S

---- This query will not work though 

SQL> descr XMLTEST;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 DOC                                                XMLTYPE

SQL> select * from XMLTEST;


insert into XMLTEST values (2, XMLELEMENT(Name "Student", abc));

SQL> insert into XMLTEST values (2, XMLELEMENT(Name "Student", "abc"));
insert into XMLTEST values (2, XMLELEMENT(Name "Student", "abc"))
                                                          *
ERROR at line 1:
ORA-00984: column not allowed here


SQL> insert into XMLTEST values (2, XMLELEMENT(Name "Student", abc));
insert into XMLTEST values (2, XMLELEMENT(Name "Student", abc))
                                                          *
ERROR at line 1:
ORA-00984: column not allowed here


insert into XMLTEST(Id, Doc) values (2, xmlparse('<Student>abc</Student>'));

SQL> insert into XMLTEST(Id, Doc) values (2, xmlparse('<Student>abc</Student>'));
insert into XMLTEST(Id, Doc) values (2, xmlparse('<Student>abc</Student>'))
                                        *
ERROR at line 1:
ORA-00904: "XMLPARSE": invalid identifier
