Solved

XML function in Oracle

Posted on 2013-11-25
18
595 Views
Last Modified: 2013-11-30
Hello Experts,

NOTE: reference taken from URL:
http://allthingsoracle.com/generating-xml-from-sql-and-plsql-part-2/

I have a function which gives me a XML output something like below :

Where I am getting all the audit company data under every visit whereas I want to display only those audit company which are under that visit.


SQL> select get_xml_data() from dual ;

OUTPUT :
-----------------------------------------------------------------
<OPEN_SITE_VISIT>
<VISIT_ID VISIT_ID="V11-5300-2013-11">
	<REPORT_TYPE>TODAY</REPORT_TYPE>
	<VISIT_STATUS>Audit In Progress</VISIT_STATUS>
	<AUDIT_COMPANY>
		<AUDIT_COMPANY AUDIT_COMPANY="Verite">
			<AUDITOR>Charles SGM</AUDITOR>
			<IS_LEAD>1</IS_LEAD>
		</AUDIT_COMPANY>
		<AUDIT_COMPANY AUDIT_COMPANY="Verite">
			<AUDITOR>David SGM</AUDITOR>
			<IS_LEAD>0</IS_LEAD>
		</AUDIT_COMPANY>
		<AUDIT_COMPANY AUDIT_COMPANY="Golder">
			<AUDITOR>Jake SGM</AUDITOR>
			<IS_LEAD>1</IS_LEAD>
		</AUDIT_COMPANY>
		<AUDIT_COMPANY AUDIT_COMPANY="Golder">
			<AUDITOR>Jasmin SGM</AUDITOR>
			<IS_LEAD>0</IS_LEAD>
		</AUDIT_COMPANY>
	</AUDIT_COMPANY>
</VISIT_ID>

Open in new window


Here is my function code:

CREATE OR REPLACE FUNCTION SGM.
    get_xml_data RETURN CLOB
is 
  l_result clob;
   l_domdoc dbms_xmldom.DOMDocument;
   l_xmltype XMLTYPE;

   l_root_node dbms_xmldom.DOMNode;

   l_departments_node dbms_xmldom.DOMNode;

   l_dept_element dbms_xmldom.DOMElement;
   l_dept_node dbms_xmldom.DOMNode;

   l_name_node dbms_xmldom.DOMNode;
   l_name_textnode dbms_xmldom.DOMNode;

   l_location_node dbms_xmldom.DOMNode;
   l_location_textnode dbms_xmldom.DOMNode;

   l_employees_node dbms_xmldom.DOMNode;

   l_emp_element dbms_xmldom.DOMElement;
   l_emp_node dbms_xmldom.DOMNode;

   l_emp_first_name_node dbms_xmldom.DOMNode;
   l_emp_first_name_textnode dbms_xmldom.DOMNode;

   l_emp_last_name_node dbms_xmldom.DOMNode;
   l_emp_last_name_textnode dbms_xmldom.DOMNode;
BEGIN
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new node Departments and add it to the root node
   l_departments_node := dbms_xmldom.appendChild( l_root_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'OPEN_SITE_VISIT' ))
                                                );

   FOR r_dept IN (SELECT
       DECODE(vsetup.visit_status, '4', 'TODAY', '1','UPCOMING', '2', 'UPCOMING','3' ,'UPCOMING', '5','FINAL', '6','FINAL') report_type
      ,(ms_apps_utilities.get_display_value(100000  ,'MS SRA VISIT STATUS',vsetup.visit_status)) AS VISIT_STATUS
      ,vsetup.visit_status as visit_status_id
      ,lp.visit_id
      ,LP.USER_ID
      ,lp.APP_ASSIGNMENT_ID  
      ,lp.metric_id
      ,lp.pid
      ,lp.instance_id
      ,LP.IS_OFFLINE
      ,fac.facility_id
      ,fac.facility_name
      ,fac.city
      ,fac.state_province
      ,fac.country
      ,fac.facility_address
      ,FAC.FACILITY_LOCAL_NAME
      ,fac.facility_local_address
      ,FAC.LONGITUDE
      ,FAC.LATITUDE
      ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                        ,'MS_SRA_VISIT' ,lp.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                             as VISIT_EDIT_FLAG
    ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                        ,'MS_SRA_VISIT_SETUP' ,vsetup.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                             as VS_EDIT_FLAG
        ,vsetup.DD_PROCESS_INSTANCE_ID as vs_pid
        , MS_APPS_UTILITIES.GET_INFOLET_ID('MS_SRA_VISIT_SETUP')  as VS_METRIC_ID
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.ONSITE_LEAD) as ONSITE_LEAD
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_LEAD) as APPLE_LEAD
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_VERIFICATION_LEAD) as APPLE_VERIFICATION_LEAD
        ,ms_apps_utilities.get_user_full_name(VSETUP.SR_PROGRAM_MANAGER) as SR_PROGRAM_MANAGER
 ,vsetup.start_date
      ,vsetup.protocol_name
      ,to_char(to_date(vsetup.tpa_submission_deadline
                      ,'dd-mon-yy')
              ,'MON dd,yyyy') as TPA_DATE
      ,TO_CHAR(TO_DATE(VSETUP.FINAL_REPORT_DEADLINE
                      ,'dd-mon-yy')
              ,'MON dd,yyyy') AS FINAL_REPORT_DEADLINE        
     ,(to_char(to_date(vsetup.start_date
                       ,'dd-mon-yy')
               ,'MON dd') || to_char(to_date(vsetup.end_date
                                             ,'dd-mon-yy')
                                     ,' - MON dd, yyyy')) as SHOW_DATE
 from MS_SRA_VISIT_LP_RPT LP,
  MS_SRA_VISIT_SETUP VSETUP,
