05 - Web Services (EN)05a - IBM i Web Services miscellanea

Three ways to consume SOAP Web Services on IBM i

Last Updated on 3 November 2019 by Roberto De Pedrini

Talking about “consuming Web Service with RPG and SQL” is not so new, I know! There are different ways to consume SOAP web service from IBM i … in this post you’ll find 3 different ways: wsds2rpg, Scott Klement HTTPAPI, HTTPPOSTCLOB. Which is your way?

You’ll find a real example of parsing a “complex” XML SOAP response: different namespaces, levels and so on.

Summary

This post is divided into 4 distinct parts:

  • In the first part, we see how to consume a SOAP Web Service with the Axis Client IBM technique, exploiting an interesting WSDL2RPG utility which, starting from the WSDL of a SOAP Web Service, generates a service program of the interface to the WS. This method is quite simple, but more than once it happened that wsdl2rpg failed to analyze some complex WSDLs.
  • In the second part we are going to use the classic HTTPAPI by Scott Klement … Holy Scott Klement … how many times have you saved us with your utilities or with your examples plagiarized a bit by everyone?
  • We therefore come to a third part where we see the consumption of a SOAP with HTTPPOSTCLOB SQL … which not only allows you to consume post type RESTs but also SOAPs, taking care to pass headers and call bodies correctly, respecting the concepts of Envelope of the soap.
  • There is also a fourth part of the post … dedicated to a real example of parsing a fairly complex SOAP Response, extracting data from different levels and namespaces … always using SQL with the XMLTABLE function.

Introduction

To make sure that you too can try the service we have preferred to create our examples on the most common SOAP web service for IBM i: ConvertTemp, the SOAP service that is created by default for each HTTP Server that we are going to create from the interface IWS IBM i.

First of all, let’s retrieve the WSDL of the ConvertTemp service by going through the IWS web management (IBM Web Administration for i), usually reachable from the URL: http: // <IP IBMi>: 2001 / HTTPAdmin

From the properties of the service, we can see the address and port of publication (URL of the service).

With the appropriate link we also see the WSDL, the XML document that explains the functions exposed by the service itself, the parameters and the detail of the HTTP communication.

Method 1: wsdl2rpg, Axis Client

WSDL2RPG creates a service program that we can “bind” in our programs to interface to the SOAP Web Service without worrying about XML, etc., by executing simple calls to procedures with fairly normal parameters for an RPG programmer.

