Forum Stats

  • 3,853,593 Users
  • 2,264,243 Discussions
  • 7,905,404 Comments

Discussions

Generate XML from tables

muttleychess
muttleychess Member Posts: 1,502 Bronze Badge
edited Nov 8, 2018 10:16AM in SQL & PL/SQL

Hi

   I have tables like schem  HR (clone)

 

I used only 3 in example  the names table are changed

TBL_LOCATION,

TBL_DEPT

TBL_EMPLOYEES

CREATE TABLE tbl_location    ( location_id    NUMBER(4)    , street_address VARCHAR2(40)    , postal_code    VARCHAR2(12)    ) ; /   CREATE UNIQUE INDEX tbl_loc_id_pkON tbl_location (location_id) ;/ALTER TABLE tbl_locationADD ( CONSTRAINT tbl_loc_id_pk            PRIMARY KEY (location_id)  ) ;/CREATE TABLE tbl_dept    ( department_id    NUMBER(4)    , department_name  VARCHAR2(30),     location_id      NUMBER(4)    ) ;/    CREATE UNIQUE INDEX tbl_dept_id_pkON tbl_dept (department_id) ;    /ALTER TABLE tbl_deptADD ( CONSTRAINT tbl_dept_id_pk                PRIMARY KEY (department_id)    , CONSTRAINT tbl_dept_loc_fk                FOREIGN KEY (location_id)              REFERENCES tbl_location (location_id)  ) ; /   CREATE TABLE tbl_employees    ( employee_id    NUMBER(6)    , first_name     VARCHAR2(20)    , last_name      VARCHAR2(25)    , manager_id     NUMBER(6)    , department_id  NUMBER(4)   ) ;/  CREATE UNIQUE INDEX tbl_emp_emp_id_pkON tbl_employees (employee_id) ;/ALTER TABLE tbl_employeesADD ( CONSTRAINT     tbl_emp_emp_id_pk                     PRIMARY KEY (employee_id)    , CONSTRAINT     tbl_emp_dept_fk                     FOREIGN KEY (department_id)                      REFERENCES tbl_dept    , CONSTRAINT     tbl_emp_manager_fk                     FOREIGN KEY (manager_id)                      REFERENCES tbl_employees                      DEFERRABLE INITIALLY DEFERRED    ) ;/    

Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1000, '1297 Via Cola di Rie', '00989');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1100, '93091 Calle della Testa', '10934');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1200, '2017 Shinjuku-ku', '1689');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1300, '9450 Kamiya-cho', '6823');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1400, '2014 Jabberwocky Rd', '26192');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1500, '2011 Interiors Blvd', '99236');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1600, '2007 Zagora St', '50090');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1700, '2004 Charade Rd', '98199');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1800, '147 Spadina Ave', 'M5V 2L7');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1900, '6092 Boxwood St', 'YSW 9T2');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2000, '40-5-12 Laogianggen', '190518');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2100, '1298 Vileparle (E)', '490231');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2200, '12-98 Victoria Street', '2901');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2300, '198 Clementi North', '540198');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS) Values  (2400, '8204 Arthur St');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2600, '9702 Chester Road', '09629850293');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2700, 'Schwanthalerstr. 7031', '80925');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2800, 'Rua Frei Caneca 1360 ', '01307-002');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2900, '20 Rue des Corps-Saints', '1730');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (3000, 'Murtenstrasse 921', '3095');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (3100, 'Pieter Breughelstraat 837', '3029SK');Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (3200, 'Mariano Escobedo 9991', '11932');COMMIT;Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)  Values (20, 'Marketing', 1800);Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (30, 'Purchasing', 1700);Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (60, 'IT', 1400);Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (90, 'Executive', 1700);COMMIT;Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID) Values  (100, 'Steven', 'King', 90);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (201, 'Michael', 'Hartstein', 100, 20);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (114, 'Den', 'Raphaely', 100, 30);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (101, 'Neena', 'Kochhar', 100, 90);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (102, 'Lex', 'De Haan', 100, 90);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (103, 'Alexander', 'Hunold', 102, 60);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (105, 'David', 'Austin', 103, 60);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (104, 'Bruce', 'Ernst', 103, 60);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (106, 'Valli', 'Pataballa', 103, 60);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (107, 'Diana', 'Lorentz', 103, 60);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (119, 'Karen', 'Colmenares', 114, 30);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (117, 'Sigal', 'Tobias', 114, 30);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (115, 'Alexander', 'Khoo', 114, 30);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (116, 'Shelli', 'Baida', 114, 30);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (118, 'Guy', 'Himuro', 114, 30);Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (202, 'Pat', 'Fay', 201, 20);COMMIT;