( SELECT *
from   (select
      VISIT_ID
      ,FACILITY_ID
      ,DD_OBJECT_TYPE 
      ,facility_name
      ,city
      ,state_province
      ,country
      ,facility_address
      ,FACILITY_LOCAL_NAME
      ,facility_local_address
      ,LONGITUDE
        ,LATITUDE
               ,Row_number() 
                 over ( 
                   PARTITION BY visit_id 
                   ORDER BY CASE WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1 WHEN 
                 DD_OBJECT_TYPE = 
                 'MS_SRA_VISIT_SETUP' then 2 end) RN 
        FROM   ms_sra_facility_info
        where  DD_OBJECT_TYPE in ( 'MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP' )) 
where  RN = 1) fac
where VSETUP.VISIT_ID =LP.VISIT_ID
  AND FAC.VISIT_ID =VSETUP.VISIT_ID 
   AND VSETUP.VISIT_STATUS IN ('1','2','3','4','5','6')
   AND LP.USER_ID = MS_APPS_UTILITIES.GET_USER_ID('2109760810')
   order by DECODE(VSETUP.VISIT_STATUS, '4', 1, '5', 2, '6', 3, '1',4,'2',5,'3',6) 
                 )
   LOOP
      -- For each record, create a new Dept element with the Department ID as attribute.
      -- and add this new Dept element to the Departments node
      l_dept_element := dbms_xmldom.createElement(l_domdoc, 'VISIT_ID' );
      dbms_xmldom.setAttribute(l_dept_element, 'VISIT_ID', r_dept.VISIT_ID );
      l_dept_node := dbms_xmldom.appendChild( l_departments_node
                                            , dbms_xmldom.makeNode(l_dept_element)
                                            );

      -- Each Dept node will get a Name node which contains the department name as text
      l_name_node := dbms_xmldom.appendChild( l_dept_node
                                            , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'REPORT_TYPE' ))
                                            );
      l_name_textnode := dbms_xmldom.appendChild( l_name_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.REPORT_TYPE ))
                                                );

      -- Each Dept node will aslo get a Location node which contains the location(city) as text
      l_location_node := dbms_xmldom.appendChild( l_dept_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'VISIT_STATUS' ))
                                                );
      l_location_textnode := dbms_xmldom.appendChild( l_location_node
                                                    , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.VISIT_STATUS ))
                                                    );

      -- For each department, add an Employees node
      l_employees_node := dbms_xmldom.appendChild( l_dept_node
                                                 , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'AUDIT_COMPANY' ))
                                                 );

      FOR r_emp IN (select (SELECT org_entity_name
          FROM SI_ORG_ENTITIES_T
         where ORG_ENTITY_ID = aud.AUD_TPA_ID) as AUDIT_COMPANY,
  MS_APPS_UTILITIES.GET_USER_FULL_NAME(AUD.AUDITOR) AS TPA_AUDITOR ,
  aud.IS_LEAD
FROM MS_SRA_VISIT_SETUP_AUD aud 
WHERE AUD.VISIT_ID = r_dept.VISIT_ID
                   )
      LOOP
         -- For each record, create a new Emp element with the Employee ID as attribute.
         -- and add this new Emp element to the Employees node
         l_emp_element := dbms_xmldom.createElement(l_domdoc, 'AUDIT_COMPANY' );
         dbms_xmldom.setAttribute(l_emp_element, 'AUDIT_COMPANY', r_emp.AUDIT_COMPANY );
         l_emp_node := dbms_xmldom.appendChild( l_employees_node
                                              , dbms_xmldom.makeNode(l_emp_element)
                                              );

         -- Each emp node will get a First name and Last name node which contains the first name and last name as text
         l_emp_first_name_node := dbms_xmldom.appendChild( l_emp_node
                                                         , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'AUDITOR' ))
                                                         );
         l_emp_first_name_textnode := dbms_xmldom.appendChild( l_emp_first_name_node
                                                             , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.TPA_AUDITOR ))
                                                             );

         l_emp_last_name_node := dbms_xmldom.appendChild( l_emp_node
                                                        , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'IS_LEAD' ))
                                                        );
         l_emp_last_name_textnode := dbms_xmldom.appendChild( l_emp_last_name_node
                                                            , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.IS_LEAD ))
                                                            );
      END LOOP;
   END LOOP;

   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
   dbms_xmldom.freeDocument(l_domdoc);

   l_result:=l_xmltype.getClobVal();
   
   return l_result;
   
END;

Open in new window



Can you please suggest where I am doing wrong ?

Attached here with the screen shot for the issue ...

XML Issue
I should only get the two audit companies and the auditors details under that ...
As I am having two different company and two auditors associated to it.
0
Comment
Question by:Swadhin Ray
  • 10
  • 6
  • 2
18 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 39675518
I attached code I think will work but I can't test it lacking the original datasources.

Beware : if your first loop contains more then 1 record you can't see the parent in your output.
XML--function--in-Oracle.txt
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39675593
@flow01 :

In my first query : I get multiple visit details with facility details...
In my second query I should get multiple audit companies (So one visit can have multiple audit companies )
Similarly one audit company can have multiple auditors.

Finally I have another sql where every auditor have multiple assignments.

This is how I need the data... I will post the all three query and result what I am getting.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39675643
Here is my function :

CREATE OR REPLACE FUNCTION SGM.
            get_xml_data_dl RETURN CLOB
is 
  l_result clob;
   l_domdoc dbms_xmldom.DOMDocument;
   l_xmltype XMLTYPE;

   l_root_node dbms_xmldom.DOMNode;

   l_open_site_visit dbms_xmldom.DOMNode;

   l_visit_element dbms_xmldom.DOMElement;
   l_visit_node dbms_xmldom.DOMNode;

   l_report_type_node dbms_xmldom.DOMNode;
   l_report_type_textnode dbms_xmldom.DOMNode;

   l_status_node dbms_xmldom.DOMNode;
   l_status_textnode dbms_xmldom.DOMNode;

   l_audcompany_node dbms_xmldom.DOMNode;

   l_auditcompany_element dbms_xmldom.DOMElement;
   l_auditcompany_node dbms_xmldom.DOMNode;

   l_auditors_name_node dbms_xmldom.DOMNode;
   l_auditors_name_textnode dbms_xmldom.DOMNode;

   l_islead_name_node dbms_xmldom.DOMNode;
   l_islead_name_textnode dbms_xmldom.DOMNode;
   
  --l_auditors_name_node dbms_xmldom.DOMNode;
 
   l_asn_node dbms_xmldom.DOMNode;
   l_asn_node_textnode dbms_xmldom.DOMNode;
   
BEGIN
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new node Departments and add it to the root node
   l_open_site_visit := dbms_xmldom.appendChild( l_root_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'OPEN_SITE_VISIT' ))
                                                );

   FOR r_visit IN (SELECT
       DECODE(vsetup.visit_status, '4', 'TODAY', '1','UPCOMING', '2', 'UPCOMING','3' ,'UPCOMING', '5','FINAL', '6','FINAL') report_type
      ,(ms_apps_utilities.get_display_value(100000  ,'MS SRA VISIT STATUS',vsetup.visit_status)) AS VISIT_STATUS
      ,vsetup.visit_status as visit_status_id
      ,lp.visit_id
      ,LP.USER_ID
      ,lp.APP_ASSIGNMENT_ID  
      ,lp.metric_id
      ,lp.pid
      ,lp.instance_id
      ,LP.IS_OFFLINE
      ,fac.facility_id
      ,fac.facility_name
      ,fac.city
      ,fac.state_province
      ,fac.country
      ,fac.facility_address
      ,FAC.FACILITY_LOCAL_NAME
      ,fac.facility_local_address
      ,FAC.LONGITUDE
      ,FAC.LATITUDE
      ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                        ,'MS_SRA_VISIT' ,lp.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                             as VISIT_EDIT_FLAG
    ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                        ,'MS_SRA_VISIT_SETUP' ,vsetup.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                             as VS_EDIT_FLAG
        ,vsetup.DD_PROCESS_INSTANCE_ID as vs_pid
        , MS_APPS_UTILITIES.GET_INFOLET_ID('MS_SRA_VISIT_SETUP')  as VS_METRIC_ID
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.ONSITE_LEAD) as ONSITE_LEAD
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_LEAD) as APPLE_LEAD
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_VERIFICATION_LEAD) as APPLE_VERIFICATION_LEAD
        ,ms_apps_utilities.get_user_full_name(VSETUP.SR_PROGRAM_MANAGER) as SR_PROGRAM_MANAGER
 ,vsetup.start_date
      ,vsetup.protocol_name
      ,to_char(to_date(vsetup.tpa_submission_deadline
                      ,'dd-mon-yy')
              ,'MON dd,yyyy') as TPA_DATE
      ,TO_CHAR(TO_DATE(VSETUP.FINAL_REPORT_DEADLINE
                      ,'dd-mon-yy')
              ,'MON dd,yyyy') AS FINAL_REPORT_DEADLINE        
     ,(to_char(to_date(vsetup.start_date
                       ,'dd-mon-yy')
               ,'MON dd') || to_char(to_date(vsetup.end_date
                                             ,'dd-mon-yy')
                                     ,' - MON dd, yyyy')) as SHOW_DATE
 from MS_SRA_VISIT_LP_RPT LP,
  MS_SRA_VISIT_SETUP VSETUP,