We open a QSH console (and execute the following command, in order to create an interface program in our library (in my case FAQ400), with the -o option we also define a directory of the IFS where to save the sources of the service program, modules and “includes” RPG

/QIBM/ProdData/OS/WebServices/V1/client/BIN/wsdl2rpg.sh 
 -t90 -o / IFS / faq400 / converttemp 
 -s / QSYS.LIB / FAQ400.LIB / convt.SRVPGM 
http://xxx.xxx.xxx.xxx:10010/web/services/ConvertTempService/ConvertTemp?wsdl

At this point we just have to write our program … indicate the appropriate / COPY for the service program procedures and “consume” the SOAP Web Service … here below the entire source of an RPG program that receives an input temperature degrees Fahrenheit and turns it into degrees centigrade.

As you can see in the following source I added the service program generated from WSDL2RPG to my Bind Directory

        //-------------------------------------
        // This program will invoke a SOAP webservice
        // through a service program generated
        // with WSDL2RPG.SH utility:
        // STRQSH
        // /QIBM/ProdData/OS/WebServices/V1/client/BIN/wsdl2rpg.sh
        // -t90 -o/home/faq400/converttemp
        // -s/QSYS.LIB/FAQ400.LIB/convt.SRVPGM
        // http://xxx.xxx.xxx.xxx:port/web/services/ConvertTempService/ConvertTemp?wsdl
        //
        // Then I added this service program to my Bind Directory
        // ADDBNDDIRE BNDDIR(FAQ400/FAQ400SRV) OBJ((CONVT))
        //
        // Now I set my BNDDIR in ctl-opt
        // --------------------------------------------------------------------

        ctl-opt DFTNAME(F4WSDL01);
        ctl-opt BNDDIR('FAQ400SRV');

        /copy /home/faq400/ConvertTemp/ConvertTempServices.rpgleinc

        dcl-s  OutputText char(50);
        dcl-ds WsStub likeds(This_t);
        dcl-ds Input     likeds(CONVERTTEMPInput_t);
        dcl-ds Result    likeds(CONVERTTEMPResult_t) ;

        // *entry plist Temperature IN
        dcl-pi main extpgm('F4WSDL01');
          tempin char(32);
        END-PI;

       //--------------------------------------------------------------------
       // Web service logic. The code will attempt to invoke a Web
       // service in order to convert temperature in Fahrenheit to Celsius
       //and then display the results.
       //--------------------------------------------------------------------

         // Get a Web service stub. The host and port for the endpoint may need
         // to be changed to match host and port of Web service. Or you can pass
         // blanks and endpoint in the WSDL file will be used.
         clear WsStub;
         WsStub.endpoint =
         'http://172.17.238.46:10010'+
         '/web/services/ConvertTempService/ConvertTemp';
         clear input;
         Input.TEMPIN.value = %trim(TEMPIN);
         if (stub_create_ConvertTempServices(WsStub) = *ON);
           // Invoke the ConvertTemp Web service operation.
           if (stub_op_ConvertTemp0(WsStub:Input:Result) = *ON);
              OutputText = Input.TEMPIN.value + ' Fahrenheit is '
                         + Result.TEMPOUT.value + ' Celsius.';
           else;
              OutputText = WsStub.excString;
           endif;

           // Display results.
           dsply OutputText;

           // Destroy Web service stubs.
           stub_destroy_ConvertTempServices(WsStub);
         endif;

         *INLR=*ON;
         return;

You can find this source on Github: https://github.com/Faq400Git/F4P0001/blob/master/F4P0001/F4P0001/F4WSDL01.RPGLE

Method 2, HTTPAPI by Scott Klement

Scott Klement has done so much for the IBM i Community over time by creating utilities, documentation and actively participating in forums, events and training courses. Among the great things he did, there is also HTTPAPI, a library that allows you to consume REST and SOAP Web Services from RPG in a very simple way.

If, for example, we want to create an RPG program that recalls our SOAP ConvertTemp Web Service, it is sufficient to take inspiration from the following source (taken from an example by Scott himself!):

       //-------------------------------------------------------
       // F4HTTPAPI: Consuming a SOAP web service
       //      through HTTPAPI utility by Scott Klement
       //
       //  Version 1.0
       // -------------------------------------------------

        ctl-opt option(*srcstmt:*nounref) dftactgrp(*no);
        ctl-opt BNDDIR('HTTPAPI') ;

        // Entry plist
        dcl-pi F4HTTPAPI1;
           TempinAlfa char(15);
        END-PI;


        /copy httpapi_h

        dcl-s UrlHost       varchar(200);
        dcl-s UrlEndPoint   varchar(200);
        dcl-s PostUrl       varchar(254);
        dcl-s PostData      varchar(32000) ;
        dcl-s PostResult    varchar(32000) ;
        dcl-s string char(30);
        dcl-s reply  char(10);
        dcl-s errorMsg char(256);

        // Input data
        dcl-s tempin packed(7:2);

        // Output data
        dcl-ds xmlout qualified;
             Tempout packed(7:2);
        END-DS;


        // Set CCSID 280 (ITA)
         exec sql
          CALL QSYS2.QCMDEXC('CHGJOB CCSID(280)');

        // Legge input
        monitor;
           tempin=%dec(tempinAlfa:7:2);
        on-error;
           tempin=40;
        ENDMON;

        UrlHost       ='http://172.17.238.46:10010';
        UrlEndPoint='/web/services/ConvertTempService/ConvertTemp';

        PostUrl=%trim(UrlHost)+%trim(UrlEndPoint);
        PostData=set_PostdataConvertTemp(Tempin);
        //PostHeader=get_PostHeader(%len(%trim(PostData)):'text/xml');

        // Call the SOAP web service
        // http_setOption('SoapAction': '"GetConversionRate"');
        clear errorMsg;
        clear xmlout;

        monitor;
          PostResult = http_string( 'POST': PostURL: PostData: 'text/xml');
        on-error;
          ErrorMsg=http_error();
          PostResult='<Result>Error</Result>';
        endmon;

        // Parse output
        monitor;
          xml-into xmlout %xml(postResult: 'case=any ns=remove +
             path=Envelope/Body/converttempResponse/return');
        on-error;
        endmon;

        // Reimposta CCSID predefinito dell'utente
         exec sql
          CALL QSYS2.QCMDEXC('CHGJOB CCSID(*USRPRF)');


        string='Tempin:'+%editc(tempin:'K');
        dsply string;
        string='Tempout:'+%editc(xmlout.tempout:'K');
        dsply string ;
        Dsply ( 'Press <Enter> to end program' ) ' ' reply;

        *inlr = *on ;


        //-------------------------------------------------------
        // Set Postdata ... SOAP Envelope
        //-------------------------------------------------------
        dcl-proc set_PostDataConvertTemp;
        dcl-pi   set_PostDataConvertTemp varchar(32000);
          TempF   packed(7:2) const;
        end-pi;
        dcl-s PostData varchar(32000);

        PostData=' '
        +'<soapenv:Envelope'
        +' xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"'
        +' xmlns:con="http://converttemp.wsbeans.iseries/">'
        +' <soapenv:Header/>'
        +'    <soapenv:Body>'
        +'      <con:converttemp>'
        +'         <arg0>'
        +'            <TEMPIN>$$TEMPF</TEMPIN>'
        +'         </arg0>'
        +'      </con:converttemp>'
        +'   </soapenv:Body>'
        +'</soapenv:Envelope>';

        // Set input temperature;
        PostData=%scanrpl('$$TEMPF':%editc(Tempf:'K'):PostData);

        return PostData;
       end-proc;

This source on Github: https://github.com/Faq400Git/F4P0001/blob/master/F4P0001/F4P0001/F4HTTPAPI1.SQLRPGLE

Method 3 .. SQL or SQL Embedded in RPG with HTTPPOSTCLOB

The HTTP capabilities of DB2 for i and the XML management functions make the consumption of SOAP services (and of course REST!) Directly in SQL very simple …

The HTTPPOSTCLOB function that we are going to use expects a HEADER, a BODY and a URL of the service … to understand how to compile the XML of Header and Body I suggest you to install one of the many utilities that test the Web Services from Windows or Mac … I personally I use SOAPUi (http://www.soapui.org) … in this way we feed the WSDL to SOAPui and we see the various Request and related parameters to be used via HTTPPOSTCLOB.

To be able to reuse and simplify the RPG work I tend to create Stored Procedures or UDFs or UDTFs to return the Web Service results as if it were a normal SELECT on a table.

Here is a SQL UDF that returns the converted temperature:

CREATE or replace function faq400.converttemp (
 i_fahrenheit CHAR(10))
 RETURNS varchar(50)
 LANGUAGE SQL
 BEGIN

 DECLARE v_header varchar (1000);
 DECLARE v_body varchar (1000);
 DECLARE v_url varchar (1000);


 declare v_data varchar (1000);
 DECLARE v_result varchar (50);


 -- Set default Envelope-Body
 SET v_body =
 '<soapenv:Envelope
 xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:con="http://converttemp.wsbeans.iseries/">
   <soapenv:Header/>
   <soapenv:Body>
      <con:converttemp>
         <arg0>
            <TEMPIN>$$$TEMPIN</TEMPIN>
         </arg0>
      </con:converttemp>
   </soapenv:Body>
</soapenv:Envelope>';

-- Replace the input variable in the body
set v_body=replace(v_body, '$$$TEMPIN', i_fahrenheit);


SET v_header =
 '<httpHeader>
 <header name ="content-type" value ="application/xml"/>
 </httpHeader>';


SET v_url =
 'http://172.17.238.46:10010/web/services/ConvertTempService/ConvertTemp';


select a. * into v_result
FROM
XMLTABLE (
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
                'http://converttemp.wsbeans.iseries/' as "ns2"),
'$ doc/soap:Envelope/soap:Body/*:converttempResponse/return'
PASSING
xmlparse (document SYSTOOLS.HTTPPOSTCLOB(v_url, v_header, v_body))
as "doc"
columns
TEMPOUT varchar (50) path 'TEMPOUT'
) as a;
 return v_result;

END;


--- Testing our UDF
select faq400.converttemp('42')
 from sysibm.sysdummy1;


--- Direct test
select a.*
FROM
XMLTABLE (
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/'
  AS "soap", 'http://converttemp.wsbeans.iseries/' as "ns2"),
'$ doc/soap:Envelope/soap:Body/*:converttempResponse/return'
PASSING
xmlparse (document SYSTOOLS.HTTPPOSTCLOB(
'http://172.17.238.46:10010/web/services/ConvertTempService/ConvertTemp',
'<httpHeader>
<header name="content-type" value="application/xml"/>
</httpHeader>',
'<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:con="http://converttemp.wsbeans.iseries/">
   <soapenv:Header/>
   <soapenv:Body>
      <con:converttemp>
         <arg0>
            <TEMPIN>80</TEMPIN>
         </arg0>
      </con:converttemp>
   </soapenv:Body>
</soapenv:Envelope>
'))
as "doc"
columns
TEMPOUT varchar (50) path 'TEMPOUT'
) as a;
 return v_result;

--
select * from table(
SYSTOOLS.HTTPPOSTCLOBVERBOSE(
'http://172.17.238.46:10010/web/services/ConvertTempService/ConvertTemp',
'<httpHeader>
<header name="content-type" value="application/xml"/>
</httpHeader>',
'<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:con="http://converttemp.wsbeans.iseries/">
   <soapenv:Header/>
   <soapenv:Body>
      <con:converttemp>
         <arg0>
            <TEMPIN>80</TEMPIN>
         </arg0>
      </con:converttemp>
   </soapenv:Body>
</soapenv:Envelope>
') );


Look at this source on Github: https://github.com/Faq400Git/F4P0001/blob/master/F4P0001/F4P0001/F4SOAPSQL1.TXT

Now we can test our UDF with a simple SELECT sql

select faq400.converttemp (’42’)
from sysibm.sysdummy1;

(4) Parsing of a “complex” XML Soap Response

The example we have done with the ConvertTemp web service is a simple, quite didactic example. In the real world then we often find ourselves involved in quite complex web services, services created by some “robot” or code generator that simplify the publication of a web service but that create a data structure that is anything but easy to manage … at least for us human beings of the RPG world, that we don’t have great tools that simplify our life in the management of XML and JSON (even if this statement is not entirely true … the latest operating system version 7.3 / 7.4 provides excellent tools for JSON and XML … and Scott Klement and the rest of the IBM i World Community)

But let’s get to Soap Response with complex XML, with Namespaces, levels, and repeated tags. The one shown below is an XML SOAP Response from a Web Service on Oracle’s NetSuite platform (with some modifications to anonymize its content!):

<soapenv: Envelope xmlns: soapenv = "http://schemas.xmlsoap.org/soap/envelope/" xmlns: xsd = "http://www.w3.org/2001/XMLSchema" xmlns: xsi = "http: //www.w3.org/2001/XMLSchema-instance ">
   <Soapenv: Header>
      <platformMsgs: documentInfo xmlns: platformMsgs = "urn: messages_2018_1.platform.webservices.netsuite.com">
         <PlatformMsgs: NSID> WEBSERVICES_xxx </ platformMsgs: NSID>
      </ PlatformMsgs: DocumentInfo>
   </ Soapenv: Header>
   <Soapenv: Body>
      <searchResponse xmlns = "urn: core_2018_1.platform.webservices.netsuite.com">
         <SearchResult>
            <status isSuccess = "true" />
            <TotalRecords> 1 </ totalRecords>
            <PageSize> 1000 </ pageSize>
            <TotalPages> 1 </ TotalPages>
            <PageIndex> 1 </ pageIndex>
            <SearchId> WEBSERVICES_xxx </ searchId>
            <SearchRowList>
               <searchRow xsi: type = "listAcct: ItemSearchRow" xmlns: listAcct = "urn: accounting_2018_1.lists.webservices.netsuite.com">
                  <listAcct: basic xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <PlatformCommon: displayName>
                        <searchValue> Item AAA </searchValue>
                     </ PlatformCommon: displayName>
                     <PlatformCommon: InternalID>
                        <searchValue internalId = "161" />
                     </ PlatformCommon: InternalID>
                     <PlatformCommon: itemId>
                        <SearchValue> XXX51113 </ searchValue>
                     </ PlatformCommon: itemId>
                     <PlatformCommon: saleUnit>
                        <searchValue internalId = "4" />
                     </ PlatformCommon: saleUnit>
                     <PlatformCommon: type>
                        <SearchValue> _nonInventoryItem </ searchValue>
                     </ PlatformCommon: type>
                     <PlatformCommon: weight>
                        <SearchValue> 0.0 </ searchValue>
                     </ PlatformCommon: weight>
                     <PlatformCommon: customFieldList>
                        <customField internalId = "2595" scriptId = "custitem_dlt_qct_pack_quantity" xsi: type = "ns1: SearchColumnLongCustomField" xmlns: ns1 = "urn: core_2018_1.platform.webservices.netsuite.com">
                           <Ns1: searchValue> 12 </ ns1: searchValue>
                        </ Customfield>
                     </ PlatformCommon: customFieldList>
                  </ ListAcct: basic>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_cseg_dlt_qct_neg_cl" internalId = "2575" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "296" />
                        <PlatformCommon: name>
                           <searchValue> Item AAA long description </searchValue>
                           <customLabel> Item Classification </customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_dlt_qct_neg_package_item" internalId = "2593" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "305" />
                        <PlatformCommon: name>
                           <SearchValue> XXX5011300012 </ searchValue>
                           <customLabel> Item Packaging </customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_dlt_qct_neg_bar_code" internalId = "2598" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "306" />
                        <PlatformCommon: name>
                           <SearchValue> 1234567890123 </ searchValue>
                           <CustomLabel> Barcode </ customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
               </ SearchRow>
               
   <searchRow xsi: type = "listAcct: ItemSearchRow" xmlns: listAcct = "urn: accounting_2018_1.lists.webservices.netsuite.com">
                  <listAcct: basic xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <PlatformCommon: displayName>
                        <searchValue> Item BBB </searchValue>
                     </ PlatformCommon: displayName>
                     <PlatformCommon: InternalID>
                        <searchValue internalId = "161" />
                     </ PlatformCommon: InternalID>
                     <PlatformCommon: itemId>
                        <SearchValue> XXX51114 </ searchValue>
                     </ PlatformCommon: itemId>
                     <PlatformCommon: saleUnit>
                        <searchValue internalId = "4" />
                     </ PlatformCommon: saleUnit>
                     <PlatformCommon: type>
                        <SearchValue> _nonInventoryItem </ searchValue>
                     </ PlatformCommon: type>
                     <PlatformCommon: weight>
                        <SearchValue> 0.0 </ searchValue>
                     </ PlatformCommon: weight>
                     <PlatformCommon: customFieldList>
                        <customField internalId = "2595" scriptId = "custitem_dlt_qct_pack_quantity" xsi: type = "ns1: SearchColumnLongCustomField" xmlns: ns1 = "urn: core_2018_1.platform.webservices.netsuite.com">
                           <Ns1: searchValue> 12 </ ns1: searchValue>
                        </ Customfield>
                     </ PlatformCommon: customFieldList>
                  </ ListAcct: basic>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_cseg_dlt_qct_neg_cl" internalId = "2575" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "296" />
                        <PlatformCommon: name>
                           <searchValue> Item BBB long description </searchValue>
                           <customLabel> Item Classification </customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_dlt_qct_neg_package_item" internalId = "2593" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "306" />
                        <PlatformCommon: name>
                           <SearchValue> XXX5011300013 </ searchValue>
                           <customLabel> Item Packaging </customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_dlt_qct_neg_bar_code" internalId = "2598" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "306" />
                        <PlatformCommon: name>
                           <SearchValue> 1234567890124 </ searchValue>
                           <CustomLabel> Barcode </ customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
               </ SearchRow>     
<searchRow xsi: type = "listAcct: ItemSearchRow" xmlns: listAcct = "urn: accounting_2018_1.lists.webservices.netsuite.com">
                  <listAcct: basic xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <PlatformCommon: displayName>
                        <searchValue> Item CCC </searchValue>
                     </ PlatformCommon: displayName>
                     <PlatformCommon: InternalID>
                        <searchValue internalId = "161" />
                     </ PlatformCommon: InternalID>
                     <PlatformCommon: itemId>
                        <SearchValue> XXX51114 </ searchValue>
                     </ PlatformCommon: itemId>
                     <PlatformCommon: saleUnit>
                        <searchValue internalId = "4" />
                     </ PlatformCommon: saleUnit>
                     <PlatformCommon: type>
                        <SearchValue> _nonInventoryItem </ searchValue>
                     </ PlatformCommon: type>
                     <PlatformCommon: weight>
                        <SearchValue> 0.0 </ searchValue>
                     </ PlatformCommon: weight>
                     <PlatformCommon: customFieldList>
                        <customField internalId = "2595" scriptId = "custitem_dlt_qct_pack_quantity" xsi: type = "ns1: SearchColumnLongCustomField" xmlns: ns1 = "urn: core_2018_1.platform.webservices.netsuite.com">
                           <Ns1: searchValue> 12 </ ns1: searchValue>
                        </ Customfield>
                     </ PlatformCommon: customFieldList>
                  </ ListAcct: basic>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_cseg_dlt_qct_neg_cl" internalId = "2575" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "297" />
                        <PlatformCommon: name>
                           <searchValue> Item CCC long description </searchValue>
                           <customLabel> Item Classification </customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_dlt_qct_neg_package_item" internalId = "2593" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "305" />
                        <PlatformCommon: name>
                           <SearchValue> XXX5011300013 </ searchValue>
                           <customLabel> Item Packaging </customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
                  <listAcct: customSearchJoin xmlns: platformCommon = "urn: common_2018_1.platform.webservices.netsuite.com">
                     <platformCommon: customizationRef scriptId = "custitem_dlt_qct_neg_bar_code" internalId = "2598" />
                     <platformCommon: searchRowBasic xsi: type = "platformCommon: CustomRecordSearchRowBasic">
                        <platformCommon: recType internalId = "306" />
                        <PlatformCommon: name>
                           <SearchValue> 1234567890125 </ searchValue>
                           <CustomLabel> Barcode </ customLabel>
                        </ PlatformCommon: name>
                     </ PlatformCommon: searchRowBasic>
                  </ ListAcct: customSearchJoin>
               </ SearchRow>			   
            </ SearchRowList>
         </ SearchResult>
      </ SearchResponse>
   </ Soapenv: Body>
</ Soapenv: Envelope>

For the parsing, in the original program, I execute it directly with a single SQL statement where I call the Netsuite web service with HTTPPOSTCLOB … in this case I go to read the XML reading it from an IFS file, but the concept of parsing does not change

Look at this SQLRPGLE source: I read the xml document from IFS and extract some columns from it

        //-----------------------------------------
       // F4XMLPARS1 SQLRPGLE Source
       //   We are trying to parse and XML stored
       //   in our IFS, and extract data from
       //   different levels and  different namespaces
       //   through XMLTABLE and XMLPARSE SQL Function
       //
       //   Pay attention to GET_XML_FILE to read an xml
       //   stored in the IFS
       //
       //   Rev. 5
       // ------------------------------------------------
       ctl-opt DFTACTGRP(*NO);




       dcl-ds dsresult qualified dim(999);
         totalrecords packed(9:0);
         pagesize packed(9:0);
         totalpages packed(9:0);
         pageindex packed(9:0);
         searchid varchar(50);
         displayname varchar(50);
         internalid varchar(50);
         itemid varchar(50);
         saleunit varchar(50);
         type varchar(50);
         weight varchar(50);
       end-ds;




       dcl-s  risposta char(10);

       dcl-s  RowsFetched int(5);
       dcl-s  i           int(5);


       // Set COMMIT to read XML file from IFS
       exec sql SET OPTION COMMIT=*CHG;

       // Set CCSID for some HTTP Functions (65535 is no good!)
       exec sql CALL QSYS2.QCMDEXC('CHGJOB CCSID(280)');


       // Get my XML file from IFS and parse it with  XMLTABLE
       // and XMLPARSE SQL Functions
       exec sql
        declare wscursor cursor for
        select *
        FROM
        XMLTABLE(
        xmlnamespaces
        (default 'urn:core_2018_1.platform.webservices.netsuite.com',
         'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
         'urn:accounting_2018_1.lists.webservices.netsuite.com' as "listact",
         'urn:common_2018_1.platform.webservices.netsuite.com' as "platf"
            ),
          '$doc/soap:Envelope/soap:Body/+
          *:searchResponse/searchResult/+
          *:searchRowList/searchRow[*]'
       PASSING
       xmlparse(document GET_XML_FILE('/tmp/NetSuite XML SOAP Response.xml'))
       as "doc"
       columns
       totalRecords decimal(9, 0) path '../../totalRecords',
       pageSize decimal(9, 0) path '../../pageSize',
       totalPages decimal(9, 0) path '../../totalPages',
       pageIndex decimal(9, 0) path '../../pageIndex',
       searchId varchar(50) path '../../searchId',
       displayName varchar(50) path '*:basic/*:displayName/searchValue',
       internalId varchar(50)
         path '*:basic/*:internalId/searchValue/@internalId',
       itemId varchar(50) path '*:basic/*:itemId/searchValue',
       saleUnit varchar(50) path '*:basic/*:saleUnit/searchValue/@internalId',
       type varchar(50) path '*:basic/*:type/searchValue',
       weight varchar(50) path '*:basic/*:weight/searchValue'
       ) as a;

       exec sql open wscursor;

       exec sql
          fetch wscursor for 999 rows into :dsresult;

       exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ;

       // Return to my current job CCSID
       exec sql CALL QSYS2.QCMDEXC('CHGJOB CCSID(*USRPRF)');

       dsply 'Risultato:';
       for i=1 to RowsFetched;
          dsply 'Row :';
          dsply i;
          dsply '- displayname:';
          dsply dsresult(i).displayname;
          dsply '- internalid:';
          dsply dsresult(i).internalid;
          dsply '- itemid:';
          dsply dsresult(i).itemid;
          dsply '- weight:';
          dsply dsresult(i).weight;
       endfor;
       dsply 'End' '' risposta;


       *inlr=*on;

This source on Github: https://github.com/Faq400Git/F4P0001/blob/master/F4P0001/F4P0001/F4XMLPARS1.SQLRPGLE

And here only the SQL for ACS/Rdi “Run SQL Script”;

select a. *
FROM
XMLTABLE (xmlnamespaces (default 'urn: core_2018_1.platform.webservices.netsuite.com',
  'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
  'urn: accounting_2018_1.lists.webservices.netsuite.com' as "listact",
  'urn: common_2018_1.platform.webservices.netsuite.com' as "platformCommon"
  )
'$ Doc / soap: Envelope / soap: Body /: SearchResponse / searchResult /: searchRowList / searchRo[]w'
PASSING
xmlparse (document GET_XML_FILE ('/ tmp / Netsuite XML SOAP Response.xml'))
as "doc"
columns
totalRecords decimal (9, 0) path '../../totalRecords',
pageSize decimal (9, 0) path '../../pageSize',
totalPages decimal (9, 0) path '../../totalPages',
pageIndex decimal (9, 0) path '../../pageIndex',
searchId varchar (50) path '../../searchId'
, displayName varchar (50) path ': basic /: displayName / searchValue'
, internalId varchar (50) path ': basic /: internalId / searchValue / @ internalId'
, itemId varchar (50) path ': basic /: itemId / searchValue'
, saleUnit varchar (50) path ': basic /: saleUnit / searchValue / @ internalId'
, type varchar (50) path ': basic /: type / searchValue'
, weight varchar (50) path ': basic /: weight / searchValue'
, customFieldList_id varchar (50) path ': basic /: customFieldList / customField / @ internalId'
, rectype_internalid varchar (50) path ': customSearchJoin /[1]: searchRowBasic /: recType / @ internalId'
, name varchar (50) path ': customSearchJoin /[1]: searchRowBasic / *: name / searchValue'
 ) as a; x

The result of this SQL is an SQL table that we can use directly in our RPG with Embedded SQL or as a normal table for JOIN or other operations.

Conclusion

We have seen three different ways of consuming a SOAP Web Service, they are naturally not the only ones, they are the ones we meet most often. Leave a comment to the post if you use a different way or if you have suggestions.

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

2 Comments

  • Pingback: URL

Leave a Reply

Your email address will not be published. Required fields are marked *