AnsweredAssumed Answered

CA PPM 15.3 - GEL SCRIPT - parsing xml variable

Question asked by RnFernandez on Jul 31, 2018
Latest reply on Jul 31, 2018 by RnFernandez

Hello everyone, I can not find a way to deal with the different labels of a XML structure.


After executing an ca ppm nsql query sb_ctrl_carga_horas:

 

    <!-- Generation xog-->
   <gel:parse var="xmlQuery">
          <q:Query xmlns:q="http://www.niku.com/xog/Query">
               <q:Code>sb_ctrl_carga_horas</q:Code>
               <q:Filter> <!-- filtro de la estructura xog -->
                    <q:team_lider_email>${email_teamLeader}</q:team_lider_email>
                    <q:fecha_from>2018-06-01T00:00:00</q:fecha_from>
                    <q:fecha_to>2018-06-10T00:00:00</q:fecha_to>
                    <q:activo_n></q:activo_n>
                    <q:obs>.ERR</q:obs>
               </q:Filter>
          </q:Query>
          <gel:out>Query sb_ctrl_carga_horas OK </gel:out>
     </gel:parse>

 

I am trying to unsuccessfully parsing the result of the query hosted in the variable "xmlReturn"

See Generation response

    <!-- Generation response-->
     <soap:invoke endpoint="http://hostClarity/niku/xog" var="xmlReturn">
           <soap:message>
                 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog" >
                       <soapenv:Header>
                              <xog:Auth>
                                    <xog:Username>admin</xog:Username>
                                    <xog:Password>********</xog:Password>
                              </xog:Auth>
                       </soapenv:Header>
                       <soapenv:Body>
                              <gel:include select="$xmlQuery"/>
                       </soapenv:Body>
                 </soapenv:Envelope>
           </soap:message>
     </soap:invoke>

see all gel script

<gel:script 
     xmlns:sql="jelly:sql"
     xmlns:core="jelly:core" 
     xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
     xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" 
     xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
     xmlns:xog="http://www.niku.com/xog"
     xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary">


<gel:setDataSource dbId="Niku" var="ClarityDS"/>
<gel:out>nueva ejecucion - - - - - - - - - - - - - - - - - - -</gel:out>

<sql:query dataSource="${ClarityDS}" escapeText="false" var="resource">
   <![CDATA[
select  distinct MNG.email
     from   PRJ_OBS_ASSOCIATIONS  ***, PRJ_OBS_UNITS UNIT, PRJ_OBS_TYPES TYPE,
                DEPARTMENTS DEP, ODF_CA_DEPARTMENT DEP2, SRM_RESOURCES MNG

     Where ***.TABLE_NAME          = 'SRM_RESOURCES'
          and TYPE.UNIQUE_NAME     = 'sb_dept'
          and ***.UNIT_ID = UNIT.ID
          and UNIT.TYPE_ID  = TYPE.ID
          and UNIT.ID = DEP.OBS_UNIT_ID
          and DEP.Id = DEP2.Id
          and DEP2.SB_MANAGER = MNG.ID 
ORDER BY MNG.EMAIL
   ]]>
 
</sql:query>