( SELECT *
from   (select
      VISIT_ID
      ,FACILITY_ID
      ,DD_OBJECT_TYPE 
      ,facility_name
      ,city
      ,state_province
      ,country
      ,facility_address
      ,FACILITY_LOCAL_NAME
      ,facility_local_address
      ,LONGITUDE
        ,LATITUDE
               ,Row_number() 
                 over ( 
                   PARTITION BY visit_id 
                   ORDER BY CASE WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1 WHEN 
                 DD_OBJECT_TYPE = 
                 'MS_SRA_VISIT_SETUP' then 2 end) RN 
        FROM   ms_sra_facility_info
        where  DD_OBJECT_TYPE in ( 'MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP' )) 
where  RN = 1) fac
where VSETUP.VISIT_ID =LP.VISIT_ID
  AND FAC.VISIT_ID =VSETUP.VISIT_ID 
   AND VSETUP.VISIT_STATUS IN ('1','2','3','4','5','6')
   AND LP.USER_ID = MS_APPS_UTILITIES.GET_USER_ID('2109760810')
   order by DECODE(VSETUP.VISIT_STATUS, '4', 1, '5', 2, '6', 3, '1',4,'2',5,'3',6) 
                 )
   LOOP
      -- For each record, create a new Dept element with the Department ID as attribute.
      -- and add this new Dept element to the Departments node
      l_visit_element := dbms_xmldom.createElement(l_domdoc, 'VISIT_ID' );
      dbms_xmldom.setAttribute(l_visit_element, 'VISIT_ID', r_visit.VISIT_ID );
      l_visit_node := dbms_xmldom.appendChild( l_open_site_visit
                                            , dbms_xmldom.makeNode(l_visit_element)
                                            );

      -- Each Dept node will get a Name node which contains the department name as text
      l_report_type_node := dbms_xmldom.appendChild( l_visit_node
                                            , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'REPORT_TYPE' ))
                                            );
      l_report_type_textnode := dbms_xmldom.appendChild( l_report_type_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_visit.REPORT_TYPE ))
                                                );

      -- Each Dept node will aslo get a Location node which contains the location(city) as text
      l_status_node := dbms_xmldom.appendChild( l_visit_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'VISIT_STATUS' ))
                                                );
      l_status_textnode := dbms_xmldom.appendChild( l_status_node
                                                    , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_visit.VISIT_STATUS ))
                                                    );

      -- For each department, add an Employees node
      l_audcompany_node := dbms_xmldom.appendChild( l_visit_node
                                                 , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'AUDIT_COMPANY' ))
                                                 );

      FOR r_audcomp IN (select (SELECT org_entity_name
          FROM SI_ORG_ENTITIES_T
         WHERE ORG_ENTITY_ID = aud.AUD_TPA_ID) AS AUDIT_COMPANY,
         AUD.AUDITOR AS AUDITOR,
         AUD.VISIT_ID as visit_id,
  MS_APPS_UTILITIES.GET_USER_FULL_NAME(AUD.AUDITOR) AS TPA_AUDITOR ,
  aud.IS_LEAD
FROM MS_SRA_VISIT_SETUP_AUD aud 
WHERE AUD.VISIT_ID = r_visit.VISIT_ID
                   )
      LOOP
         -- For each record, create a new Emp element with the Employee ID as attribute.
         -- and add this new Emp element to the Employees node
         l_auditcompany_element := dbms_xmldom.createElement(l_domdoc, 'AUDIT_COMPANY' );
         dbms_xmldom.setAttribute(l_auditcompany_element, 'AUDIT_COMPANY', r_audcomp.AUDIT_COMPANY );
         l_auditcompany_node := dbms_xmldom.appendChild( l_audcompany_node
                                              , dbms_xmldom.makeNode(l_auditcompany_element)
                                              );



         -- Each emp node will get a First name and Last name node which contains the first name and last name as text
         l_auditors_name_node := dbms_xmldom.appendChild( l_auditcompany_node
                                                         , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'AUDITOR' ))
                                                         );
         l_auditors_name_textnode := dbms_xmldom.appendChild( l_auditors_name_node
                                                             , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_audcomp.TPA_AUDITOR ))
                                                             );

         l_islead_name_node := dbms_xmldom.appendChild( l_auditors_name_node
                                                        , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'IS_LEAD' ))
                                                        );
         l_islead_name_textnode := dbms_xmldom.appendChild( l_islead_name_node
                                                            , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_audcomp.IS_LEAD ))
                                                            );
      
       -- For each audit company add an assignment node
l_asn_node := dbms_xmldom.appendChild( l_auditors_name_node
                                                 , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Assignments' ))
                                                 );

      FOR r_assn IN (SELECT asn.VISIT_ID,
  asn.ASSIGNMENTS,
  asn.auditor
FROM
  (SELECT 
    a.VISIT_ID ,
    a.auditor,
    MAX(a.rowid)                                                                             AS rn ,
    NVL(COALESCE(C.CATEGORY_NAME , S.SECTION_NAME, 'Facility'),'No Assignment to this User') AS ASSIGNMENTS
  FROM ms_sra_visit_setup_asn a
  LEFT JOIN MS_SRA_PROTOCOL_CATEGORY C
  ON SUBSTR(a.AUDITOR_ASSIGNMENTS,3,225) = C.CATEGORY_ID
  LEFT JOIN MS_SRA_PROTOCOL_SECTION S
  ON SUBSTR(A.AUDITOR_ASSIGNMENTS,3,255) =S.SECTION_ID
  GROUP BY A.AUDITOR_ASSIGNMENTS,
    A.VISIT_ID ,
    A.AUDITOR ,
    NVL(COALESCE(C.CATEGORY_NAME , S.SECTION_NAME, 'Facility'),'No Assignment to this User')
  ) ASN
WHERE  ASN.VISIT_ID = r_audcomp.visit_id
AND ASN.AUDITOR   = r_audcomp.auditor
)
/*union
SELECT 
 b.VISIT_ID,
  NULL AS ASSIGNMENTS
  ,b.auditor
FROM MS_SRA_VISIT_SETUP_AUD B
WHERE
NOT EXISTS
  (SELECT 1
  FROM MS_SRA_VISIT_SETUP_ASN A
  WHERE B.VISIT_ID = A.VISIT_ID
  AND A.AUDITOR    =B.AUDITOR
  )
                   )*/
      LOOP
        
         -- Each emp node will get a First name and Last name node which contains the first name and last name as text
         l_asn_node := dbms_xmldom.appendChild( l_auditors_name_node
                                                         , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Assignment' ))
                                                         );
         l_asn_node_textnode := dbms_xmldom.appendChild( l_asn_node
                                                             , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_assn.ASSIGNMENTS ))
                                                             );

         
      END LOOP;
      END LOOP;
   END LOOP;

   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
   dbms_xmldom.freeDocument(l_domdoc);

   l_result:=l_xmltype.getClobVal();
   
   return l_result;
   