I would like to create a XML like below

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>  <EMPLOY CAPTA="TRUE">      <FUNC CMD="VOID">       <LOCAL>       <location_id>1700</<location_id>          <DEPTO>           <department_id>30</department_id>              <EMPLOY>               <EMPLOYEE_ID>114 </EMPLOYEE_ID>               <FIRST_NAME>Den</FIRST_NAME>               <LAST_NAME>Raphaely</LAST_NAME>             </EMPLOY>                <EMPLOY>               <EMPLOYEE_ID>115</EMPLOYEE_ID>               <FIRST_NAME>Alexander</FIRST_NAME>               <LAST_NAME>Khoo</LAST_NAME>             </EMPLOY>                <EMPLOY>              <EMPLOYEE_ID>116 </EMPLOYEE_ID>              <FIRST_NAME>Shelli</FIRST_NAME>              <LAST_NAME>Baida</LAST_NAME>             </EMPLOY>                <EMPLOY>             <EMPLOYEE_ID>117 </EMPLOYEE_ID>             <FIRST_NAME>Sigal</FIRST_NAME>             <LAST_NAME>Tobias</LAST_NAME>             </EMPLOY>                <EMPLOY>             <EMPLOYEE_ID>118</EMPLOYEE_ID>             <FIRST_NAME>Guy</FIRST_NAME>             <LAST_NAME>Himuro</LAST_NAME>             </EMPLOY>                <EMPLOY>             <EMPLOYEE_ID>119</EMPLOYEE_ID>             <FIRST_NAME>Karen</FIRST_NAME>             <LAST_NAME>Colmenares</LAST_NAME>             </EMPLOY>            </DEPTO>            <DEPTO>       <department_id>90</department_id>         <EMPLOY>              <EMPLOYEE_ID>100</EMPLOYEE_ID>              <FIRST_NAME>Steven</FIRST_NAME>              <LAST_NAME>King</LAST_NAME>         </EMPLOY>           <EMPLOY>               <EMPLOYEE_ID>101</EMPLOYEE_ID>               <FIRST_NAME>Neena</FIRST_NAME>               <LAST_NAME>Kochhar</LAST_NAME>         </EMPLOY>           <EMPLOY>              <EMPLOYEE_ID>102 </EMPLOYEE_ID>              <FIRST_NAME>Lex</FIRST_NAME>          <LAST_NAME>De Haan</LAST_NAME>            </EMPLOY>           </DEPTO>           </LOCAL>       </FUNC>   </EMPLOY>  

I tried the query below, but I do know how can to do It

SELECT E.EMPLOYEE_ID,        E.FIRST_NAME ,        E.LAST_NAME ,        J.DEPARTMENT_IDFROM  TBL_EMPLOYEES EINNER JOIN TBL_DEPT JON E.DEPARTMENT_ID = J.DEPARTMENT_IDINNER JOIN TBL_LOCATION LON J.LOCATION_ID = L.LOCATION_IDWHERE L.LOCATION_ID = 1700START WITH E.MANAGER_ID IS NULL CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID 

Using

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0    Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Thank you in advance