<core:forEach trim="true" items="${resource.rowsByIndex}" var="row">
   <core:set value="${row[0]}" var="email_teamLeader"/>
   <core:set var="RowDetails">Envio de email a: ${email_teamLeader}</core:set>
   <gel:out>${RowDetails}</gel:out>
  
    <!-- Generacion de xog-->
   <gel:parse var="xmlQuery">
          <q:Query xmlns:q="http://www.niku.com/xog/Query">
               <q:Code>sb_ctrl_carga_horas</q:Code>
               <q:Filter> <!-- filtro de la estructura xog -->
                    <q:team_lider_email>${email_teamLeader}</q:team_lider_email>
                    <q:fecha_from>2018-06-01T00:00:00</q:fecha_from>
                    <q:fecha_to>2018-06-10T00:00:00</q:fecha_to>
                    <q:activo_n></q:activo_n>
                    <q:obs>.ERR</q:obs>
               </q:Filter>
          </q:Query>
          <gel:out>Query sb_ctrl_carga_horas OK</gel:out>
     </gel:parse>
    
    <!-- Generacion de response-->
     <soap:invoke endpoint="http://hostClarity/niku/xog" var="xmlReturn">
           <soap:message>
                 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog" >
                       <soapenv:Header>
                              <xog:Auth>
                                    <xog:Username>admin</xog:Username>
                                    <xog:Password>********</xog:Password>
                              </xog:Auth>
                       </soapenv:Header>
                       <soapenv:Body>
                              <gel:include select="$xmlQuery"/>
                       </soapenv:Body>
                 </soapenv:Envelope>
           </soap:message>
     </soap:invoke>
    
     <gel:out>
         <gel:expr select="$xmlReturn"/>
    </gel:out>
    
</core:forEach>
<gel:out>FINALIZÓ</gel:out>

</gel:script>

 

This is the result in the log bg-system.txt

              <gel:out>
                  <gel:expr select="$xmlReturn"/>
              </gel:out>

 

I want to do a forEach to take the values of the fields soapenv:Envelope/soapenv:body/QueryResult/QueryResult/Records/Record/team_lider, etc

 