END;

Open in new window


And attached is the screen shot like how I am getting now....

Current view

The way I am trying to get is like  first visit information and then the audit company details and then auditors  with assignments .
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39675680
Also tried with below query but still no luck:

select
       xmlelement(
       "OPEN_SITE_VISITS"
      ,XMLAGG(xmlelement(
          "VISIT", xmlattributes(LP.VISIT_ID as id)
          ,xmlforest(
                DECODE(vsetup.visit_status, '4', 'TODAY', '1','UPCOMING', '2', 'UPCOMING','3' ,'UPCOMING', '5','FINAL', '6','FINAL') report_type
                ,(ms_apps_utilities.get_display_value(100000  ,'MS SRA VISIT STATUS',vsetup.visit_status)) AS VISIT_STATUS
                ,vsetup.visit_status as visit_status_id
                ,lp.visit_id
                ,LP.USER_ID
                ,lp.APP_ASSIGNMENT_ID  
                ,lp.metric_id
                ,lp.pid
                ,lp.instance_id
                ,LP.IS_OFFLINE
                ,fac.facility_id
                ,fac.facility_name
                ,fac.city
                ,fac.state_province
                ,fac.country
                ,fac.facility_address
                ,FAC.FACILITY_LOCAL_NAME
                ,fac.facility_local_address
                ,FAC.LONGITUDE
                ,FAC.LATITUDE
                ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                                  ,'MS_SRA_VISIT' ,lp.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                                       as VISIT_EDIT_FLAG
                ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                                  ,'MS_SRA_VISIT_SETUP' ,vsetup.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                                       as VS_EDIT_FLAG 
                  ,vsetup.DD_PROCESS_INSTANCE_ID as vs_pid
                  , MS_APPS_UTILITIES.GET_INFOLET_ID('MS_SRA_VISIT_SETUP')  as VS_METRIC_ID
                  ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.ONSITE_LEAD) as ONSITE_LEAD
                  ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_LEAD) as APPLE_LEAD
                  ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_VERIFICATION_LEAD) as APPLE_VERIFICATION_LEAD
                  ,ms_apps_utilities.get_user_full_name(VSETUP.SR_PROGRAM_MANAGER) as SR_PROGRAM_MANAGER
           ,vsetup.start_date
                ,vsetup.protocol_name
                ,to_char(to_date(vsetup.tpa_submission_deadline
                                ,'dd-mon-yy')
                        ,'MON dd,yyyy') as TPA_DATE
                ,TO_CHAR(TO_DATE(VSETUP.FINAL_REPORT_DEADLINE
                                ,'dd-mon-yy')
                        ,'MON dd,yyyy') AS FINAL_REPORT_DEADLINE        
               ,(to_char(to_date(vsetup.start_date
                                 ,'dd-mon-yy')
                         ,'MON dd') || to_char(to_date(vsetup.end_date
                                                       ,'dd-mon-yy')
                                               ,' - MON dd, yyyy')) as SHOW_DATE
                                               
          
          ,XMLAGG( XMLELEMENT(
                                                      "AUDIT_COMPANY", XMLATTRIBUTES(aud_asn.AUDIT_COMPANY as Company_name)
                                                      --XMLFOREST(TPA_AUDITOR,  XMLE(XMLFOREST(ASSIGNMENTS)) "ASSIGNMENTS")
                                                    
                                                     -- XMLAGG(XMLFOREST(TPA_AUDITOR))
                                                    
                                          ))"AUDIT_COMPANIES"
          
          
         /* ,aud_asn.AUDIT_COMPANY
          ,aud_asn.TPA_AUDITOR
          ,aud_asn.IS_LEAD 
          ,AUD_ASN.ASSIGNMENTS */
)--XMLFOREST
)--XMLELEMENT
)--XMLAGG
)
XML 
from MS_SRA_VISIT_LP_RPT LP,
  MS_SRA_VISIT_SETUP VSETUP,
( SELECT *
from   (select
      VISIT_ID
      ,FACILITY_ID
      ,DD_OBJECT_TYPE 
      ,facility_name
      ,city
      ,state_province
      ,country
      ,facility_address
      ,FACILITY_LOCAL_NAME
      ,facility_local_address
      ,LONGITUDE
        ,LATITUDE
               ,Row_number() 
                 over ( 
                   PARTITION BY visit_id 
                   ORDER BY CASE WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1 WHEN 
                 DD_OBJECT_TYPE = 
                 'MS_SRA_VISIT_SETUP' then 2 end) RN 
        FROM   ms_sra_facility_info
        where  DD_OBJECT_TYPE in ( 'MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP' )) 
where  RN = 1) fac,
(SELECT AUD.VISIT_ID,
  (SELECT org_entity_name
          FROM SI_ORG_ENTITIES_T
         where ORG_ENTITY_ID = aud.AUD_TPA_ID) as AUDIT_COMPANY,
  MS_APPS_UTILITIES.GET_USER_FULL_NAME(AUD.AUDITOR) AS TPA_AUDITOR ,
  aud.IS_LEAD,
  asn.ASSIGNMENTS
FROM MS_SRA_VISIT_SETUP_AUD aud,
  (SELECT a.AUDITOR_ASSIGNMENTS,
    a.VISIT_ID ,
    a.auditor,
    MAX(a.rowid)                                                                             AS rn ,
    NVL(COALESCE(C.CATEGORY_NAME , S.SECTION_NAME, 'Facility'),'No Assignment to this User') AS ASSIGNMENTS
  FROM ms_sra_visit_setup_asn a
  LEFT JOIN MS_SRA_PROTOCOL_CATEGORY C
  ON SUBSTR(a.AUDITOR_ASSIGNMENTS,3,225) = C.CATEGORY_ID
  LEFT JOIN MS_SRA_PROTOCOL_SECTION S
  ON SUBSTR(A.AUDITOR_ASSIGNMENTS,3,255) =S.SECTION_ID
  GROUP BY A.AUDITOR_ASSIGNMENTS,
    A.VISIT_ID ,
    A.AUDITOR ,
    NVL(COALESCE(C.CATEGORY_NAME , S.SECTION_NAME, 'Facility'),'No Assignment to this User')
  ) ASN
WHERE AUD.VISIT_ID = ASN.VISIT_ID
AND ASN.AUDITOR    =AUD.AUDITOR  union
SELECT 
 b.VISIT_ID,
  (SELECT org_entity_name
          FROM SI_ORG_ENTITIES_T
         where ORG_ENTITY_ID = b.AUD_TPA_ID) as AUDIT_COMPANY,
  MS_APPS_UTILITIES.GET_USER_FULL_NAME(b.AUDITOR) AS TPA_AUDITOR ,
  b.IS_LEAD,
  NULL AS ASSIGNMENTS
FROM MS_SRA_VISIT_SETUP_AUD B
WHERE
NOT EXISTS
  (SELECT 1
  FROM MS_SRA_VISIT_SETUP_ASN A
  WHERE B.VISIT_ID = A.VISIT_ID
  AND A.AUDITOR    =B.AUDITOR
  )) aud_asn
