Clarity

  • Private Community

  • 1.  CA PPM 15.3 - GEL SCRIPT - parsing xml variable

    Posted Jul 31, 2018 10:13 AM

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


    After executing an #cappmnsql 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>Sí</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>Sí</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>Sí</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>Sí</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>Sí</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>Sí</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>Sí</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?



  • 2.  Re: CA PPM 15.3 - GEL SCRIPT - parsing xml variable

    Posted Jul 31, 2018 11:04 AM

    There is a example in the "GEL for Dummies" file in the zip archive 'Gel.zip' attached to the old FAQ thread

     

    (direct link should work ; https://communities.ca.com/servlet/JiveServlet/download/2297606-1-48932/Gel.zip  )



  • 3.  Re: CA PPM 15.3 - GEL SCRIPT - parsing xml variable

    Posted Jul 31, 2018 04:59 PM

    thank you very much David



  • 4.  Re: CA PPM 15.3 - GEL SCRIPT - parsing xml variable
    Best Answer

    Posted Jul 31, 2018 12:12 PM

    Hi RnFernandez ,

     

    You can iterate each record and then fetch data as per below sample code

      <!-- to get each Record from xmlRequest -->
    <gel:forEach select="$xmlReturn//QueryResult/Records/Record" var="RecordNode">
        
           <!-- to get value -->
         <gel:set asString="true" select="$RecordNode/team_lider/text()" var="team_lider"/>
         <gel:set asString="true" select="$RecordNode/team_lider_email/text()" var="team_lider_email"/>
        
    </gel:forEach>

     

    Regards,

    Prashank Singh



  • 5.  Re: CA PPM 15.3 - GEL SCRIPT - parsing xml variable

    Posted Jul 31, 2018 05:00 PM

    thank you very much Prashank