2018/07/31 09:57:08.154 | nueva ejecucion - - - - - - - - - - - - - - - - - - -
2018/07/31 09:57:08.154 | Envio de email a: emailName01@domain.com.ar
2018/07/31 09:57:08.154 | Query sb_ctrl_carga_horas OK -
2018/07/31 09:57:22.591 | <?xml version="1.0" encoding="UTF-8"?>
2018/07/31 09:57:22.591 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
2018/07/31 09:57:22.607 |   <soapenv:Header/>
2018/07/31 09:57:22.607 |   <soapenv:Body>
2018/07/31 09:57:22.607 |     <SessionID xmlns="http://www.niku.com/xog">26037335__5E437780-FACC-428A-ABB8-00E9720604D6</SessionID>
2018/07/31 09:57:22.623 |     <QueryResult xmlns="http://www.niku.com/xog/Query">
2018/07/31 09:57:22.623 |       <Code>sb_ctrl_carga_horas</Code>
2018/07/31 09:57:22.638 |       <Records>
2018/07/31 09:57:22.638 |         <Record>
2018/07/31 09:57:22.638 |           <team_lider>lastName01, Name01</team_lider>
2018/07/31 09:57:22.638 |           <team_lider_email>emailName01@domain.com.ar</team_lider_email>
2018/07/31 09:57:22.654 |           <name_resource>lastName02, Name 02</name_resource>
2018/07/31 09:57:22.654 |           <resource_email>emailName02@domain.com.ar</resource_email>
2018/07/31 09:57:22.669 |           <fecha>2018-06-01T00:00:00</fecha>
2018/07/31 09:57:22.669 |           <activo_n></activo_n>
2018/07/31 09:57:22.669 |           <role_full_name>IP - Ing. Procesos</role_full_name>
2018/07/31 09:57:22.685 |           <id>5154473</id>
2018/07/31 09:57:22.685 |           <obs>.ERR</obs>
2018/07/31 09:57:22.685 |         </Record>
2018/07/31 09:57:22.685 |         <Record>
2018/07/31 09:57:22.701 |           <team_lider>lastName01, Name01</team_lider>
2018/07/31 09:57:22.701 |           <team_lider_email>emailName01@domain.com.ar</team_lider_email>
2018/07/31 09:57:22.716 |           <name_resource>lastName02, Name 02</name_resource>
2018/07/31 09:57:22.716 |           <resource_email>emailName02@domain.com.ar</resource_email>
2018/07/31 09:57:22.732 |           <fecha>2018-06-04T00:00:00</fecha>
2018/07/31 09:57:22.732 |           <activo_n></activo_n>
2018/07/31 09:57:22.732 |           <role_full_name>IP - Ing. Procesos</role_full_name>
2018/07/31 09:57:22.748 |           <id>5154473</id>
2018/07/31 09:57:22.748 |           <obs>.ERR</obs>
2018/07/31 09:57:22.748 |         </Record>
2018/07/31 09:57:22.748 |         <Record>
2018/07/31 09:57:22.748 |           <team_lider>lastName01, Name01</team_lider>
2018/07/31 09:57:22.763 |           <team_lider_email>emailName01@domain.com.ar</team_lider_email>
2018/07/31 09:57:22.763 |           <name_resource>lastName02, Name 02</name_resource>
2018/07/31 09:57:22.779 |           <resource_email>emailName02@domain.com.ar</resource_email>
2018/07/31 09:57:22.779 |           <fecha>2018-06-05T00:00:00</fecha>
2018/07/31 09:57:22.794 |           <activo_n></activo_n>
2018/07/31 09:57:22.794 |           <role_full_name>IP - Ing. Procesos</role_full_name>
2018/07/31 09:57:22.794 |           <id>5154473</id>
2018/07/31 09:57:22.810 |           <obs>.ERR</obs>
2018/07/31 09:57:22.810 |         </Record>
2018/07/31 09:57:22.810 |         <Record>
2018/07/31 09:57:22.810 |           <team_lider>lastName01, Name01</team_lider>
2018/07/31 09:57:22.826 |           <team_lider_email>emailName01@domain.com.ar</team_lider_email>
2018/07/31 09:57:22.826 |           <name_resource>lastName02, Name 02</name_resource>
2018/07/31 09:57:22.841 |           <resource_email>emailName02@domain.com.ar</resource_email>
2018/07/31 09:57:22.841 |           <fecha>2018-06-06T00:00:00</fecha>
2018/07/31 09:57:22.857 |           <activo_n></activo_n>
2018/07/31 09:57:22.857 |           <role_full_name>IP - Ing. Procesos</role_full_name>
2018/07/31 09:57:22.857 |           <id>5154473</id>
2018/07/31 09:57:22.873 |           <obs>.ERR</obs>
2018/07/31 09:57:22.873 |         </Record>
2018/07/31 09:57:22.873 |         <Record>
2018/07/31 09:57:22.873 |           <team_lider>lastName01, Name01</team_lider>
2018/07/31 09:57:22.873 |           <team_lider_email>emailName01@domain.com.ar</team_lider_email>
2018/07/31 09:57:22.888 |           <name_resource>lastName02, Name 02</name_resource>
2018/07/31 09:57:22.888 |           <resource_email>emailName02@domain.com.ar</resource_email>
2018/07/31 09:57:22.904 |           <fecha>2018-06-07T00:00:00</fecha>
2018/07/31 09:57:22.904 |           <activo_n></activo_n>
2018/07/31 09:57:22.919 |           <role_full_name>IP - Ing. Procesos</role_full_name>
2018/07/31 09:57:22.919 |           <id>5154473</id>
2018/07/31 09:57:22.919 |           <obs>.ERR</obs>
2018/07/31 09:57:22.919 |         </Record>
                                        ...
2018/07/31 09:57:28.341 |       </Records>
2018/07/31 09:57:28.341 |       <Slice>
2018/07/31 09:57:28.341 |         <Number>0</Number>
2018/07/31 09:57:28.341 |         <Size>114</Size>
2018/07/31 09:57:28.357 |         <Total>114</Total>
2018/07/31 09:57:28.357 |       </Slice>
2018/07/31 09:57:28.357 |     </QueryResult>
2018/07/31 09:57:28.357 |   </soapenv:Body>
2018/07/31 09:57:28.357 | </soapenv:Envelope>

 

Could you help me with this?

Outcomes