where VSETUP.VISIT_ID =LP.VISIT_ID
  AND FAC.VISIT_ID =VSETUP.VISIT_ID 
   and aud_asn.visit_id =VSETUP.VISIT_ID 
   AND VSETUP.VISIT_STATUS IN ('1','2','3','4','5','6')
   AND LP.USER_ID = MS_APPS_UTILITIES.GET_USER_ID('2109760810')
   
   group by 
         --REPORT_TYPE
         DECODE(VSETUP.VISIT_STATUS, '4', 'TODAY', '1','UPCOMING', '2', 'UPCOMING','3' ,'UPCOMING', '5','FINAL', '6','FINAL')
        ,VISIT_STATUS
        --,VISIT_STATUS_ID
        ,vsetup.visit_status
        ,lp.visit_id
        ,LP.USER_ID
        ,lp.APP_ASSIGNMENT_ID  
        ,lp.metric_id
        ,lp.pid
        ,lp.instance_id
        ,LP.IS_OFFLINE
        ,fac.facility_id
        ,fac.facility_name
        ,fac.city
        ,fac.state_province
        ,fac.country
        ,fac.facility_address
        ,FAC.FACILITY_LOCAL_NAME
        ,fac.facility_local_address
        ,FAC.LONGITUDE
        ,FAC.LATITUDE
        --,VISIT_EDIT_FLAG
        ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                                  ,'MS_SRA_VISIT' ,lp.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
        --,VS_EDIT_FLAG
        ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760810'
                                                  ,'MS_SRA_VISIT_SETUP' ,vsetup.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
        
        ,vsetup.DD_PROCESS_INSTANCE_ID
        --,VS_METRIC_ID
        ,MS_APPS_UTILITIES.GET_INFOLET_ID('MS_SRA_VISIT_SETUP')
        ,ONSITE_LEAD
        ,APPLE_LEAD
        ,APPLE_VERIFICATION_LEAD
        ,SR_PROGRAM_MANAGER
        ,VSETUP.START_DATE
        ,VSETUP.PROTOCOL_NAME

        ,to_char(to_date(vsetup.tpa_submission_deadline
                                ,'dd-mon-yy')
                        ,'MON dd,yyyy')
        ,FINAL_REPORT_DEADLINE
   
        ,(to_char(to_date(vsetup.start_date
                                 ,'dd-mon-yy')
                         ,'MON dd') || to_char(to_date(vsetup.end_date
                                                       ,'dd-mon-yy')
                                               ,' - MON dd, yyyy'))
        
        ;

Open in new window

0
 
LVL 20

Expert Comment

by:flow01
ID: 39676140
I'm confused
I assume my suggestion didn't have the desired effect.
Are we talking about 1 or more functions ?
Can you give the desired results?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39676498
We are talking about one function. But if you see I just changed the variable names and added one more SQL for assignments in the function and named it by adding "_dl" .

And I was also trying to get the expected result in a simple SQL statement which I mentioned above .

The desired result will be like as below:
<OPEN_SITE_VISIT>
<VISIT_ID NAME="101">
   <VISIT_TYPE>visit</VISIT_TYPE>
   <VISIT_LOC>usa</VISIT_LOC>
   <AUDIT_COMPANY COMPANY_NAME="oracle">
      <AUDITOR>
         <AUDITORNAME>sandy</AUDITORNAME>
         <ISLEAD>0</ISLEAD>
         <ASSIGNMENTS>
            <ASSIGNMENTS>d</ASSIGNMENTS>
         </ASSIGNMENTS>
      </AUDITOR>
      <AUDITOR>
         <AUDITORNAME>sam</AUDITORNAME>
         <ISLEAD>1</ISLEAD>
         <ASSIGNMENTS>
            <ASSIGNMENTS>d</ASSIGNMENTS>
            <ASSIGNMENTS>d</ASSIGNMENTS>
         </ASSIGNMENTS>
      </AUDITOR>
   </AUDIT_COMPANY>
</VISIT_ID>
</OPEN_SITE_VISIT>

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39677745
This xml looks very similar to another question, are the questions related?

http://www.experts-exchange.com/Database/Oracle/Q_28303402.html
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39677830
@sdstuber : That doesn't look similar ....that question is same as what I asked here.
The only difference is the method of getting the result is different.

But I was trying to do it with XMLDOM and there I see the similar method what you have posted there and what I was trying to do it using "xmlelement" ...

In my post : 39675643

I have created the function referring to :
http://allthingsoracle.com/generating-xml-from-sql-and-plsql-part-2/

Where I am lacking to match with the example provided......

But no doubt someone from my team is also having experts-exchange ID..
Because this is the same thing what my question was.

But still I don't want to close this question or delete it due to duplicate questions but I know the answers will be different and the way of getting the result is also different.


But if you still feel that this question need to be deleted then please let me know .
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39678023
No, it's not a duplicate but since it seemed related I was wondering if there was a possibility of consolidating answers.
flow01's already helping, I'll let him continue.  It would be a lot easier if you could provide a test bed for him (or anybody else trying) to work from though.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:flow01
ID: 39679030
And is your desired result based on the same data as the result you showed in your first post. I don't see the match.

Can you give the current result of your function ?
A set of  real data your select statements provide would be very useful.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39682861
Here I have created three tables with sample data.

Tables :
Sample1 (Is the main loop)
Sample2 can be used in the 2nd loop
sample3 can be used in the 3rd loop.

Here are the structure and insert SQL files.
Sample-Table.sql
Sample-Table-Insert-Script.sql
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39682895
Here is the function which I created :

CREATE OR REPLACE FUNCTION  get_xml_data RETURN CLOB IS
        l_result  CLOB;
        l_domdoc  dbms_xmldom.domdocument;
        l_xmltype xmltype;

        l_root_node dbms_xmldom.domnode;

        l_open_site_visit dbms_xmldom.domnode;

        l_visit_element dbms_xmldom.domelement;
        l_visit_node    dbms_xmldom.domnode;

        l_report_type_node     dbms_xmldom.domnode;
        l_report_type_textnode dbms_xmldom.domnode;

        l_status_node     dbms_xmldom.domnode;
        l_status_textnode dbms_xmldom.domnode;

        l_audcompany_node dbms_xmldom.domnode;

        l_auditcompany_element dbms_xmldom.domelement;
        l_auditcompany_node    dbms_xmldom.domnode;

        l_auditors_name_node     dbms_xmldom.domnode;
        l_auditors_name_textnode dbms_xmldom.domnode;

        l_islead_name_node     dbms_xmldom.domnode;
        l_islead_name_textnode dbms_xmldom.domnode;

        --l_auditors_name_node dbms_xmldom.DOMNode;

        l_asn_node          dbms_xmldom.domnode;
        l_asn_node_textnode dbms_xmldom.domnode;