Tagged:
muttleychessricard888

Best Answer

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Nov 7, 2018 10:00AM
  • Paulzip
    Paulzip Member Posts: 8,753 Blue Diamond
    edited Nov 7, 2018 10:21AM Answer ✓

    I could easily do this for you, but you won't learn anything.  However, here's a good article on how to go about it.

    ricard888
  • muttleychess
    muttleychess Member Posts: 1,502 Bronze Badge
    edited Nov 7, 2018 11:38AM

    wonderful !!

    It seems the same example

    Only I would like to know how to get the version of XML

    xml version="1.0" encoding="UTF-8" standalone="yes

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Nov 7, 2018 1:53PM

    something like ...

    select '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' ||xmlserialize(document ... as clob indent) as xml_output....
  • Paulzip
    Paulzip Member Posts: 8,753 Blue Diamond
    edited Nov 7, 2018 2:11PM
    muttleychess wrote:wonderful !!It seems the same example Only I would like to know how to get the version of XMLxml version="1.0" encoding="UTF-8" standalone="yes

    The prolog is added (and only needed) if you serialise the data to a BLOB.  It's simply for the consumer's sake so they know how to decode the BLOB into character data.  If you are serialising into a CLOB, you don't need it, however if you really want to, use mNem's concat approach.

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Nov 7, 2018 2:34PM

    Another way ...

    select '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' ||xmlserialize(document xmlquery (    '    <EMPLOY CAPTA="TRUE">      <FUNC CMD="VOID">    <LOCAL>    {        for $location_id in distinct-values($coll/ROWSET/ROW/LOCATION_ID/text())        return         (            <location_id>{$location_id}</location_id>            ,            for $dept in distinct-values($coll/ROWSET/ROW[LOCATION_ID = $location_id]/DEPARTMENT_ID/text())            return                <DEPTO>                <department_id>{$dept}</department_id>                {                    for $emp in $coll/ROWSET/ROW[LOCATION_ID = $location_id and DEPARTMENT_ID = $dept]                    return                         <EMPLOY>                        {                            $emp/EMPLOYEE_ID,                            $emp/FIRST_NAME,                            $emp/LAST_NAME                        }                        </EMPLOY>                }                </DEPTO>        )    }    </LOCAL>    </FUNC>    </EMPLOY>    '    passing xmltype(                 cursor(                    select location_id, department_id, employee_id, first_name, last_name                     from tbl_employees                     join tbl_dept     using (department_id)                     join tbl_location using (location_id)                     order by department_id, location_id, employee_id               )             )             as "coll"                returning content  )as clob indent) as xml_outputfrom dual;

    output:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

    <EMPLOY CAPTA="TRUE">

      <FUNC CMD="VOID">

       <LOCAL>

       <location_id>1800</location_id>

       <DEPTO>

       <department_id>20</department_id>

       <EMPLOY>

       <EMPLOYEE_ID>201</EMPLOYEE_ID>

       <FIRST_NAME>Michael</FIRST_NAME>

       <LAST_NAME>Hartstein</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>202</EMPLOYEE_ID>

       <FIRST_NAME>Pat</FIRST_NAME>

       <LAST_NAME>Fay</LAST_NAME>

       </EMPLOY>

       </DEPTO>

       <location_id>1700</location_id>

       <DEPTO>

       <department_id>30</department_id>

       <EMPLOY>

       <EMPLOYEE_ID>114</EMPLOYEE_ID>

       <FIRST_NAME>Den</FIRST_NAME>

       <LAST_NAME>Raphaely</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>115</EMPLOYEE_ID>

       <FIRST_NAME>Alexander</FIRST_NAME>

       <LAST_NAME>Khoo</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>116</EMPLOYEE_ID>

       <FIRST_NAME>Shelli</FIRST_NAME>

       <LAST_NAME>Baida</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>117</EMPLOYEE_ID>

       <FIRST_NAME>Sigal</FIRST_NAME>

       <LAST_NAME>Tobias</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>118</EMPLOYEE_ID>

       <FIRST_NAME>Guy</FIRST_NAME>

       <LAST_NAME>Himuro</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>119</EMPLOYEE_ID>

       <FIRST_NAME>Karen</FIRST_NAME>

       <LAST_NAME>Colmenares</LAST_NAME>

       </EMPLOY>

       </DEPTO>

       <DEPTO>

       <department_id>90</department_id>

       <EMPLOY>

       <EMPLOYEE_ID>100</EMPLOYEE_ID>

       <FIRST_NAME>Steven</FIRST_NAME>

       <LAST_NAME>King</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>101</EMPLOYEE_ID>

       <FIRST_NAME>Neena</FIRST_NAME>

       <LAST_NAME>Kochhar</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>102</EMPLOYEE_ID>

       <FIRST_NAME>Lex</FIRST_NAME>

       <LAST_NAME>De Haan</LAST_NAME>

       </EMPLOY>

       </DEPTO>

       <location_id>1400</location_id>

       <DEPTO>

       <department_id>60</department_id>

       <EMPLOY>

       <EMPLOYEE_ID>103</EMPLOYEE_ID>

       <FIRST_NAME>Alexander</FIRST_NAME>

       <LAST_NAME>Hunold</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>104</EMPLOYEE_ID>

       <FIRST_NAME>Bruce</FIRST_NAME>

       <LAST_NAME>Ernst</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>105</EMPLOYEE_ID>

       <FIRST_NAME>David</FIRST_NAME>

       <LAST_NAME>Austin</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>106</EMPLOYEE_ID>

       <FIRST_NAME>Valli</FIRST_NAME>

       <LAST_NAME>Pataballa</LAST_NAME>

       </EMPLOY>

       <EMPLOY>

       <EMPLOYEE_ID>107</EMPLOYEE_ID>

       <FIRST_NAME>Diana</FIRST_NAME>

       <LAST_NAME>Lorentz</LAST_NAME>

       </EMPLOY>

       </DEPTO>

       </LOCAL>

      </FUNC>

    </EMPLOY>

    muttleychessmuttleychess
  • muttleychess
    muttleychess Member Posts: 1,502 Bronze Badge
    edited Nov 8, 2018 7:08AM

    Thank you

       I need to understand about FLWR   :-(

    I tried to remove tag <DEPTO>    and  <department_id>{$dept}</department_id>  , but I couldn't

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Nov 8, 2018 10:16AM
    select     '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'   ||  xmlserialize(document     xmlquery (      '      <EMPLOY CAPTA="TRUE">        <FUNC CMD="VOID">      <LOCAL>      {          for $location_id in distinct-values($coll/ROWSET/ROW/LOCATION_ID/text())          return           (              <location_id>{$location_id}</location_id>              ,              for $dept in distinct-values($coll/ROWSET/ROW[LOCATION_ID = $location_id]/DEPARTMENT_ID/text())              return                  (: <DEPTO>  :)                (: <department_id>{$dept}</department_id>  :)                (: {  :)                    for $emp in $coll/ROWSET/ROW[LOCATION_ID = $location_id and DEPARTMENT_ID = $dept]                      return                           <EMPLOY deptno="{$dept}">                          {                              $emp/EMPLOYEE_ID,                              $emp/FIRST_NAME,                              $emp/LAST_NAME                          }                          </EMPLOY>                  (: }  :)                (: </DEPTO>  :)        )      }      </LOCAL>      </FUNC>      </EMPLOY>      '      passing xmltype(                   cursor(                      select location_id, department_id, employee_id, first_name, last_name                       from tbl_employees                       join tbl_dept     using (department_id)                       join tbl_location using (location_id)                       order by department_id, location_id, employee_id                 )               )               as "coll"                    returning content    )    as clob indent)     as xml_output    from dual  ; 
    XML_OUTPUT                                                                     
    --------------------------------------------------------------------------------
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?><EMPLOY CAPTA="TRUE">
      <FUNC CMD="VOID">
        <LOCAL>
          <location_id>1800</location_id>
          <EMPLOY deptno="20">
            <EMPLOYEE_ID>201</EMPLOYEE_ID>
            <FIRST_NAME>Michael</FIRST_NAME>
            <LAST_NAME>Hartstein</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="20">
            <EMPLOYEE_ID>202</EMPLOYEE_ID>        <FIRST_NAME>Pat</FIRST_NAME>
            <LAST_NAME>Fay</LAST_NAME>
          </EMPLOY>
          <location_id>1700</location_id>
          <EMPLOY deptno="30">
            <EMPLOYEE_ID>114</EMPLOYEE_ID>
            <FIRST_NAME>Den</FIRST_NAME>
            <LAST_NAME>Raphaely</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="30">
            <EMPLOYEE_ID>115</EMPLOYEE_ID>        <FIRST_NAME>Alexander</FIRST_NAME>
            <LAST_NAME>Khoo</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="30">
            <EMPLOYEE_ID>116</EMPLOYEE_ID>
            <FIRST_NAME>Shelli</FIRST_NAME>
            <LAST_NAME>Baida</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="30">
            <EMPLOYEE_ID>117</EMPLOYEE_ID>
            <FIRST_NAME>Sigal</FIRST_NAME>        <LAST_NAME>Tobias</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="30">
            <EMPLOYEE_ID>118</EMPLOYEE_ID>
            <FIRST_NAME>Guy</FIRST_NAME>
            <LAST_NAME>Himuro</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="30">
            <EMPLOYEE_ID>119</EMPLOYEE_ID>
            <FIRST_NAME>Karen</FIRST_NAME>
            <LAST_NAME>Colmenares</LAST_NAME>      </EMPLOY>
          <EMPLOY deptno="90">
            <EMPLOYEE_ID>100</EMPLOYEE_ID>
            <FIRST_NAME>Steven</FIRST_NAME>
            <LAST_NAME>King</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="90">
            <EMPLOYEE_ID>101</EMPLOYEE_ID>
            <FIRST_NAME>Neena</FIRST_NAME>
            <LAST_NAME>Kochhar</LAST_NAME>
          </EMPLOY>      <EMPLOY deptno="90">
            <EMPLOYEE_ID>102</EMPLOYEE_ID>
            <FIRST_NAME>Lex</FIRST_NAME>
            <LAST_NAME>De Haan</LAST_NAME>
          </EMPLOY>
          <location_id>1400</location_id>
          <EMPLOY deptno="60">
            <EMPLOYEE_ID>103</EMPLOYEE_ID>
            <FIRST_NAME>Alexander</FIRST_NAME>
            <LAST_NAME>Hunold</LAST_NAME>
          </EMPLOY>      <EMPLOY deptno="60">
            <EMPLOYEE_ID>104</EMPLOYEE_ID>
            <FIRST_NAME>Bruce</FIRST_NAME>
            <LAST_NAME>Ernst</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="60">
            <EMPLOYEE_ID>105</EMPLOYEE_ID>
            <FIRST_NAME>David</FIRST_NAME>
            <LAST_NAME>Austin</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="60">        <EMPLOYEE_ID>106</EMPLOYEE_ID>
            <FIRST_NAME>Valli</FIRST_NAME>
            <LAST_NAME>Pataballa</LAST_NAME>
          </EMPLOY>
          <EMPLOY deptno="60">
            <EMPLOYEE_ID>107</EMPLOYEE_ID>
            <FIRST_NAME>Diana</FIRST_NAME>
            <LAST_NAME>Lorentz</LAST_NAME>
          </EMPLOY>
        </LOCAL>
      </FUNC></EMPLOY>