/********************************/
/* XML fa rima con SQL - esempi */
/********************************/

    -- esempio XML employee
set current schema = 'DB2SAMPLE';

    -- creazione di un file XML semplice: 1 colonna 1 record (esempio 1)
select xmlserialize(
        xmldocument(
         xmlelement(name "lastname", LASTNAME)
        ) as clob(500) including xmldeclaration) as "XML output"
  from EMPLOYEE
  where EMPNO = '000010';

select LASTNAME
  from EMPLOYEE
  where EMPNO = '000010';

    -- creazione di un file XML di un impiegato (esempio 2)
select xmlserialize(
        xmldocument(
         xmlelement(name "employee", xmlattributes(EMPNO as "id", trim(JOB) as "job"),
          xmlrow(FIRSTNME as "firstname",
                 LASTNAME as "lastname"
                 option row "name"
          ),
          xmlelement(name "department", WORKDEPT),
          xmlrow(SALARY as "salary",
                 BONUS as "bonus",
                 HIREDATE as "hiredate"
                 option row "contract"
          )
         )
        ) as clob(1000) including xmldeclaration) as "XML output"
  from EMPLOYEE
  where EMPNO = '000010';

select EMPNO, JOB, FIRSTNME, LASTNAME, WORKDEPT, SALARY, BONUS, HIREDATE
  from EMPLOYEE
  where EMPNO = '000010';

    -- creazione di un file XML con elenco di impiegati (esempio 3)
select
   xmlserialize(
    xmldocument(
     xmlelement(name "employees", 
      xmlagg(
       xmlelement(name "employee", xmlattributes(EMPNO as "id", trim(JOB) as "job"),
        xmlrow(FIRSTNME as "firstname",
               LASTNAME as "lastname"
               option row "name"
        ),
        xmlelement(name "department", WORKDEPT),
        xmlrow(SALARY as "salary",
               BONUS as "bonus",
               HIREDATE as "hiredate"
               option row "contract"
        )
       )
      )
     )
    ) as clob(1000) including xmldeclaration) as "XML output"
  from EMPLOYEE
  where EMPNO in('000010', '000200');

select EMPNO, JOB, FIRSTNME, LASTNAME, WORKDEPT, SALARY, BONUS, HIREDATE
  from EMPLOYEE
  where EMPNO in('000010', '000200');

    -- sintassi alternativa usando XMLCONCAT invece di XMLROW (esempio 3b)
select
   xmlserialize(
    xmldocument(
     xmlelement(name "employees", 
      xmlagg(
       xmlelement(name "employee", xmlattributes(EMPNO as "id", trim(JOB) as "job"),
        xmlelement(name "name", xmlconcat(xmlelement(name "firstname", FIRSTNME),
                                          xmlelement(name "lastname", LASTNAME))
        ),
        xmlelement(name "department", WORKDEPT),
        xmlelement(name "contract", xmlconcat(xmlelement(name "salary", SALARY), 
                                              xmlelement(name "bonus", BONUS), 
                                              xmlelement(name "hiredate", HIREDATE))
        )
       )
      )
     )
    )
   ) as clob(1000) including xmldeclaration) as "XML output"
  from EMPLOYEE
  where EMPNO in('000010', '000200');

    -- creazione di un file XML con elenco di impiegati e i progetti a loro assegnati (esempio 4)
select
   xmlserialize(
    xmldocument(
     xmlelement(name "employees", 
      xmlagg(
       xmlelement(name "employee", xmlattributes(E.EMPNO as "id", trim(E.JOB) as "job"),
        xmlelement(name "name", xmlconcat(xmlelement(name "firstname", E.FIRSTNME),
                                          xmlelement(name "lastname", E.LASTNAME))
        ),
        xmlelement(name "department", E.WORKDEPT),
        xmlelement(name "contract", xmlconcat(xmlelement(name "salary", E.SALARY), 
                                              xmlelement(name "bonus", E.BONUS), 
                                              xmlelement(name "hiredate", E.HIREDATE))
        ),
        xmlelement(name "projects", (select 
                                        xmlagg(
                                         xmlelement(name "project", xmlconcat(xmlelement(name "ProjectID", P.PROJNO),
                                                                              xmlelement(name "ActivityID", P.ACTNO),
                                                                              xmlelement(name "hours", P.EMPTIME))
                                         )                                      
                                        )
                                      from EMPPROJACT as P
                                      where P.EMPNO = E.EMPNO)
        )
       )
      )
     )
   ) as clob(5000) including xmldeclaration) as "XML output"
  from EMPLOYEE as E
  where E.EMPNO in('000010', '000020');
  
select E.EMPNO, E.JOB, E.FIRSTNME, E.LASTNAME, E.WORKDEPT, E.SALARY, E.BONUS, E.HIREDATE, P.PROJNO, P.ACTNO, P.EMPTIME
  from EMPLOYEE as E
    left join EMPPROJACT as P on P.EMPNO = E.EMPNO
  where E.EMPNO in('000010', '000200');