BEGIN
        -- Create an empty XML document
        l_domdoc := dbms_xmldom.newdomdocument;

        -- Create a root node
        l_root_node := dbms_xmldom.makenode(l_domdoc);

        -- Create a new node Departments and add it to the root node
        l_open_site_visit := dbms_xmldom.appendchild(l_root_node
                                                    ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                   ,'OPEN_SITE_VISIT')));

        FOR r_visit IN (SELECT * FROM sample1)
        LOOP
                -- For each record, create a new visit element with the visit ID as attribute.
                -- and add this new visit element to the visit node
                l_visit_element := dbms_xmldom.createelement(l_domdoc
                                                            ,'VISIT_ID');
                dbms_xmldom.setattribute(l_visit_element
                                        ,'VISIT_ID'
                                        ,r_visit.visit_id);
                l_visit_node := dbms_xmldom.appendchild(l_open_site_visit
                                                       ,dbms_xmldom.makenode(l_visit_element));
        
                l_report_type_node     := dbms_xmldom.appendchild(l_visit_node
                                                                 ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                ,'REPORT_TYPE')));
                l_report_type_textnode := dbms_xmldom.appendchild(l_report_type_node
                                                                 ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                 ,r_visit.report_type)));
        
                l_status_node     := dbms_xmldom.appendchild(l_visit_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                           ,'VISIT_STATUS')));
                l_status_textnode := dbms_xmldom.appendchild(l_status_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                            ,r_visit.visit_status)));
        
                -- For each visit, add an audit company node
                l_audcompany_node := dbms_xmldom.appendchild(l_visit_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                           ,'AUDIT_COMPANY')));
        
                FOR r_audcomp IN (SELECT * FROM sample2 WHERE visit_id = r_visit.visit_id)
                LOOP
                
                        l_auditcompany_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'AUDIT_COMPANY');
                        dbms_xmldom.setattribute(l_auditcompany_element
                                                ,'AUDIT_COMPANY'
                                                ,r_audcomp.audit_company);
                        l_auditcompany_node := dbms_xmldom.appendchild(l_audcompany_node
                                                                      ,dbms_xmldom.makenode(l_auditcompany_element));
                
                        l_auditors_name_node     := dbms_xmldom.appendchild(l_auditcompany_node
                                                                           ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                          ,'AUDITOR')));
                        l_auditors_name_textnode := dbms_xmldom.appendchild(l_auditors_name_node
                                                                           ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                           ,r_audcomp.tpa_auditor)));
                
                        l_islead_name_node     := dbms_xmldom.appendchild(l_auditors_name_node
                                                                         ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                        ,'IS_LEAD')));
                        l_islead_name_textnode := dbms_xmldom.appendchild(l_islead_name_node
                                                                         ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                         ,r_audcomp.is_lead)));
                
                        -- For each audit company add an assignment node
                        l_asn_node := dbms_xmldom.appendchild(l_auditors_name_node
                                                             ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                            ,'Assignments')));
                
                        FOR r_assn IN (SELECT *
                                         FROM sample3
                                        WHERE visit_id = r_audcomp.visit_id
                                          AND auditor = r_audcomp.auditor)
                        LOOP
                        
                                l_asn_node          := dbms_xmldom.appendchild(l_auditors_name_node
                                                                              ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                             ,'Assignment')));
                                l_asn_node_textnode := dbms_xmldom.appendchild(l_asn_node
                                                                              ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                              ,r_assn.assignments)));
                        
                        END LOOP;
                END LOOP;
        END LOOP;

        l_xmltype := dbms_xmldom.getxmltype(l_domdoc);
        dbms_xmldom.freedocument(l_domdoc);

        l_result := l_xmltype.getclobval();

        RETURN l_result;

END;

Open in new window


Output of the function:

SQL> select get_xml_data() from dual;
<OPEN_SITE_VISIT>
  <VISIT_ID VISIT_ID="V1-4884-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
          <Assignment>Health &amp; Safety</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Supplier Responsibility">
        <AUDITOR>Ryan SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Emergency Prevention, Preparedness and Response</Assignment>
          <Assignment>Hazardous Substance Management and Restrictions</Assignment>
          <Assignment>Management Systems</Assignment>
          <Assignment>Occupational Injury Prevention</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Supplier Responsibility">
        <AUDITOR>Gia SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Emergency Prevention, Preparedness and Response</Assignment>
          <Assignment>Hazardous Substance Management and Restrictions</Assignment>
          <Assignment>Hazardous Substance Management and Restrictions 1</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Management Accountability and Responsibility</Assignment>
          <Assignment>Management Systems</Assignment>
          <Assignment>Occupational Injury Prevention</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V1-5776-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V4-7595-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V4-5346-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V2-5354-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Bradley SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V8-5342-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V11-5300-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Charles SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Emergency Prevention, Preparedness and Response</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Hazardous Substance Management and Restrictions</Assignment>
          <Assignment>Hazardous Substance Management and Restrictions 1</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Emergency Prevention, Preparedness and Response</Assignment>
          <Assignment>Ethics</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Management Systems</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V98-5293-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Scott SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Property Protection</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Charles SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Ethics</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V15-5343-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Leo SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V27-5343-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V2-4884-2013-11">
    <REPORT_TYPE>TODAY</REPORT_TYPE>
    <VISIT_STATUS>Audit In Progress</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V13-4002-2013-11">
    <REPORT_TYPE>FINAL</REPORT_TYPE>
    <VISIT_STATUS>Pending TPA Submission</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Charles SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Scott SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Supplier Responsibility">
        <AUDITOR>Gia SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Supplier Responsibility">
        <AUDITOR>Ruth SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Supplier Responsibility">
        <AUDITOR>Ryan SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Leo SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Maggie SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Maria SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Vick SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V22-5302-2013-11">
    <REPORT_TYPE>FINAL</REPORT_TYPE>
    <VISIT_STATUS>Pending TPA Submission</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V18-5298-2013-11">
    <REPORT_TYPE>FINAL</REPORT_TYPE>
    <VISIT_STATUS>Pending Final Report</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Bradley SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Beth Griffith<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V5-5349-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V35-5300-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Supplier Responsibility">
        <AUDITOR>Ryan SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V3-5377-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Charles SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V3-5346-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Facility</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
          <Assignment>Labor &amp; Human Rights</Assignment>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Sathwick Rao<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Charles SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V295-5287-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Maria SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Hazardous Substance Management and Restrictions</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Yasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Environment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Sathwick Rao<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Hazardous Substance Management and Restrictions</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="PWC">
        <AUDITOR>Leo SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Environment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V280-5287-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V24-5300-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V23-5300-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V22-5300-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V2-5377-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Labor &amp; Human Rights</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Antidiscrimination</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Sathwick Rao<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Wages &amp; Benefits</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Sathwick5 Rao5<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V16-5342-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Environment</Assignment>
          <Assignment>Health &amp; Safety</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V12-5344-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Environment</Assignment>
          <Assignment>Health &amp; Safety</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V9-5342-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Audit Planned</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>David SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jake SGM<IS_LEAD>0</IS_LEAD>
          <Assignments/>
          <Assignment>Environment</Assignment>
          <Assignment>Ethics</Assignment>
          <Assignment>Hazardous Substance Management and Restrictions</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Jasmin SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Business Integrity</Assignment>
          <Assignment>Disclosure of Information</Assignment>
          <Assignment>Solid Waste Management</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Verite">
        <AUDITOR>Charles SGM<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>No Assignment</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
  <VISIT_ID VISIT_ID="V1-5414-2013-11">
    <REPORT_TYPE>UPCOMING</REPORT_TYPE>
    <VISIT_STATUS>Pre-Audit - Facility Profile Requested</VISIT_STATUS>
    <AUDIT_COMPANY>
      <AUDIT_COMPANY AUDIT_COMPANY="Golder">
        <AUDITOR>Sathwick Rao<IS_LEAD>1</IS_LEAD>
          <Assignments/>
          <Assignment>Facility</Assignment>
        </AUDITOR>
      </AUDIT_COMPANY>
    </AUDIT_COMPANY>
  </VISIT_ID>
</OPEN_SITE_VISIT>

Open in new window

0
 
LVL 20

Expert Comment

by:flow01
ID: 39684051
The structure corresponds to your example: you might have to change some datasource columns (for example visit_type)

CREATE OR REPLACE FUNCTION  get_xml_data RETURN CLOB IS
        l_result  CLOB;
        l_domdoc  dbms_xmldom.domdocument;
        l_xmltype xmltype;

        l_root_node dbms_xmldom.domnode;

        l_open_site_visit dbms_xmldom.domnode;

        l_visit_element dbms_xmldom.domelement;
        l_visit_node    dbms_xmldom.domnode;

        l_visit_type_node     dbms_xmldom.domnode;
        l_visit_type_textnode dbms_xmldom.domnode;

        l_visit_location_node     dbms_xmldom.domnode;
        l_visit_location_textnode dbms_xmldom.domnode;

        l_audcompany_node dbms_xmldom.domnode;

        l_auditcompany_element dbms_xmldom.domelement;
        l_auditcompany_node    dbms_xmldom.domnode;

        l_auditor_element      dbms_xmldom.domelement;
        l_auditor_node        dbms_xmldom.domnode;
       
        l_auditors_name_node     dbms_xmldom.domnode;
        l_auditors_name_textnode dbms_xmldom.domnode;

        l_islead_name_node     dbms_xmldom.domnode;
        l_islead_name_textnode dbms_xmldom.domnode;

        --l_auditors_name_node dbms_xmldom.DOMNode;
        l_assignments_element      dbms_xmldom.domelement;
        l_assignments_node        dbms_xmldom.domnode;

        l_asn_node          dbms_xmldom.domnode;
        l_asn_node_textnode dbms_xmldom.domnode;

BEGIN
        -- Create an empty XML document
        l_domdoc := dbms_xmldom.newdomdocument;

        -- Create a root node
        l_root_node := dbms_xmldom.makenode(l_domdoc);

        -- Create a new node Departments and add it to the root node
        l_open_site_visit := dbms_xmldom.appendchild(l_root_node
                                                    ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                   ,'OPEN_SITE_VISIT')));

        FOR r_visit IN (SELECT * FROM sample1 )
        LOOP
                -- For each record, create a new visit element with the visit ID as attribute.
                -- and add this new visit element to the visit node
                l_visit_element := dbms_xmldom.createelement(l_domdoc
                                                            ,'VISIT_ID');
                dbms_xmldom.setattribute(l_visit_element
                                        ,'NAME'
                                        ,r_visit.FACILITY_NAME);
                l_visit_node := dbms_xmldom.appendchild(l_open_site_visit
                                                       ,dbms_xmldom.makenode(l_visit_element));
       
                l_visit_type_node     := dbms_xmldom.appendchild(l_visit_node
                                                                 ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                ,'VISIT_TYPE')));
                l_visit_type_textnode := dbms_xmldom.appendchild(l_visit_type_node
                                                                 ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                 ,r_visit.report_type)));    --## change to column with visit_type
       
                l_visit_location_node     := dbms_xmldom.appendchild(l_visit_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                           ,'VISIT_LOC')));
                l_visit_location_textnode := dbms_xmldom.appendchild(l_visit_location_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                            ,r_visit.COUNTRY  )));
       
       
                FOR r_audcomp IN (SELECT * FROM sample2 WHERE visit_id = r_visit.visit_id)
                LOOP
               
                        l_auditcompany_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'AUDIT_COMPANY');
                        dbms_xmldom.setattribute(l_auditcompany_element
                                                ,'COMPANY_NAME'
                                                ,r_audcomp.audit_company);
                        l_auditcompany_node := dbms_xmldom.appendchild(l_visit_node
                                                                      ,dbms_xmldom.makenode(l_auditcompany_element));
                        l_auditor_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'AUDITOR');                                              
                                                                     
                        l_auditor_node     :=  dbms_xmldom.appendchild(l_auditcompany_node
                                                                       ,dbms_xmldom.makenode(l_auditor_element));
                                                                       
                        l_auditors_name_node     := dbms_xmldom.appendchild(l_auditor_node
                                                                           ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                          ,'AUDITORNAME')));
                        l_auditors_name_textnode := dbms_xmldom.appendchild(l_auditors_name_node
                                                                           ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                           ,r_audcomp.tpa_auditor)));
               
                        l_islead_name_node     := dbms_xmldom.appendchild(l_auditor_node
                                                                         ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                        ,'IS_LEAD')));
                        l_islead_name_textnode := dbms_xmldom.appendchild(l_islead_name_node
                                                                         ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                         ,r_audcomp.is_lead)));
               
                        -- For each auditor add an assignment element
                        l_assignments_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'ASSIGNMENTS');                                              
                        l_assignments_node := dbms_xmldom.appendchild(l_auditor_node
                                                             ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                            ,'ASSIGNMENTS')));
               
                        FOR r_assn IN (SELECT *
                                         FROM sample3
                                        WHERE visit_id = r_audcomp.visit_id
                                          AND auditor = r_audcomp.auditor)
                        LOOP
                       
                                l_asn_node          := dbms_xmldom.appendchild(l_assignments_node
                                                                              ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                             ,'ASSIGNMENT')));
                                l_asn_node_textnode := dbms_xmldom.appendchild(l_asn_node
                                                                              ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                              ,r_assn.assignments)));
                       
                        END LOOP;
                END LOOP;
        END LOOP;

        l_xmltype := dbms_xmldom.getxmltype(l_domdoc);
        dbms_xmldom.freedocument(l_domdoc);

        l_result := l_xmltype.getclobval();

        RETURN l_result;

END;
/
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39684704
But I don't have anything called  visit_type .... in my source columns ..
0
 
LVL 20

Expert Comment

by:flow01
ID: 39685686
I could not find it either : but Look at your desired results some posts ago.
There is a tag VISIT_ID . I created the tag, you have to determine what column value(or literal for example 'visit')  it should get.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39685875
oh! ok got it ... <VISIT_TYPE>visit</VISIT_TYPE> this is the one you are talking about ..

I made the change to report type and now when I run the function i get everything correctly except AUDIT_COMPANY.

Attached here with the screen shot :Function OUTPUT

If you see the output you will find that for one visit there is 4 audit company but actually there are 2 audit company and under that there can be multiple auditors.

But say I have 2 audit company and each company has 2 auditors , so as per the current logic I am seeing 4 audit company because i have 4 auditors under that ..

Only one issue there , rest of them are fine..
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39686210
You will need another level  : of company
You can do it to add a additional cursor between visit and auditor
or to determine the first occurrence of a new company in the auditor-cursor

I choose the last option

CREATE OR REPLACE FUNCTION  get_xml_data RETURN CLOB IS
        l_result  CLOB;
        l_domdoc  dbms_xmldom.domdocument;
        l_xmltype xmltype;

        l_root_node dbms_xmldom.domnode;

        l_open_site_visit dbms_xmldom.domnode;

        l_visit_element dbms_xmldom.domelement;
        l_visit_node    dbms_xmldom.domnode;

        l_visit_type_node     dbms_xmldom.domnode;
        l_visit_type_textnode dbms_xmldom.domnode;

        l_visit_location_node     dbms_xmldom.domnode;
        l_visit_location_textnode dbms_xmldom.domnode;

        l_audcompany_node dbms_xmldom.domnode;

        l_auditcompany_element dbms_xmldom.domelement;
        l_auditcompany_node    dbms_xmldom.domnode;

        l_auditor_element      dbms_xmldom.domelement;
        l_auditor_node        dbms_xmldom.domnode;
       
        l_auditors_name_node     dbms_xmldom.domnode;
        l_auditors_name_textnode dbms_xmldom.domnode;

        l_islead_name_node     dbms_xmldom.domnode;
        l_islead_name_textnode dbms_xmldom.domnode;

        --l_auditors_name_node dbms_xmldom.DOMNode;
        l_assignments_element      dbms_xmldom.domelement;
        l_assignments_node        dbms_xmldom.domnode;

        l_asn_node          dbms_xmldom.domnode;
        l_asn_node_textnode dbms_xmldom.domnode;

BEGIN
        -- Create an empty XML document
        l_domdoc := dbms_xmldom.newdomdocument;

        -- Create a root node
        l_root_node := dbms_xmldom.makenode(l_domdoc);

        -- Create a new node Departments and add it to the root node
        l_open_site_visit := dbms_xmldom.appendchild(l_root_node
                                                    ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                   ,'OPEN_SITE_VISIT')));

        FOR r_visit IN (SELECT * FROM sample1
                --       where visit_id = 'V27-5343-2013-11'
                       )
        LOOP
                -- For each record, create a new visit element with the visit ID as attribute.
                -- and add this new visit element to the visit node
                l_visit_element := dbms_xmldom.createelement(l_domdoc
                                                            ,'VISIT_ID');
                dbms_xmldom.setattribute(l_visit_element
                                        ,'NAME'
                                        ,r_visit.FACILITY_NAME);
                l_visit_node := dbms_xmldom.appendchild(l_open_site_visit
                                                       ,dbms_xmldom.makenode(l_visit_element));
       
                l_visit_type_node     := dbms_xmldom.appendchild(l_visit_node
                                                                 ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                ,'VISIT_TYPE')));
                l_visit_type_textnode := dbms_xmldom.appendchild(l_visit_type_node
                                                                 ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                 ,r_visit.report_type)));    --## change to column with visit_type
       
                l_visit_location_node     := dbms_xmldom.appendchild(l_visit_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                           ,'VISIT_LOC')));
                l_visit_location_textnode := dbms_xmldom.appendchild(l_visit_location_node
                                                            ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                            ,r_visit.COUNTRY  )));
       
       
                FOR r_audcomp IN (SELECT s2.* , rank() over (partition by s2.audit_company  order by auditor) rnk FROM sample2 s2 WHERE visit_id = r_visit.visit_id)
                                           -- better to do  (partition by s2.AUD_TPA_ID  ..) but that is not availeble in the sample2 but can be used in your original cursor
                LOOP
                   IF r_audcomp.RNK = 1 THEN -- create a new audit_company element only once per audit_company
                        l_auditcompany_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'AUDIT_COMPANY');
                        dbms_xmldom.setattribute(l_auditcompany_element
                                                ,'COMPANY_NAME'
                                                ,r_audcomp.audit_company);
                        l_auditcompany_node := dbms_xmldom.appendchild(l_visit_node
                                                                      ,dbms_xmldom.makenode(l_auditcompany_element));

                   END IF;
                        l_auditor_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'AUDITOR');                                              
                                                                     
                        l_auditor_node     :=  dbms_xmldom.appendchild(l_auditcompany_node
                                                                       ,dbms_xmldom.makenode(l_auditor_element));
                                                                       
                        l_auditors_name_node     := dbms_xmldom.appendchild(l_auditor_node
                                                                           ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                          ,'AUDITORNAME')));
                        l_auditors_name_textnode := dbms_xmldom.appendchild(l_auditors_name_node
                                                                           ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                           ,r_audcomp.tpa_auditor)));
               
                        l_islead_name_node     := dbms_xmldom.appendchild(l_auditor_node
                                                                         ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                        ,'IS_LEAD')));
                        l_islead_name_textnode := dbms_xmldom.appendchild(l_islead_name_node
                                                                         ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                         ,r_audcomp.is_lead)));
               
                        -- For each auditor add an assignment element
                        l_assignments_element := dbms_xmldom.createelement(l_domdoc
                                                                           ,'ASSIGNMENTS');                                              
                        l_assignments_node := dbms_xmldom.appendchild(l_auditor_node
                                                             ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                            ,'ASSIGNMENTS')));
               
                        FOR r_assn IN (SELECT *
                                         FROM sample3
                                        WHERE visit_id = r_audcomp.visit_id
                                          AND auditor = r_audcomp.auditor)
                        LOOP
                       
                                l_asn_node          := dbms_xmldom.appendchild(l_assignments_node
                                                                              ,dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc
                                                                                                                             ,'ASSIGNMENT')));
                                l_asn_node_textnode := dbms_xmldom.appendchild(l_asn_node
                                                                              ,dbms_xmldom.makenode(dbms_xmldom.createtextnode(l_domdoc
                                                                                                                              ,r_assn.assignments)));
                       
                        END LOOP;
                END LOOP;
        END LOOP;

        l_xmltype := dbms_xmldom.getxmltype(l_domdoc);
        dbms_xmldom.freedocument(l_domdoc);

        l_result := l_xmltype.getclobval();

        RETURN l_result;

END;
/
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39686862
Awesome...................... Thanks a ton for this.......
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now